>>As long as the trigger is enabled you can make sure that the logic will work.
Maybe could also create a database-level trigger using the same technique, to make sure that the table wasn't altered or dropped outside of an approved way, to prevent this?
Great article, thank you...
>L<
I thought maybe we could do this with EVENTINFO() in a trigger, but the calling proc isn't included in the schema (would be nice!).
Looking at the docs, it seems maybe you could use context info more pervasively in a system and then do something like this:
SELECT context_info AS MyCtxInfo
FROM
WHERE
... ? but I am not sure, I don't understand how CURRENT_REQUEST_ID() works or whether you will ever see more than one result from the above code.
Anyone??
Very useful article ! I don't know about this until now.
Thank you. David N Nguyen.
This procedure wouldn't work for me in SQL 2005. Casting the Context_Info as varchar did not work. I had to cast the string as binary(128).
I had to rewrite the trigger as following:
IF
Also, in SQL 2005, Context_Info can be Null which it is for an ad hoc query. A Null Context_Info would not be caught by the trigger since Null is neither equal nor not equal to anything. So, I further had to modify it to account for null:
Of course, this doesn't really stop me from doing ad hoc queries, as I can still do this:
Set
Delete
Where
SET
Informative article... i was not aware of the CONTEXT_INFO.
I feel that use of CONTEXT_INFO in the scripts will be creating dependencies with the developers i.e., it will be mandatory to use this in each script. Setting up the permissions will be the correct and secure option.
