AFTER triggers in SQL Server

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