Trigger Update

Question

Trigger Update

am-244616

SSCarpal Tunnel

Points: 4936
More actions
April 1, 2010 at 2:26 pm

#103920

All,
I have the following trigger which is built to update date_time_mod whenever an update is fired on the table, however the trigger is updating the date_time_mod of the first row only when a mass update is fired. Is there anyway to get around to update the date_time_mod for all the rows effected by the mass update statement.
ALTER TRIGGER [dbo].[TRG_table_DT_MOD]
ON [dbo].
AFTER INSERT, UPDATE
NOT FOR REPLICATION
AS
BEGIN
SET NOCOUNT ON
IF TRIGGER_NESTLEVEL() > 1
RETURN
declare @table_id bigint
select @table_id=table_id from INSERTED
UPDATE table
SET DATE_TIME_MOD = GETDATE() WHERE table_id = @table_id
end
GO
below is the mass update statement which effects 200 rows but the date_time_mod is updated only for the first row.
update table
set user_id = user_id
Any suggestions and inputs would help.
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Dan.Humphries SSChampion Points: 12125 More actions · Answer 1

Based on what I have read an I apologize becuase I don;t know that I have had direct expierence with this but it is my understanding that Sql server does not support row update triggeres so based on other forumns and other responces the popular answer seems to be that you would use cusor selected from inserted or deleted virtual table to see what records was updated

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

Dan.Humphries SSChampion Points: 12125 More actions · Answer 2

HEre is a link to one of the threads on this subject.

http://www.sqlservercentral.com/Forums/Topic730493-338-1.aspx

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

WayneS SSC Guru Points: 95460 More actions · Answer 3

am-244616 - when you select into a variable from a table, the variable is going to hold the value from the last row selected. It might be the first row updated, the last, or something in between.

Dan - forget the cursor. Use the proper, set-based update statement instead:

UPDATE t

SET DATE_TIME_MOD = GETDATE()

-- make sure the first table in the from is the table being updated!

FROM "MyTable" t

JOIN inserted i

ON t.table_id = i.table_id

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 4

Dan.Humphries (4/1/2010)

HEre is a link to one of the threads on this subject.

http://www.sqlservercentral.com/Forums/Topic730493-338-1.aspx%5B/quote%5D

This link is referring to the specific case of firing a trigger during a bulk insert and doesn't really apply to this question.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Dan.Humphries SSChampion Points: 12125 More actions · Answer 5

A trigger will still only fire once per statemnet though is that not correct?

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

WayneS SSC Guru Points: 95460 More actions · Answer 6

Dan.Humphries (4/1/2010)
A trigger will still only fire once per statemnet though is that not correct?

A trigger will fire only ONCE per DML statement. The problem is that the DML statement is updating > 1 row, but only one row is being updated by the trigger.

The inserted virtual table will contain the new contents of ALL the rows being updated by the one DML statement, and thus can be used to update the auditing field for ALL of the rows that were affected.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 7

am-244616 (4/1/2010)
declare @table_id bigint
select @table_id=table_id from INSERTED

This query only give one value for table_id irrespective of no of rows afftected in main table by inset/update

see this code to get this more clearer

create table tbl

( id int identity, name varchar(200) )

create table tr_tbl( output_id int )

create trigger tr on tbl AFter insert

as

begin

SET NOCOUNT ON

IF TRIGGER_NESTLEVEL() > 1

RETURN

declare @id bigint

select @id=id from INSERTED

insert into tr_tbl

select @id

end

GO

insert into tbl

select 'bhuvnesh'

union

select 'jack'

union

select 'max'

select * from tbl

select * from tr_tbl

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)