SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


After Update trigger does not catch all updated columns


After Update trigger does not catch all updated columns

Author
Message
pgomes-997062
pgomes-997062
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 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
Bruce W Cassidy
Bruce W Cassidy
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1723 Visits: 1033
Can we see the code for your trigger, and some table formats please?
RBarryYoung
RBarryYoung
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19072 Visits: 9518
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."
RBarryYoung
RBarryYoung
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19072 Visits: 9518
.. 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."
pgomes-997062
pgomes-997062
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search