I have several layers of nested procedures and when it reached trigger time, @@spid returned the top executing sproc rather than the update sproc, so it failed out. The only way I could make it work was to add a context_info check in the trigger against every procedure that might be at the top of the hierarchy.

Of course, this allows direct upserts in any procedure along the hierarchy, but at least it protects the table from business analysts with sysadmin rights who use "open table" all the time.

Having standards (like good security) is nice if you can enforce them, but my shop exists somewhere in the wild west!

Here's my latest implementation within the trigger:

if exists (

select *

from sys.dm_exec_sessions as SYS

where not exists (

select *

from dbo.v_lookup_context_info as sub

where sub.context_info_as_varchar

= cast( SYS.context_info as varchar )

this_is_a_close_paren_that_turned_into_an_emoticon

and session_id = @@spid

)

begin

raiserror('You can not write to XXXX outside of XXXX',16,1)

return

end