TRIGGERS AFTER UPDATE,DELETE

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Tom Thomson » TRIGGERS AFTER UPDATE,DELETE

TRIGGERS AFTER UPDATE,DELETE

Rate Topic

Display Mode

Topic Options

Author

Message

john.david.birch

Posted Thursday, June 24, 2010 10:17 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 08, 2012 1:46 AM
Points: 148, Visits: 31

I have a need for triggers during UPDATE and DELETE. The need in each case is "almost" identical, but there is a slight difference.

Do I have to write two triggers ? or can I introduce some logic in the trigger definition ?

example

Create Trigger dbo.MyCombinedTrigger on dbo.MyTable
...
AFTER UPDATE, DELETE
...

Begin
/*
Common code for "Update" and "Delete" operations
*/

if @@mysteryVariable = DELETE
begin
/* "Delete" specific code */
end

End

Is there such a "@@mysteryVariable" or another technique to do this ?

John

Post #942563

JBZ

Posted Thursday, June 24, 2010 10:42 AM

Valued Member

Group: General Forum Members
Last Login: Monday, April 15, 2013 5:03 PM
Points: 74, Visits: 151

John,

The easiest way to find out if this is UPDATE or DELETE event is to check rows in inserted table: for UPDATE event there will be rows in both tables - inserted and deleted, for DELETE event you will find rows only in deleted table.

Is this what you are looking for?

JBZ

Post #942574

john.david.birch

Posted Monday, June 28, 2010 12:05 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 08, 2012 1:46 AM
Points: 148, Visits: 31

Yes, thank you very much :)

Just in case anyone else is wondering. Here is my test code:

create table JDBT1 ( a int, b int);
create table JDBT2 ( i int, d int);
go
create trigger tr1 on JDBT1 after insert, update, delete
as
begin
declare @qI int,@qU int,@qD int;
select @qI = count(*) from inserted;
select @qD = count(*) from deleted;
insert into JDBT2 ( i,d) values (@qI, @qD)
end
go

insert into JDBT1 (a,b) values( 1,2);
update JDBT1 set b=3;
delete from JDBT1;

select * from JDBT2;

which gives a nice little result set of
1, 0
1, 1
0, 1

John

Post #943659

« Prev Topic | Next Topic »

Permissions