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