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

migrated from serverfault.com Sep 26 '14 at 13:42

This question came from our site for system and network administrators.

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.
  • Wouldn't be easier to have separate triggers for INSERT and UPDATE? – ypercubeᵀᴹ Sep 27 '14 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. – Aaron Bertrand Sep 27 '14 at 12:09
  • @Aaron Bertrand,Thanks but I am unable to handle the multiple row see this link: stackoverflow.com/questions/26043106/… – Ramesh S Oct 3 '14 at 7:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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