sql server trigger

Question

Hi,

I would like to write trigger that after inserting record to table Person I would like to create new record in table Car with PersonID = newly inserter ID from table Person:

Person: ID Name

Car: ID PersonID //FK to Person Name

thanks for any hint, bye

Answer 1

So you would have something like:

CREATE TRIGGER trgAfterPersonInsert
ON dbo.Person AFTER INSERT 
AS BEGIN
   INSERT INTO dbo.Car(PersonID)
      SELECT i.PersonID 
      FROM INSERTED i
END

The important point to remember is: if you insert 10 people into your table in a single statement, your trigger will be called once with a "pseudo-table" INSERTED containing those ten people.

So your trigger code needs to be set-aware - do not assume the trigger gets called once for each row - that is not the case.

See the MSDN docs for CREATE TRIGGER for more details, and check out the An Introduction To Triggers tutorial on SQL Team.