[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

pass parameters to a trigger

Posted on 2006-05-08
11
Medium Priority
?
522 Views
Last Modified: 2011-08-18
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
0
Comment
Question by:pdvd
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16629513
You can't pass a parameter to a trigger
0
 
LVL 6

Expert Comment

by:hans_vd
ID: 16629586
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
 

Author Comment

by:pdvd
ID: 16629764
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16629799
you can of course use a table in the database putting some data into it which the trigger can use.
0
 

Author Comment

by:pdvd
ID: 16629928
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16629943
>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
 
LVL 6

Expert Comment

by:hans_vd
ID: 16631559
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
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 400 total points
ID: 16632107
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
 

Author Comment

by:pdvd
ID: 16840436
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
 
LVL 20

Expert Comment

by:Venabili
ID: 16964083
It is almost abandoned again
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question