How can I fire an Update Trigger for each row when multiple rows are changed in one statement

Question

How can I fire an Update Trigger for each row when multiple rows are changed in one statement

jonespm

Ten Centuries

Points: 1393
More actions
July 9, 2008 at 7:39 pm

#195186

Hi,
If I update multiple rows in table in a single sql statement, as I understand it, the update trigger for that table fires just the once. What I don't understand is how can I iterate through each updated row and apply the further updates performed by the table's trigger?
Cheers, Peter

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

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

noeld SSC Guru Points: 96590 More actions · Answer 1

The purpose of TSQL is to encourage SET-BASED processing and not a row-by-row processing. Can't you do what you need to do on all affected records at once ?

If you provide more info we may be able to help you

* Noel

jonespm Ten Centuries Points: 1393 More actions · Answer 2

Hi,

Wow - what a prompt response - very much appreciated.

"Can't you do what you need to do on all affected records at once" - I guess this is what I don't know how to do.

My specific issue is that I want to log all changes to a parent table that has two child tables. When the parent row changes I want to log the value of each column before the change and I want to write a copy of every child record to another two log files so that I can recover exactly how this set of parent/child records looked before the parent was updated.

My plan was to create a log row for the parent which contained cerain control info plus every column in parent table. The PK for this table would be a GUID which I would generate before inserting to row into the log table. I would then iterate through every row in the child tables, that related to the updated parent, and copy them to their own log table. I would have a foreign key column in the child log tables that pointed to the parent log record just created using the GUID I had previously created for the parent log record.

This works fine when I change a single parent record (the normal case) but when more than one parent is updated it would seem that the GUID I create at the start of the trigger is created just the once (because the trigger fires once I guess) this means I get a PK unique contraint error when the subsequent parent record updates are processed and I try to insert new parent log records.

I could let the parent log table create a default PK using newid() and that would work but, if I do that, I don't know how to retrieve the pk just generated so I can use it in foreign key column in the child record logs.

BTW - GUID's used because replication is involved.

Cheers, Peter

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 3

The data in an insert trigger is contained in a set called INSERTED that you can treat just like a table. So if you wanted to capture the data all at once into another table and include a new ID, your query would look something like this:

INSERT INTO AuditTable (...column list...)

SELECT NEWID(),...column list....

FROM INSERTED

That will bring it all over in a set.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 4

You will have to deal with this in a couple of parts. Using the OUTPUT caluse of your insert into your parent table logging table will get you the records just logged with their GUID's. Since you must have a FK relationship of some sort in your actual tables, you can leverage that relationhip to get the children of all of the records you just inserted.

In my sample, I used a temp table for the output of the parent log insert, but you may want to use a table variable.

[font="Courier New"]/* Sample tables and Data */

CREATE TABLE #tmpParent (

RecNum INT NOT NULL

, Info VARCHAR(10) NULL)

CREATE TABLE #tmpChild (

ParentRecNum INT NOT NULL

, ChildRecNum INT NOT NULL

, Info VARCHAR(10) NULL)

CREATE TABLE #tmpParentLog (

RecordGUID UNIQUEIDENTIFIER NOT NULL DEFAULT(NEWID())

, OrigRecNum INT NOT NULL

, Info VARCHAR(10) NULL)

CREATE TABLE #tmpChildLog (

ParentOrigRecNum INT NOT NULL

, ParentRecordGUID UNIQUEIDENTIFIER NULL

, ChildOrigRecNum INT NOT NULL

, ChildRecordGUID UNIQUEIDENTIFIER NOT NULL DEFAULT(NEWID())

, ChildInfo VARCHAR(10) NULL)

INSERT #tmpParent (RecNum, Info)

SELECT 1, 'My Data 1'

UNION SELECT 2, 'My Data 2'

UNION SELECT 3, 'My Data 3'

INSERT #tmpChild (ParentRecNum, ChildRecNum, Info)

SELECT 1,1,'My Data A'

UNION SELECT 1,2,'My Data B'

UNION SELECT 2,1,'My Data A'

UNION SELECT 2,2,'My Data B'

UNION SELECT 3,1,'My Data A'

UNION SELECT 3,2,'My Data B'

/* End Sample tables and data */

/* From here is what a trigger may look like */

CREATE TABLE #tmpOut (

RecordGUID UNIQUEIDENTIFIER NOT NULL

, OrigRecNum INT NOT NULL

, Info VARCHAR(10) NULL)

INSERT #tmpParentLog (OrigRecNum, Info)

OUTPUT INSERTED.* INTO #tmpOut

SELECT * FROM #tmpParent

INSERT #tmpChildLog (ParentOrigRecNum, ParentRecordGUID, ChildOrigRecNum, ChildInfo)

SELECT P.OrigRecNum, P.RecordGUID, C.ChildRecNum, C.Info

FROM #tmpOut P INNER JOIN #tmpChild C ON P.OrigRecNum = C.ParentRecNum[/font]

jonespm Ten Centuries Points: 1393 More actions · Answer 5

Thanks guys, that gets the job underway...

Cheers, Peter

pokerclan Valued Member Points: 65 More actions · Answer 6

I would like to second the thanks! Found what I was looking for!