Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TRIGGERS AFTER UPDATE,DELETE Expand / Collapse
Author
Message
Posted Thursday, June 24, 2010 10:17 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Thursday, June 24, 2010 10:42 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 10, 2014 11:48 AM
Points: 74, Visits: 161
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
Posted Monday, June 28, 2010 12:05 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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 »

Add to briefcase

Permissions Expand / Collapse