On inserting a rec, auto insert into related table

Hi there,
I'm new...
Could someone tell me how to automatically (via a trigger perhaps ?) insert
records into a related (child) table, based on the insert into the parent ?
I have a situation in my app where I want to add a record into a tabe called
'OrderedService'. On insert into this table, I want to insert automatically,
exactly 5 records into (foreign key) related table 'OrderedServiceResult'.
Note that the only fields that need populating on creation of the child are
those necessary for the key...
Thank you !Bazza Formez (bazza.formez@.paradise.net.nz) writes:
> Could someone tell me how to automatically (via a trigger perhaps ?)
> insert records into a related (child) table, based on the insert into
> the parent ?
> I have a situation in my app where I want to add a record into a tabe
> called 'OrderedService'. On insert into this table, I want to insert
> automatically, exactly 5 records into (foreign key) related table
> 'OrderedServiceResult'.
> Note that the only fields that need populating on creation of the child
> are those necessary for the key...
Yes, you could use a trigger for this, at least judging from the information
you have provided. Here is a simple example:
CREATE TRIGGER tbl_tri ON tbl FOR INSERT AS
INSERT othertbl(col1, col2, ...)
SELECT col1, col2, ...
FROM inserted
"inserted" that appears here is a virtual table that holds the rows
that were inserted. This table is only visible within a trigger. There
is a sister table "deleted" which holds deleted rows in a DELETE trigger.
In an UPDATE trigger both tables are populated, "inserted" holding the
new rows, and "deleted" the old rows.
Very important to understand is that a trigger on SQL Server is fired
once per statement. Thus "inserted" can hold many rows.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for this excellent reply... Your effort is appreciated.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96CF4D245B5FYazorman@.127.0.0.1...
> Bazza Formez (bazza.formez@.paradise.net.nz) writes:
> Yes, you could use a trigger for this, at least judging from the
> information
> you have provided. Here is a simple example:
> CREATE TRIGGER tbl_tri ON tbl FOR INSERT AS
> INSERT othertbl(col1, col2, ...)
> SELECT col1, col2, ...
> FROM inserted
> "inserted" that appears here is a virtual table that holds the rows
> that were inserted. This table is only visible within a trigger. There
> is a sister table "deleted" which holds deleted rows in a DELETE trigger.
> In an UPDATE trigger both tables are populated, "inserted" holding the
> new rows, and "deleted" the old rows.
> Very important to understand is that a trigger on SQL Server is fired
> once per statement. Thus "inserted" can hold many rows.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>

ole db destination not inserting data

my package has a data flow task that is attempting to insert data into a sql server table using the ole db destination. the package validates and runs without reporting any errors, but the data was not inserted into the table. my data mappings look fine. how can i see what's happening when the component attempts to insert the data into the table?

Can you post all log messages that are produced from your data-flow?

Regards

Jamie

|||

Jamie Thomson wrote:

Can you post all log messages that are produced from your data-flow?

Regards

Jamie

here's the info from the output windows:

SSIS package "CaseStudy_Load.dtsx" starting.
Information: 0x4004300A at Data Flow Lockbox Detail Data Task, DTS.Pipeline: Validation phase is beginning.
Warning: 0x80047076 at Data Flow Lockbox Detail Data Task, DTS.Pipeline: The output column "PaymentAmount" (294) on output "Merge Join Output" (268) and component "Merge Join Checks and Invoices" (265) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Information: 0x4004300A at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Data Flow Lockbox Validate File and Header Info, Flat File Lockbox [1]: The processing of file "c:\casestudy\lockbox\samplelockbox.txt" has started.
Information: 0x400490F4 at Data Flow Lockbox Validate File and Header Info, Lookup BankBatchID [373]: component "Lookup BankBatchID" (373) has cached 0 rows.
Information: 0x4004300C at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: Execute phase is beginning.
Information: 0x402090DE at Data Flow Lockbox Validate File and Header Info, Flat File Lockbox [1]: The total number of data rows processed for file "c:\casestudy\lockbox\samplelockbox.txt" is 11.
Information: 0x402090DF at Data Flow Lockbox Validate File and Header Info, OLE DB Destination Error Log [326]: The final commit for the data insertion has started.
Information: 0x402090E0 at Data Flow Lockbox Validate File and Header Info, OLE DB Destination Error Log [326]: The final commit for the data insertion has ended.
Information: 0x40043008 at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Data Flow Lockbox Validate File and Header Info, Flat File Lockbox [1]: The processing of file "c:\casestudy\lockbox\samplelockbox.txt" has ended.
Information: 0x40043009 at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: "component "OLE DB Destination Error Log" (326)" wrote 0 rows.
Information: 0x4004300B at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: "component "Script Component to Capture BatchItems" (643)" wrote 0 rows.
Information: 0x4004300B at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: "component "Script Component to store variables" (437)" wrote 0 rows.
SSIS package "CaseStudy_Load.dtsx" finished: Success.
The program '[3448] CaseStudy_Load.dtsx: DTS' has exited with code 0 (0x0).

below is the information written to log.txt:

#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message|||When debugging in BIDS - what is the number of rows passed to destination (it is displayed on the path leading from a previous transform to the destination while running package in BIDS)?

If the number is 0, no data has reached destination - something might be wrong with tranformations. Try adding data viewers to understand what data flows between transforms.|||ok. thanks. i discovered that the data isn't flowing from the flat file source. i'll make a new thread with a more appropriate subject line so that more people will notice my issue.

OLE DB Destination

I am proceesing a file and using a OLE Db destination for inserting the rows present into a file to the table.

As of now OLE db destination points to the table and having the fileds mapping of input column and destination column.

now i want to perform some calculation and want insert the rows into table based on the value of some other column for ex.

if (column2 == 1)

{

column1 = column4 - column5

}

if (column3== 1)

{

column1 = column4 - Column6

}

Please let me know how to do this.

Use a Derived Column transformation.

Select "Replace Column 1" as the column to work on in the lower grid, then use the conditional operator.

To just one conditional -

column2 == 1 ? column4 - column 5 : coumn1

if column2 equals 1 then result is column4 minus column5, else use current column1 value.

To do the two if clauses -

column2 == 1 ? column4 - column 5 : column3 = 1 ? column4 - column 6 : column1

|||

I have the same problem

Totally,Thanks

|||

I want to implement all this four condition in one derived column expression.

if (Column1== 1)

{

OutputColumn = ColumnA- Column1RATE
}

if (Column2 == 1)

{

OUtputColumn = ColumnA- Column2RATE
}

if (Column3 == 1)

{

OUtputColumn = ColumnA- Column3RATE
}

if (Column4 == 1)

{

OUtputColumn = ColumnA- Column4RATE
}

please suggest