June 24, 2010 at 10:17 am
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
June 24, 2010 at 10:42 am
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
June 28, 2010 at 12:05 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply