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
>