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.