February 10, 2009 at 3:49 pm
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
February 10, 2009 at 3:53 pm
[font="Verdana"]Can we see the code for your trigger, and some table formats please?[/font]
February 10, 2009 at 4:00 pm
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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 10, 2009 at 4:01 pm
.. and as Bruce says, post the trigger code if you want an answer to your specific problem.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 10, 2009 at 5:26 pm
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
updaterequest
setdetails = 'Details updates first and after requestStatus',
requeststatus = 'CLR'
whererequestnum='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
setrequeststatus = 'CLR',
details = 'requestStatus updates first and after Details',
whererequestnum='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_syncvarchar(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@byteOrderInt
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=requeststatusfrominserted
Select@old_value=requeststatusfromdeleted
IfNOT(@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
insertinto 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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply