SQL Server trigger example: One trigger vs. two for the same logic

Intro | Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6 | Part 7

Problem: You already have a trigger on the table. You have to add new functionality to the trigger. You wonder if performance will improve if you add functionality to the existing trigger or maybe the best option is to create another trigger. What would you do?

Solution: I ran the following example in order to compare performance and resources usage between the two methods for the same functionality: • Using only one trigger • Using two triggers

Create tables

-- Create 3 tables with the same schema:
create table Table_1
(Col_1 int identity (1,1) PRIMARY KEY,
 Col_2 varchar(10) NOT NULL,
 Col_3 datetime NOT NULL default getdate())
go
select convert(int,Col_1) as Col_1, -- I use the convert to eliminate the 
                                                      Identity property.
         Col_2, Col_3 into Table_2 
from Table_1
go
select convert(int,Col_1) as Col_1, Col_2, Col_3 
into Table_3 
from Table_1
go

Common script to run

-- Truncate 3 tables
truncate table Table_1
truncate table Table_2
truncate table Table_3
go
-- Clean SQL SERVER memory:
dbcc dropcleanbuffers
go
-- Run 10000 inserts
declare @i int
set @i = 1
while @i <= 10000
begin
 insert into Table_1 (Col_2) values(convert(varchar(10),@i))
 set @i = @i + 1
end
go
-- Create another table to hold the 10000 rows:
Select * into Table_4 from Table_1
go
-- Truncate and clean memory again:
truncate table Table_1
truncate table Table_2
truncate table Table_3
go
dbcc dropcleanbuffers
go
-- Run 10000 inserts at once:
insert into Table_1 (Col_2, Col_3) 
select Col_2, Col_1
from Table_4
order by Col_1
Go

Case 1: Create one trigger

-- Create one trigger with two inserts:
create trigger trg_I_Table_1 
ON Table_1 
FOR INSERT
as
insert into Table_2 (Col_1, Col_2, Col_3) select Col_1, Col_2, Col_3 
from inserted
insert into Table_3 (Col_1, Col_2, Col_3) select Col_1, Col_2, Col_3 
from inserted
go

Case 2: Create two triggers

-- Create two triggers – one with each command:
create trigger trg1_I_Table_1
ON Table_1 
FOR INSERT
as
insert into Table_2 (Col_1, Col_2, Col_3) select Col_1, Col_2, Col_3 
from inserted
go
create trigger trg2_I_Table_1
ON Table_1 
FOR INSERT
as
insert into Table_3 (Col_1, Col_2, Col_3) select Col_1, Col_2, Col_3 
from inserted
go

I ran the common script six times for each case -- three times cleaning SQL Server memory and three times without cleaning it -- and here are the results I received from SQL Profiler:

The averages for CPU, reads, writes and duration were very close for all options. There is no one "best solution."

If you end up with a very long trigger, you should split it up it is easier to flush a smaller unit from memory as needed (or recompile it again when necessary). Otherwise, if you are not sure which solution is best in a specific case, just test it as I have demonstrated.

Explore Microsoft SQL Server triggers

• Home Introduction
• Part 1 Why use triggers in SQL Server?
• Part 2 Designing and implementing triggers in SQL Server
• Part 3 Example: INSTEAD OF triggers vs. AFTER triggers
• Part 4 Example: One trigger vs. two for the same logic
• Part 5 Example: Nested triggers in SQL Server
• Part 6 Example: Heavy insertion process from OLTP and trigger
• Part 7 Example: Cursors in SQL Server triggers

ABOUT THE AUTHOR:

Michelle Gutzait works as a team member of the SQL Server Database Experts at www.pythian.com, a worldwide company providing Remote Administration services for databases, operating systems, SANs and networks. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the past 10 years, she has worked exclusively with SQL Server. Her skills include SQL infrastructure and database design, performance tuning, security, high availability, disaster recovery, very large databases, replication, T-SQL coding, DTS/SSIS packages, administrative and infrastructure tools development, reporting services and more.