CODEWITHSUNDEEP
sql-servertriggers
Ramesh Somalagari
Ramesh Somalagari

Reputation: 535

How to determine if insert or update

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

Upvotes: 1

Views: 12887

Answers (5)

spoorthi vaidya
spoorthi vaidya

Reputation: 479

create or replace trigger comp
before
insert or delete or update
on student
referencing old as o new as n
for each row
begin
  if deleting then
           insert into student_backup values
      (:o.studid,:o.studentname,:o.address,:o.contact_no,:o.branch,sysdate);
  end if;
  if inserting then
        insert into student_backup values
      (:n.studid,:n.studentname,:n.address,:n.contact_no,:n.branch,sysdate);
 end if;
  if updating then
       insert into student_backup values
      (:o.studid,:o.studentname,:o.address,:o.contact_no,:o.branch,sysdate);
  end if;
end comp;

Upvotes: 0

Lee Derting
Lee Derting

Reputation: 306

Try this code for trigger for INSERT, UPDATE and DELETE. This works fine on Microsoft SQL SERVER 2008

if (Select Count(*) From inserted) > 0 and (Select Count(*) From deleted) = 0
begin
   print ('Insert...')
end

if (Select Count(*) From inserted) = 0 and (Select Count(*) From deleted) > 0
begin
   print ('Delete...')
end

if (Select Count(*) From inserted) > 0 and (Select Count(*) From deleted) > 0
begin
   print ('Update...')
end

Upvotes: 2

SamoanProgrammer
SamoanProgrammer

Reputation: 954

The easiest way to solve this problem would be to have two triggers, one for the insert and one for the update.

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

END

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

Upvotes: 2

Rawheiser
Rawheiser

Reputation: 1220

Let SQL Server be SQL Server, and have it do the work for you!

Create separate triggers for each change event (insert,update and/or delete). Put the logic for each into the trigger that needs it. No need to have to check for the event type.

And don't call a procedure unless it is quick, fast, and can't block others.

Upvotes: 2

Tab Alleman
Tab Alleman

Reputation: 31775

On an INSERT, the virtual DELETED table will be empty.

Upvotes: 0

Related Questions