Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

After Update trigger does not catch all updated columns Expand / Collapse
Author
Message
Posted Tuesday, February 10, 2009 3:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, February 10, 2009 3:53 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 758, Visits: 1,031
Can we see the code for your trigger, and some table formats please?
Post #654315
Posted Tuesday, February 10, 2009 4:00 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
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
Posted Tuesday, February 10, 2009 4:01 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
.. 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
Posted Tuesday, February 10, 2009 5:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse