pass parameters to a trigger

Hi,

I was wondering if anyone found a way to pass parameters to a trigger from a procedure calling the update / delete / insert statement.

I want this because my trigger does things it needs to do almost always, but... some procedures need to update a table without calling the trigger, or even better, somehow set a param that the trigger checks, and acts depending on the value of the param.

I do not want to use a field in the table to achief this!

Any thoughts welcom
pdvdAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
I'm 99% certain that context data *is* available from within a trigger, so you can use SET CONTEXT_INFO to control trigger processing:


SET CONTEXT_INFO 0xEE  --exit trigger immediately!
UPDATE ...

SET CONTEXT_INFO 0x00  --normal trigger processing
UPDATE ...

SET CONTEXT_INFO 0x01  --special trigger processing
UPDATE ...


CREATE TRIGGER ...
ON ...
AFTER ...
AS
IF @@ROWCOUNT = 0
    RETURN
DECLARE @context_info VARBINARY(128)
SELECT @context_info = context_info
FROM master.dbo.sysprocesses WTH (NOLOCK)
WHERE spid = @@SPID
IF @context_info = 0xEE
    RETURN
IF @context_info = 0x00
BEGIN
    ...
END
ELSE
IF @context_info = 0x01
BEGIN
END
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
You can't pass a parameter to a trigger
0
 
hans_vdCommented:
I suggest that you create a procedure that handles the trigger logic.  You can call that procedure after any insert/update/delete you want and you can pass parameters to it as much as you want.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
pdvdAuthor Commented:
Hans_vd,

Thank you for your comment, I know I could use a SP to handle the logic. But that would mean that  I would have to have control over all software doing updates, and change all procedures etc. updating this table.

all this for the exceptional occasion that I want the trigger not to run.

I was hoping to find a way using a global variable or temp table or something like that...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you can of course use a table in the database putting some data into it which the trigger can use.
0
 
pdvdAuthor Commented:
I was hoping to find a way that stays withing the scope of the calling statement

e.g.

In my SP I do:

   SELECT 1 as tempParam INTO #myTemp
   UPDATE testTable SET field = 'value'

In my trigger I would then test if the table #myTemp exists, and if so, do, or do not do certain things...

BUT.... this does not work... a temp table created in the SP is not available in the trigger on testTable.

if I would use a global temp table or the like, I would risk that the value in the global table was set, and before my update executes a different user / process does an update on the table (which does not mean to set the tempParam) and things would be a mess...

Maybe.... I just want the impossible...


 
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I was hoping to find a way that stays withing the scope of the calling statement
as you found out that is not possible, as the trigger has a different context than the main update.
0
 
hans_vdCommented:
How about you store the value in a global temp table together with the username of the user that is executing the procedure?  You can retrieve the username in the trigger and get the correct value from the global temp table.
0
 
pdvdAuthor Commented:
I seem to have mist the last added most, my appologies.

I'll look in to it straight away, and close the question myself.

0
 
VenabiliCommented:
It is almost abandoned again
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.