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


Access variables values from Trigger


Access variables values from Trigger

Author
Message
john.david.birch
john.david.birch
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 Visits: 31
providing you keep the number series separate ,
and can easily distinguish between them ,
and provided loads of people don't login using the same account,
and that these are likely to be true next week, month, year,..
minimyme
minimyme
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 13
Why not just insert the row into the audit table first from SP directly and delete the actual table since you have all the needed info within SP itself? Using the trigger and context_info is complicating the code for no good reason that I can see based on the info in the article.

I'm a big fan of KIS (keep it simple) principal. If you can avoid the triggers in the first place, absolutely avoid them.
david.wright-948385
david.wright-948385
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: 1686 Visits: 991
minimyme (6/9/2010)

I'm a big fan of KIS (keep it simple) principal. If you can avoid the triggers in the first place, absolutely avoid them.


Auditing is normally performed in triggers

1) for security purposes so the action is audited however the action is performed and (pretty much) whoever it is performed by and

2) because the auditing logic is decoupled from the deletion logic.

Dave.
Tejas Shah SQLYoga
Tejas Shah SQLYoga
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 240
Hi Mahesh,

Context_info is unique per session. So, when multiple users are working on product, system will create separate session for each session. So there is no any issue.

Thanks,
Tejas
SQLYoga.com
Tejas Shah SQLYoga
Tejas Shah SQLYoga
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 240
Why not just insert the row into the audit table first from SP directly and delete the actual table since you have all the needed info within SP itself? Using the trigger and context_info is complicating the code for no good reason that I can see based on the info in the article.

I'm a big fan of KIS (keep it simple) principal. If you can avoid the triggers in the first place, absolutely avoid them.

Tejas:
In Application, it might happen that we allow the user to delete record from multiple pages OR sometimes some record will be deleted based on business condition. In any case, we need to make sure that we have audit data, who deleted and the reason?

To make sure that we have audit data, trigger is a place where we can write a code to keep audit data when any information of that table is being changed.

Thanks,

Tejas
SQLYoga.com
UMG Developer
UMG Developer
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4002 Visits: 2204
Interesting article, thanks. I'm not sure I will end up using it, but I will keep it in the back of my mind for when an issue might come up.
forjonathanwilson
forjonathanwilson
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 33
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
dbishop
dbishop
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 248
In light of the other posts, this is just to clarify a comment you made in your article. You stated, "the first character is removed and then converted into an XML documnet." The subsequent code line was:
SELECT @XML = CAST(REPLACE(@VarXML,CHAR(0),'') AS XML)


However, the CHAR(0) does not replace the first character, but defines an ASCII 000 character, sometimes referred to as a NULL (not associated with a SQL NULL value) or end-of-document character.

Thus, this line of code is replacing an ASCII 0 with an empty string character (which is probably located at the end of the string, not the first character.
aszendi
aszendi
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 7
Hello,
I think the example is not the luckiest as once you have a stored proc that deletes the record it could take care of the auditing part as well.
Adding auditing to a DB where DMLs commands are not wrapped into storedprocs is more tricky.

Áron-Hungary
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