After Update trigger does not catch all updated columns

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » After Update trigger does not catch all...

After Update trigger does not catch all updated columns

Rate Topic

Display Mode

Topic Options

Author

Message

pgomes-997062

Posted Tuesday, February 10, 2009 3:49 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 19, 2009 4:47 AM
Points: 3, Visits: 16

Hi,

As an example, the sql

update request set details = 'RRR', description = 'new description for RRR'
where requestnum = '788787'

The trigger should insert into an audit table both values but is inserting only with the description column value and null for details.

My question is: how many rows are created in the inserted and deleted tables for a script like above that update more than one column, for the same primary key in the host table?

Thanks,

Paulo

Post #654313

Bruce W Cassidy

Posted Tuesday, February 10, 2009 3:53 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, March 22, 2013 11:49 AM
Points: 945, Visits: 998

Can we see the code for your trigger, and some table formats please?

Post #654315

RBarryYoung

Posted Tuesday, February 10, 2009 4:00 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

pgomes (2/10/2009)

My question is: how many rows are created in the inserted and deleted tables for a script like above that update more than one column, for the same primary key in the host table?

The number of rows is unrelated to the number of columns. There will be one row in the inserted table and one row in the deleted table for every row that is updated. Columns have nothing to do with it.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #654321

RBarryYoung

Posted Tuesday, February 10, 2009 4:01 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

.. and as Bruce says, post the trigger code if you want an answer to your specific problem.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #654322

pgomes-997062

Posted Tuesday, February 10, 2009 5:26 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 19, 2009 4:47 AM
Points: 3, Visits: 16

Hi,
Thanks all for the answers. What I found: the updated table has columns NTEX. If the update occurs first in a NTEXT column than the trigger catches the other updated columns.

After executing

update request
set details = 'Details updates first and after requestStatus',
requeststatus = 'CLR'
where requestnum = '109413'

the trigger inserts one row in the audit table correctly, a column that counts the updates as 2.

If I run the script inverting the update sequence as below

set requeststatus = 'CLR',
details = 'requestStatus updates first and after Details',
where requestnum = '109413'

the trigger inserts the row in the audit table with the requestStatus column = 'CLR',
the Details = null and the column that counts the updates as 1.

Here the trigger logic:

...
Declare @update_qualify_for_sync varchar(5), @pk uniqueidentifier
Select @update_qualify_for_sync = 'FALSE'
--get the PK
Select @pk = request_id_hex from inserted

--Check the details column(NTEXT): byte number 3 (from left) and column order 2
--Byte 3 columns starts from column number 17. The details is the column number 19, so the position
--inside byte 3 will be 19 - 17 + 1 = 3
Declare @byteOrder Int
Set @byteOrder = 2
If (SUBSTRING(columns_updated(),3,1)= power(2,(@byteOrder-1)) )
Begin
Set @update_qualify_for_sync = 'TRUE'
--set the details as 'UPDATE'
Set @details = 'UPDATE'
Set @countUpdates = @countUpdates + 1
End
Else
Begin
Set @details = null
End
--check request status (I could use here If UPDATE(requeststatus)
Select @new_value= requeststatus from inserted
Select @old_value= requeststatus from deleted
If NOT (@new_value = @old_value)
Begin
Set @requestStatus = @new_value
Set @update_qualify_for_sync = 'TRUE'
Set @countUpdates = @countUpdates + 1
End
... code to check other columns
...
--insert the updates into the audit table (PK of this table is autoincrement)
If @update_qualify_for_sync = 'TRUE'
Begin
insert into dbo.sync_requests
(request_num,requeststatus,details,updatecount)
values (@pk, @requeststatus,@details,@countUpdates)
End
--end trigger statement
END

I'm using SQL Server 2000 and it does not allow to handle NTEXT columns in the deleted neither inserted tables. This is why I'm using the SUBSTRING with COLUMNS_UPDATE() function.
Also the 2000 version also does not have the VARBINARY(MAX) data types.

Thanks for your help.

Paulo

Post #654354

« Prev Topic | Next Topic »

Permissions