Hi All,
I have a trigger on a table tracking changes to certain fields.
However, when I do a batch update on that field, it looks like the
trigger only gets fired once. However, I do want to track changes on
every record that the update statement touches. So, is there anyway to
make it work or is there any work around? Do I have to create a cursor
and update one record a time?
Thanks a lot,
blueyep, triggers in SQL Server are fired once per statement, not once per
row. when you describe how do you need to track changes, we might be
able to help|||what I want to do is very simple. I want to track the change to a field
in one table. if the value changes, I will insert a row in the change
log table. Basically if old field value <> new field value, insert a
row in the change log.
However, in another script, I sometimes update the field for many rows
if they meet the criteria. Therefore, I have something like UPDATE
table1 SET field1= 'Y' WHERE field2>field3. When this statement is
executed, the trigger only fired once and therefore, only one row gets
inserted into the changelog table.
I just wonder if there is anyway I can make the trigger fired for each
row without abandoning the batch update and use cursor to do update for
each individual row
Thanks a lot.|||if you don't modify the PK, that's easy:
create table seq(s_id int identity, i int)
insert into seq(i) values(1)
insert into seq(i) values(2)
insert into seq(i) values(3)
insert into seq(i) values(4)
go
create table seq_audit(s_id int, old_i int, new_i int)
go
create trigger seq_upd
on seq
for update
as
insert into seq_audit
select inserted.s_id, deleted.i, inserted.i from inserted, deleted
where inserted.s_id = deleted.s_id
go
select * from seq
s_id i
-- --
1 1
2 2
3 3
4 4
go
update seq set i=i+1 where s_id > 1
go
select * from seq_audit
s_id old_i new_i
-- -- --
2 2 3
3 3 4
4 4 5
go
drop table seq
drop table seq_audit
2012年2月16日星期四
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
>
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
>
订阅:
博文 (Atom)