home
-- Triggers 1 - A Beginners Guide
Author Nigel Rivett
Triggers 1 - A Beginners Guide.
Auther Nigel Rivett
A trigger can be created on a table which will fire whenever the data in that table is updated (insert, update, delete).
There are two types of trigger available
1. After triggers - these were the only type avaiable pre v2000 and are the default.
These triggers fire after the data is updated - hence if the data violates a constraint then the error will occur before the trigger fires.
It is possible to define multiple after triggeres on a table. These will all fire. The first and last trigger to firs can be set by sp_settriggerorder.
2. Instead of triggers.
These triggers fire instead of the update taking place and can be applied to updateable views. This means that the trigger can alter the data to fit in with database integrity but has the added complication that the trigger must apply the updates itself.
Of course only one instead of trigger is allowed on a table.
inserted and deleted logical tables
These tables are only available to the trigger. They cannot be referenced via stored procedures called from the trigger nor via dynamic sql.
The inserted table contains the before image of the rows and the deleted the after image.
Text/image fields are not available in these tables for after triggers but they are available to instead of triggers.
For an insert the inserted table will have entries and the deleted table will be empty.
For a delete the deleted table will have entries and the inserted table will be empty.
For an update both the inserted and deleted tables will have the same number of entries.
A common mistake is to assume that a trigger will fire once for each row updated and that the inserted and deleted tables will have one entry.
The trigger will fire once (excepting recursion) for the sql statement and the inserted/deleted tables will have entries for all rows affected.
Therefor:
declare @name varchar(100)
select @name = name from inserted
Will be incorrect unless the application enforces single row updates
Operations within a trigger must be set based using the primary key rather than variables.
Transactions
Every sql statement is atomic. The statement does not complete until all triggers have completed therefore the triggers all run as part of the same transaction as the sql statement.
This cab be seen by interrogating @@trancount within the trigger.
A rollback tran within a trigger will rollback all updates made as a consequence of the sql statement and cause no more triggers to fire.
It will in fact cause the termination of processing (but this may be release dependent).
Trigger with rollback
if exists (select * from sysobjects where id = object_id(N'[dbo].[trigtest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[trigtest]
go
create table trigtest (i_int_key int not null, j_int_key int not null, s_varchar varchar(10), t_char varchar(10), d_date datetime)
go
alter table trigtest add constraint pk primary key (i_int_key, j_int_key)
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tr_trigtest]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[tr_trigtest]
go
create trigger tr_trigtest on trigtest for insert, update, delete
as
select trancount = @@trancount
rollback tran
go
insert trigtest (i_int_key, j_int_key, s_varchar, t_char, d_date)
select 1, 1, 'hello', 'goodbye', '20000101'
select @@error, @@rowcount
Result
@@trancount displayed = 1
select @@error, @@rowcount is not executed
no rows inserted into table.
Recursion
If a trigger updates the table then that update will cause the trigger to fire again. This can be avoided by setting the recursive triggers database option to false.
If a trigger updates another table and a trigger on that table updates the first then the trigger will fire again. This will not be preveneted by recursive triggers being false but can be avoided by seting the server option nested triggers to 0. Naturally this will also disable recursive triggers for ecery database.
Triggers can only be nested to a maximum of 32 levels after which an error will occur.
The nest level of the trigger is held in @@nestlevel.
Examples or recursion
Recusive trigger
Failure due to max nest level
if exists (select * from sysobjects where id = object_id(N'[dbo].[trigtest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[trigtest]
go
create table trigtest (i_int_key int not null, j_int_key int not null, s_varchar varchar(10), t_char varchar(10), d_date datetime)
go
alter table trigtest add constraint pk primary key (i_int_key, j_int_key)
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tr_trigtest]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[tr_trigtest]
go
create trigger tr_trigtest on trigtest for insert, update, delete
as
select @@nestlevel
update t
set s_varchar = convert(varchar(10),@@nestlevel)
from trigtest t
join inserted i
on t.i_int_key = i.i_int_key
and t.j_int_key = i.j_int_key
go
exec sp_dboption 'triggertest', 'recursive triggers', 'true'
go
insert trigtest (i_int_key, j_int_key, s_varchar, t_char, d_date)
select 1, 1, 'hello', 'goodbye', '20000101'
select @@error, @@rowcount
result
Returns @@nestlevel up to 32 then
Server: Msg 217, Level 16, State 1, Procedure tr_trigtest, Line 4
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
No update is made to the table.
select @@error, @@rowcount is not executed
Checking nest level to prevent max nest level violation
if exists (select * from sysobjects where id = object_id(N'[dbo].[trigtest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[trigtest]
go
create table trigtest (i_int_key int not null, j_int_key int not null, s_varchar varchar(10), t_char varchar(10), d_date datetime)
go
alter table trigtest add constraint pk primary key (i_int_key, j_int_key)
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tr_trigtest]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[tr_trigtest]
go
create trigger tr_trigtest on trigtest for insert, update, delete
as
select @@nestlevel
if @@nestlevel <= 10
begin
update t
set s_varchar = convert(varchar(10),@@nestlevel)
from trigtest t
join inserted i
on t.i_int_key = i.i_int_key
and t.j_int_key = i.j_int_key
end
go
exec sp_dboption 'triggertest', 'recursive triggers', 'true'
go
insert trigtest (i_int_key, j_int_key, s_varchar, t_char, d_date)
select 1, 1, 'hello', 'goodbye', '20000101'
select @@error, @@rowcount
select * from trigtest
result
Returns @@nestlevel up to 10 then
Insert completes successfully with s_varchar = 10
select @@error, @@rowcount returns 0, 1
Global Variables
@@identity, scope_identity
If the trigger inserts into a table with an identity then @@identity will reflect that identity.
Scope_identity() will show the identity of the table which fires the trigger.
Normally an insert into a table without an identity will set @@identity to null. This is not the case with an insert in a trigger.
@@rowcount
@@rowcount will be set within the trigger to reflect the number of rows affected by the last statement.
After the trigger has completed it will the number of rows affected by the firing statement.
-- Trigger with insert into table with identity
if exists (select * from sysobjects where id = object_id(N'[dbo].[trigtest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[trigtest]
go
create table trigtest (i_int_key int not null identity(1,1), j_int_key int not null)
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tr_trigtest]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[tr_trigtest]
go
create trigger tr_trigtest on trigtest for insert, update, delete
as
select 'start of trigger', id = @@identity, scopeidentity = scope_identity()
insert trigtest (j_int_key)
select 2
select 'end of trigger', id = @@identity, scopeidentity = scope_identity()
go
insert trigtest (j_int_key) select 1
select * from trigtest
select 'after insert', id = @@identity, scopeidentity = scope_identity()
results
id scopeidentity
---------------- ---------------------------------------- ----------------------------------------
start of trigger 1 NULL
id scopeidentity
-------------- ---------------------------------------- ----------------------------------------
end of trigger 2 2
i_int_key j_int_key
----------- -----------
1 1
2 2
id scopeidentity
------------ ---------------------------------------- ----------------------------------------
after insert 2 1
-- Trigger with insert into table without identity
if exists (select * from sysobjects where id = object_id(N'[dbo].[trigtest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[trigtest]
go
create table trigtest (i_int_key int not null identity(1,1), j_int_key int not null)
go
if exists (select * from sysobjects where id = object_id(N'[dbo].[trigtest2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[trigtest2]
go
create table trigtest2 (i_int_key int not null, j_int_key int not null)
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tr_trigtest]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[tr_trigtest]
go
create trigger tr_trigtest on trigtest for insert, update, delete
as
select 'start of trigger', id = @@identity, scopeidentity = scope_identity()
insert trigtest2 (i_int_key, j_int_key)
select 2,2
select 'end of trigger', id = @@identity, scopeidentity = scope_identity()
go
insert trigtest (j_int_key) select 1
select * from trigtest
select * from trigtest2
select 'after insert', id = @@identity, scopeidentity = scope_identity()
Results
id scopeidentity
---------------- ---------------------------------------- ----------------------------------------
start of trigger 1 NULL
id scopeidentity
-------------- ---------------------------------------- ----------------------------------------
end of trigger 1 NULL
i_int_key j_int_key
----------- -----------
1 1
i_int_key j_int_key
----------- -----------
2 2
id scopeidentity
------------ ---------------------------------------- ----------------------------------------
after insert 1 1
Trigger which inserts 2 rows
if exists (select * from sysobjects where id = object_id(N'[dbo].[trigtest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[trigtest]
go
create table trigtest (i_int_key int not null identity(1,1), j_int_key int not null)
go
if exists (select * from sysobjects where id = object_id(N'[dbo].[trigtest2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[trigtest2]
go
create table trigtest2 (i_int_key int not null, j_int_key int not null)
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tr_trigtest]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[tr_trigtest]
go
create trigger tr_trigtest on trigtest for insert, update, delete
as
select 'start of trigger', Rcount = @@rowcount
insert trigtest2 (i_int_key, j_int_key)
select * from (select i = 2, j = 2 union select 3,3) as a
select 'end of trigger', Rcount = @@rowcount
go
insert trigtest (j_int_key) select 1
select 'after insert', Rcount = @@rowcount
select * from trigtest
select * from trigtest2
select 'after insert', id = @@identity, scopeidentity = scope_identity()
Results
Rcount
---------------- -----------
start of trigger 1
Rcount
-------------- -----------
end of trigger 2
Rcount
------------ -----------
after insert 1
i_int_key j_int_key
----------- -----------
1 1
i_int_key j_int_key
----------- -----------
2 2
3 3
id scopeidentity
------------ ---------------------------------------- ----------------------------------------
after insert 1 1
home