john_wong_ca (6/9/2010)
That's a good idea. This will work for most of cases but not for replication. If a table is published in a merge publication, while replication agent is modifying the data, the connection of the agent will use context info as part of the identification of merge agent. In this case, the context info passed in the trigger will not be xml conversable.:-)
absolutely right :w00t:
a solution that will work for replication is to add username and reason fields to the table, populate them with your delete proc then select them from DELETED in the trigger.
on the same topic, heres a cool slice I wrote to figure out what field changed to activate the trigger, in case you dont REALLY want to fire triggers generating history when a certain set of fields is updated, as in the case of the darned replication id... (intended to be inserted into the trigger whose action you want to modify based on the field(s) updated)
BEGIN TRANSACTION
declare @NameToCheck varchar(8)
set @NameToCheck = 'val3' --this is the column name to check for singleton update, set to ModifiedDate,
--if we need to check any combination of values, just expand this routine to compensate
declare @checksum int --holds the int value of two raised to (the ordinal position of this column minus one)
declare @ChangeSum int --holds the int value of the COLUMNS_UPDATED varbinary
--------------------------------------------------------------------------------------------------------------------------
--this is the guts of it, as you can see it is only really one line
--generate the checksum
select
@checksum = power(2,colorder-1) --colorder index is 1 based, switch to 0 based
from syscolumns where
id = (select id --this is the column id of the column we want to watch
from sysobjects where
name = (select object_name(parent_obj) --the name of the table to which this trigger is attached
from sysobjects where xtype = 'tr' --the trigger type label
and name = object_name(@@PROCID) --the name of this trigger))
AND name = @NameToCheck --is the column we are watching
--if more than one field needs checked, just do again and add the check sums together... or do some other clever thing...
---------------------------------------------------------------------------------------------------------------------------
--recover the change sum, the value of the bit mask
set @ChangeSum = cast(COLUMNS_UPDATED()as int)
--create meta label for the history record
declare @newmeta varchar(200)
set @newmeta = '@CheckSum=' + cast(@CheckSum as varchar(8)) + ' '
set @newmeta = @newmeta + '@ChangeSum=' + cast(@ChangeSum as varchar(8))
--here we check if our pattern of change occurred
if(@CheckSum = @ChangeSum)
begin
set @newmeta = @newmeta + ' update only in ' + @NameToCheck + ' where we watched'
goto createHistory
end
--otherwise
set @newmeta = @newmeta + ' some other update'
createHistory:
INSERT INTO jonathan1H
( val1, val2, val3, modDate, meta )
SELECT val1, val2, val3, getdate(), @newmeta
FROM deleted
COMMIT TRANSACTION