SQLTeam.com | Weblogs | Forums

Conditional trigger


#1

I am trying to create a trigger with an if else. The trigger is on inserts from two different stored procedures. Both procedures insert into the same table. Then I want the trigger to log the transaction into another table. The last part is there are two different tables with numbers that need to be incremented based on which procedure is run. So I basically have:

IF EXISTS (Select top(1) * from SDG_CWF where getdate() = status_date and sproc = 'sproc1')
BEGIN
INSERT INTO Log
(Date, Task, Status, sproc)
VALUES
(getdate(), 'Copy', Import Successful', sproc1)

UPDATE Date1
SET Num1 = Num1 + 1
END

ELSE

IF EXISTS (Select top(1) * from SDG_CWF where getdate() = status_date and sproc = 'sproc2')
BEGIN
INSERT INTO Log
(Date, Task, Status, sproc)
VALUES
(getdate(), 'Copy', Import Successful', sproc2)

UPDATE Date2
SET Num2 = Num2 + 1
END

Of course my logic is faulty here because once sproc1 is run the first condition is always going to be true. So the number in Date2 never increments and the wrong values are inserted into Log.

Does anyone have any ideas on how I can handle this? I asked in another thread I think and was told as I suspected that you can not have a trigger execute based on which stored procedure does the insert. The only thing I can think of is to make sure that sproc2 always runs first so the first condition will never be true but that is far from ideal.

thanks


#2

Why not have the stored procedure do the logging?


#3

My stored procedure is executing an SSIS package. This is my first time attempting this and I thought since my transaction is in the package it would be easier to handle through the trigger.

Not sure how to have the stored procedure run the package then insert to one table and update another.


#4

Have each proc set the same relative byte # in CONTEXT_INFO to a specific value -- different for each one of course :smiley: -- and the trigger can check that to know which proc fired it.

For example:

--proc1
...
SET CONTEXT_INFO 0x01
INSERT INTO ... --statement that fires trigger
SET CONTEXT_INFO 0x00
...

--proc2
...
SET CONTEXT_INFO 0x02
INSERT INTO ...
SET CONTEXT_INFO 0x00
...

--...additional proc(s) if needed: SET CONTEXT_INFO 0x50 ...

GO
CREATE TRIGGER ...
ON ...
AFTER INSERT
AS
SET NOCOUNT ON;
IF SUBSTRING(CONTEXT_INFO, 1, 1) = 0x01
BEGIN
--code for proc1
END --IF
ELSE
IF SUBSTRING(CONTEXT_INFO, 1, 1) = 0x02
BEGIN
--code for proc2
END --IF
...
GO --end of trigger


#5

Yea - I did try this without success but I think I see where I may have gone wrong.

Thanks.