2012年2月11日星期六

basic trigger ?

Hi
I want to have a trigger than when record is inserting into table A, the
trigger inserts a record into table B
I want the identity value from Table A to be one of the values inserted into
table B.
How do I get the indentity value from tableA in my trigger so that I can
insert into table b
thanksAussie Rules (someone@.somewhere.com) writes:
> I want to have a trigger than when record is inserting into table A, the
> trigger inserts a record into table B
> I want the identity value from Table A to be one of the values inserted
> into table B.
> How do I get the indentity value from tableA in my trigger so that I can
> insert into table b
CREATE TRIGGER A_tri ON A FOR INSERT AS
INSERT B (some_col, some_other_col, ...)
SELECT i.identity_col, i.other_col, ...
FROM inserted i
JOIN ...
inserted is a virttual table holds the inserted rows, so you find the
identity value right there.
Be aware of that a trigger fires once per *statement*.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Sirs
You might want to consider using 'After Insert' instead of 'For
Insert'. This way you know that the record has been successfully inserted
into Table A, Before you insert into Table B.
Mark
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96C24758FE7FYazorman@.127.0.0.1...
> Aussie Rules (someone@.somewhere.com) writes:
the
> CREATE TRIGGER A_tri ON A FOR INSERT AS
> INSERT B (some_col, some_other_col, ...)
> SELECT i.identity_col, i.other_col, ...
> FROM inserted i
> JOIN ...
> inserted is a virttual table holds the inserted rows, so you find the
> identity value right there.
> Be aware of that a trigger fires once per *statement*.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||I expect you meant Instead Of rather than For.
For Insert triggers are classified as After Insert triggers, and only fire
after the row has been successfully inserted. Instead Of triggers fire in
place of the triggering action. If you want to perform an insert, then the
body of the Instead Of Insert trigger must issue an insert into the
underlying table.
"Mark Moss" <markmoss@.adelphia.net> wrote in message
news:O4hAxQPrFHA.1172@.TK2MSFTNGP11.phx.gbl...
> Sirs
> You might want to consider using 'After Insert' instead of 'For
> Insert'. This way you know that the record has been successfully inserted
> into Table A, Before you insert into Table B.
> Mark
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns96C24758FE7FYazorman@.127.0.0.1...
> the
inserted
can
>

没有评论:

发表评论