5

Whenever INSERT is happened in the CUSTOMER table,I need to call the "StoredProcedure1"and UPDATE is happend in the CUSTOMER table,I need to call the "StoredProcedure2" in the Trigger. How to determine if insert or update in the trigger from SQL Server 2008.

Some one can please help me how to solve?

Code:

CREATE TRIGGER Notifications ON CUSTOMER
FOR INSERT,UPDATE
AS
BEGIN
DECLARE @recordId varchar(20);
set @recordId= new.Id;
    //if trigger is insert at the time I call to SP1
        EXEC StoredProcedure1 @recordId
    //if trigger is Upadeted at the time I call to SP2
        EXEC StoredProcedure2 @recordId
END

1 Answer 1

10

Since it's only UPDATE,INSERT you can say:

IF EXISTS (SELECT 1 FROM deleted)
  -- update
ELSE
  -- insert

You have a bigger problem, though. There is no such thing as new.Id, and an insert or update can affect multiple rows (in some platforms, triggers fire per row; in SQL Server, they fire per operation). So you need to either:

  1. Use a loop to call the stored procedure for all of the RecordId values in inserted.
  2. Stop using a stored procedure for this and perform whatever logic it does inside the trigger, as a set-based operation.
3
  • Wouldn't be easier to have separate triggers for INSERT and UPDATE? Sep 27, 2014 at 11:57
  • 1
    @ypercube maybe, I don't know that it's necessary and depends on other things (how much logic applies to both, maintenance to multiple objects, etc). When I've had to modify triggers it's usually all not just for one specific action. Sep 27, 2014 at 12:09
  • @Aaron Bertrand,Thanks but I am unable to handle the multiple row see this link: stackoverflow.com/questions/26043106/… Oct 3, 2014 at 7:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.