This one is a bit of a no-brainer and is really just for me, but if it does help anyone out there then I suppose it was worth posting.

I had to write a trigger today to monitor changes to data in a table. The client application does DML for the login language of the current user and the trigger has to do the same for the other languages for which the system was configured, so that clients logging on using another language also see the current data. For example: if a user in England inserts a new part number (along with description etc…), the same information has to be available for users in Romania and Germany. The same applied to updated and deleted data.

I generally don’t write many triggers, as I have had bad (really bad) experiences in the past, with undocumented triggers. But, the requirement was that a trigger be implemented.

Because the code that has to be run varies depending on which DML event was executing the trigger, I had to establish which event was occurring. I could have written three triggers, once each for insert, update and delete but I didn’t want to have three db-objects essentially linked by functionality and purpose, but acting as separate entities. This can get rather confusing when someone else has to maintain and or support the database and application using the database. (I have yet to meet the support-technician that reads system documentation at 3am). So I figured: Just give them one trigger to deal with.

This is how I went about finding out which DML event was firing the trigger:

create trigger TR_TriggerName
on Table
after insert, update, delete
as

declare @Update bit = 0
declare @Insert bit = 0
declare @Delete bit = 0

if exists (select * from inserted) --could be update or insert, but not delete
begin
	if exists (select * from deleted) --this is an update statement
	begin
		select	@Update = 1,
				@Insert = 0,
				@Delete = 0;
	end
	else --this is an insert statement
	begin
		select	@Insert = 1,
				@Update = 0,
				@Delete = 0;
	end
end
else --can only be a delete because nothing was in inserted
begin
	select	@Delete = 1,
			@Insert = 0,
			@Update = 0;
end

if (@Insert = 1)
begin
--insert code for insert procedure
end
else if (@Update = 1)
begin
--insert code for update procedure
end
else if (@Delete= 1)
begin
--insert code for delete procedure
end
go

2 Responses to “Insert, Update or Delete Trigger”

1. excelf-online says:

   July 2, 2010 at 06:45

   Many thanks.
2. Sean says:

   July 2, 2010 at 09:08

   You’re welcome! I hope you were able to use this post to your advantage.

Leave a Reply

Cancel Reply

Name (required)

Mail (will not be published) (required)

Web site

CAPTCHA Code *