SQL Server Trigger Examples
Published: 28th August 2015
As mentioned in my previous post, we have 2 types of Trigger available in SQL Server… AFTER and INSTEAD OF.

In this post I’m going to go through a few examples to show syntax and a couple of uses.

These examples will cover both types and also reference both the Inserted and Deleted tables that Triggers offer.

I’ll start with the AFTER Trigger.

So, we’ll make a quick Person table and enter a few rows:

use testDB
go

if object_id('person') is not null drop table person
go

create table person
(
  
personID int identity,
  
firstName varchar(100),
  
lastName varchar(100)
)
go

insert into person
values ('Homer', 'Simpson'),
       (
'Marge', 'Simpson'),
       (
'Lisa', 'Simpson'),
       (
'Bart', 'Simpson'),
       (
'Maggy', 'Simpson') -- Note intentional spelling mistake
go


Now what we want is to ensure that any Updates to this table are tracked. So we’ll create a logging table to collect some pertinent information:

if object_id('personLogging') is not null drop table personLogging
go

create table personLogging
(
  
personID int,
  
firstName varchar(100),
  
lastName varchar(100),
  
userName varchar(100),
  
changeTime datetime
)
go


Now we just need to connect our Person table to the Logging table by using Triggers:

create trigger tr_personAfterUpdate
  
on person
   after
update
as
begin
   insert into
personLogging
  
select personID, firstName, lastName, SUSER_SNAME(), current_timestamp
  
from deleted -- the before row
end
go


Now let’s try these out:

update person
set firstName = 'Maggie'
where firstName = 'Maggy'
go

select *
from personLogging
go


There you can see how easy it is to use an AFTER Trigger to fire an event AFTER something happens. It also shows how easy it is to access the Inserted and Deleted tables.

Now let’s make a quick INSTEAD OF Trigger.

We’ll make a quick table containing some order numbers and prices:

use testDB
go

if object_id('prices') is not null drop table prices
go

create table prices
(
  
priceID int identity,
  
orderNo int,
  
price decimal(19, 2)
)
go


Annoyingly our application (which we can’t change) insists on sending us costs in Net whereas we want the Gross figure, so we’re going to place a Trigger to change what we’re being given by the application and, instead, inserting different values based on a calculation:

create trigger tr_priceTax
  
on prices
   instead
of insert
as
begin
   insert into
prices
  
select orderNo, price * 1.2
  
from inserted
end
go


Now let’s try it out:

insert into prices
values (1, 100.00),
       (
2, 956.48)
go

select *
from prices
go


As a last example, we’ll place an INSTEAD OF Trigger which will allow Inserts and Deletes, but will ban all Updates from happening:

create trigger tr_priceUpdateBan
  
on prices
   instead
of update
as
begin
   rollback tran

   print
'Updates are not allowed on this table.'
end
go

update prices
set price = price * 100
go


Crude (as we don’t like receiving Level 16 errors), but it does work.

As a whole, Triggers are quite useful when used correctly. A lot of people will say that they shouldn’t be used due to performance or complexity and, to be honest, if you can hide logic in Stored Procedures instead, then that should be your route… but sometimes the only option is a Trigger.
Comments:
NB: Comments will only appear once they have been moderated.