Is there a definitive way to determine if a DML command was issued from a stored procedure?
I inherited a SQL Server 2008 database that the calling applications can access through stored procedures.
Each table in the database has a shadow audit table that records Insert / Update / Delete operations.
Performance testing on populating the audit tables showed that inserting audit records using statements OUTPUT
was 20% faster than using triggers, so this was implemented in stored procedures.
However, since this project cannot track changes made directly to tables using DML statements issued directly against tables, triggers have also been implemented that use a value @@NESTLEVEL
to determine whether or not to fire a trigger (assuming that all DML executed by stored procedures , will have @@NESTLEVEL
> 1). those. the body of the startup code looks something like this:
IF @@NESTLEVEL = 1 -- implies call is direct sql so generate history from here
BEGIN
... insert into audit table
This construct is flawed because it will not track updates that execute DML statements in dynamic SQL, or in any other context that @@NESTLEVEL
displays above 1.
Can anyone suggest a completely reliable method that we can use in triggers to execute them only if not called by a stored procedure?
Or is it (as I suspect) not possible?
a source to share
Use CONTEXT_INFO (Transact-SQL) . In the procedure, set a value to warn the trigger not to write anything:
--in the procedure doing the insert/update/delete
DECLARE @CONTEXT_INFO varbinary(128)
SET @CONTEXT_INFO =cast('SkipTrigger=Y'+REPLICATE(' ',128) as varbinary(128))
SET CONTEXT_INFO @CONTEXT_INFO
--do insert/update/delete that will fire the trigger
SET CONTEXT_INFO 0x0
In the trigger, check the CONTEXT_INFO and determine if you need to do anything:
--here is the portion of the trigger to retrieve the value:
IF CAST(CONTEXT_INFO() AS VARCHAR(128))='SkipTrigger=Y'
BEGIN
--log your data here
END
for anyone just doing rogue insert / update / delete, they won't set CONTEXT_INFO and the trigger will write that change. You can get an idea of what value you put in CONTEXT_INFO like table name or @@ SPID etc. if you think the rogue code will try to use CONTEXT_INFO as well.
a source to share