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
Tejas Shah SQLYoga
Tejas Shah SQLYoga
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 236
Comments posted to this topic are about the item Access variables values from Trigger
john_wong_ca
john_wong_ca
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 199
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.:-)



Fiacre Lenehan
Fiacre Lenehan
Old Hand
Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)

Group: General Forum Members
Points: 370 Visits: 160
An alternative is to use a temp table created by the proc which will be visible to the trigger
david.wright-948385
david.wright-948385
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1242 Visits: 979
A very good article to show how context_info works and how it can be applied.

If the 128 bytes of context_info is insufficient for the purpose, a CLR could be used to first register persistent, session related context information, then retrieve it.

I'm not so sure the auditing function should rely on cooperation from the process that is deleting the rows though :-)
David Rueter
David Rueter
SSC Veteran
SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)

Group: General Forum Members
Points: 262 Visits: 574
Nice article, and interesting use of CONTEXT_INFO.

Be careful that nothing else depends on CONTEXT_INFO though! (Overwriting CONTEXT_INFO could have unintended impact on other routines that rely on session information stored there.)


While not suitable for deletions or for minimally-invasive auditing, another approach to passing in data to a trigger is to use an INSTEAD OF trigger on a view:

Create a view that returns all the table columns, plus adds an additional "parameter" column or columns.

Then you can create an INSTEAD OF INSERT trigger (or INSTEAD OF UPDATE) on the view.

This allows you to perform the insert into the view (instead of the underlying table), providing values for the "parameter" column--and the trigger has the ability to read the "parameter" column.

(To clarify, the "parameter" column doesn't really get stored anywhere or returned by the view: it is in the view solely to allow a parameter value to be passed in on updates or inserts.)

This approach assumes SQL 2005, but provides the benefit of efficiently passing in parameters for each row (as opposed to per-execution), and allows the parameter(s) to be of any data type.

This approach doesn't do any good for deletes, and is probably not ideal for minimally-invasive passive auditing...but I've used it extensively with great success.
hardikshah307
hardikshah307
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 2
nice article
honza.mf
honza.mf
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1939 Visits: 1323
Nice idea. But we have context info already used for other data. It is possible to this data into a variable, use this trick and restore them. I'm afraid of some subprocess that will want the original.



See, understand, learn, try, use efficient
© Dr.Plch
simonellistonball
simonellistonball
SSC Veteran
SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)

Group: General Forum Members
Points: 249 Visits: 60
A nice idea, but your trigger relies on the fact that you would only ever delete one product at a time. You'd have to be very careful about other procedures which perform deletes and may not fill the context_info.

A global temporary table, or even a persistent table with a key based on the connection id, or better the transaction id from dm_tran_current_transaction would be better, and allow you to delete multiple rows.
sknox
sknox
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2602 Visits: 2829
simon.ellistonball (6/9/2010)
A nice idea, but your trigger relies on the fact that you would only ever delete one product at a time.


No, it doesn't. It does rely on having only one UserID and Reason for a given batch of deletes, and it does rely on context_info being XML, but the trigger as written should properly audit a deletion of multiple records in a batch if the previous conditions are met.
Mike Dougherty
Mike Dougherty
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 952
I can appreciate a get-it-done-today workaround, but wouldn't a better approach be to rewrite the trigger/application layers to provide better tier isolation? I assumed a trigger should be encapsulated such that it can provide integrity validation of data based solely on RI rules; it should not need information provided by the user in the SP scope. Likewise the business logic probably should be enforced at the interface between data and the application (aka SP). Pushing this requirement all the way back to the UI, the application should not even call for a delete without proper authentication and "reason" coding.

I also wonder what kind of performance you can expect from the xml->CONTEXT_INFO->xml happening per transaction. Granted deletes may be infrequent but transactional overhead should be considered before it becomes a performance problem.

I guess I don't understand why the auditing is done in the trigger rather than directly in the SP. I have seen where the trigger provides a single place to hook actions on the data when multiple SP already exist that act on the table. This solution would still require visiting each SP to ensure the context_info is properly set.

Thanks for the article. I don't think I would have learned about the context_info myself.
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