AFTER triggers in SQL Server can only be defined on tables. They are called AFTER triggers because they only execute after a DML operation (INSERT, UPDATE, DELETE) successfully passed all constraints on a table. If a DML statement fails those constraints an AFTER trigger won’t get a chance to execute.
Last time in our constraint blog we said that constraint could help us for data validation. It is a good idea to use constraint but we can’t always have the desired flexibility with constraint. Let us see how after triggers could help us to relax this restriction by creating an after trigger to work as a primary key constraint.
In the next example we will see that instead of returning error whenever a user passes a duplicate value for our primary key column we want to insert it to the table and insert it to an audit table to be validated later on.
if object_id('customers','U') is not null drop table customers; go CREATE TABLE customers ( id INT , firstName VARCHAR(20), lastName VARCHAR(20) ); go if object_id('customerAudit','U') is not null drop table customerAudit; go CREATE TABLE customerAudit ( id INT , firstName VARCHAR(20), lastName VARCHAR(20) ); go -- create inistead of delete trigger on customers table if object_id('tr_aft_ins_customers','TR') is not null drop trigger tr_aft_ins_customers; go create trigger tr_aft_ins_customers on dbo.customers after INSERT --you could use for in place of after as, for INSERT as begin if ( @@rowcount = 0 ) RETURN; --this will exit the trigger if no row is updated by DML set nocount on; if object_id('tempdb.sys.#inserted') is not null drop table #inserted; select * into #inserted from inserted if exists( select 1 from #inserted i where exists (select count(*) from customers where customers.id = i.id having count(*)>1)) begin insert into customerAudit(id,firstName, lastName) select i.id, i.firstName,i.lastName from #inserted i; end end return; --=======test1======== --assign values to customers for test insert into customers (id,firstName, lastName) values(1,'John','Doe'); insert into customers (id,firstName, lastName) values(1,'Jane','Doe'); go |
--check customers table select * from customers; --check audit table select * from customerAudit; go |
If we decide to be more restrictive as primary key constraint we could also change our trigger to return an error message when every a duplicate value inserted.
alter trigger tr_aft_ins_customers on dbo.customers after INSERT as begin if ( @@rowcount = 0 ) RETURN; --this will exit the trigger if no row is updated by DML set nocount on; if object_id('tempdb.sys.#inserted') is not null drop table #inserted; select * into #inserted from inserted if exists( select 1 from #inserted i where exists (select count(*) from customers where customers.id = i.id having count(*)>1)) throw 50001,'customer id already exists',0; end return; --=======test2======== truncate table customers; go --assign values to customers for test insert into customers (id,firstName, lastName) values(1,'John','Doe'); insert into customers (id,firstName, lastName) values(1,'Jane','Doe'); go |
--check customers table select * from customers; go |
Read More
http://technet.microsoft.com/en-us/library/ms189799.aspx