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 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
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 triggers
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.|