Pros & Cons of Using
Stored Procedures and Trigger in SQL Server
Last time, I wrote an article and focussed on Using SELECT, Views and Stored Procedures in SQL Server. I was quite glad to differentiate the three and its Pros & Cons. I was not even given much importance of Trigger not because of NO TIME. To be honest, I was just a beginner in use of Triggers. In this article I decided to write about Triggers and its difference with Stored Procedure. We had a team meeting to incorporate the audit information in our organization.
After a few months, we had a debate to incorporate the audit information either in Stored Procedure or Trigger. In the meeting, the set of team member has suggested having audit statements in Triggers and another set of team member has recommended having audit statements in Stored Procedures. I was totally confused to take a decision. Then, I told my team members to stop the debate. I requested my team members to involve in R&D for few days before come to the conclusion and judge the Pros & Cons of the two. This is how the article has been born to have audit information in our project. I devoted my influence to write an article on Stored ProcedureVsTrigger and to differentiate the two and its Pros & Cons.
So, how do I start! I decided to use some information from my previous article http://www.sql-server-performance.com/gv_sp_views_selects.asp. I have introduced the characters that would be involving in the article before the initiative. These characters would play important role in the article.
- Syscacheobjects
- DBCC FREEPROCCACHE
- Stored Procedure
- Trigger
- Select
- Create
- Truncate
- Insert
- Exec and,
- Drop
How do I incorporate my audit work for tables in my database? Should I use Stored Procedure or Trigger? This is "Sub-Title" for the article. I have divided this article into three parts to better explain this information. I created two virtual teams with a set of three members. Team A will work on Trigger and Team B will work on Stored Procedure. I have provided scripts and examples to both the teams.
I will not explain the definition for Stored Procedure and Trigger. I would recommend you to read the MS-SQL Server BOL definition for Stored Procedure and Trigger.
Introduction
To learn the differences between the Stored procedure and Trigger, I need to mention the syscacheobjects system table. It is used to store information about compiled objects and their execution plans. The reason for this is because compiled source are stored here, and I have used this table to experiment the article. I have used one more DBCC FREEPROCCACHE consultant in the article. This command clears the syscacheobjects table of any current cached objects.
I presume that you have rudiment knowledge in database and use of DDL and DML commands. If you do not have much knowledge on DDL and DML commands, this article will help you to learn both the types of commands.
Now, let’s create a table and input a few rows in the table before we commence at taking a look at the differences between the Stored Procedure and Trigger.
Create Sample Table
I assume you have a database you can use for this. If not, you will want to create one at this time. Now, we need to create a table for our experimentation.
Create Table DummyTable1 (EmpId Int, EmpName Varchar(8000) ) Create Table DummyTable1_Audit (EmpId Int, EmpName Varchar(8000), AuditFlag Char (1) )
Now, let’s add a few records in this table using this script:
Insert Into DummyTable1 Values (1, Replicate ('a',20))
Insert Into DummyTable1_Audit Values (1, Replicate ('a',20), ‘N’)
GO
Insert Into DummyTable1 Values (2, Replicate ('b',20))
Insert Into DummyTable1_Audit Values (2, Replicate ('b',20), ‘N’)
GO
Insert Into DummyTable1 Values (3, Replicate ('c',20))
Insert Into DummyTable1_Audit Values (3, Replicate ('c',20), ‘N’)
GO
Insert Into DummyTable1 Values (4, Replicate ('d',20))
Insert Into DummyTable1_Audit Values (4, Replicate ('d',20), ‘N’)
GO
Insert Into DummyTable1 Values (5, Replicate ('e',20))
Insert Into DummyTable1_Audit Values (5, Replicate ('e',20), ‘N’)
GO
Insert Into DummyTable1 Values (6, Replicate ('f',20))
Insert Into DummyTable1_Audit Values (6, Replicate ('f',20), ‘N’)
GO
Insert Into DummyTable1 Values (7, Replicate ('g',20))
Insert Into DummyTable1_Audit Values (7, Replicate ('g',20), ‘N’)
GO
Insert Into DummyTable1 Values (8, Replicate ('h',20))
Insert Into DummyTable1_Audit Values (8, Replicate ('h',20), ‘N’)
GO
Insert Into DummyTable1 Values (9, Replicate ('i',20))
Insert Into DummyTable1_Audit Values (9, Replicate ('i',20), ‘N’)
GO
Insert Into DummyTable1 Values (10, Replicate ('j',20))
Insert Into DummyTable1_Audit Values (10, Replicate ('j',20), ‘N’)
GO
DummyTable1 and DummyTable1_Audit has contains sufficient rows.
SELECT Statement
Now, let’s view the contents of the table by executing the following command in Query Analyzer for our new table.
SELECT EmpId, EmpName FROM DummyTable1 GO
EmpID | EmpName |
1 | aaaaaaaaaaaaaaaaaaaa |
2 | bbbbbbbbbbbbbbbbbbbb |
3 | Cccccccccccccccccccc |
4 | dddddddddddddddddddd |
5 | eeeeeeeeeeeeeeeeeeee |
6 | Ffffffffffffffffffff |
7 | gggggggggggggggggggg |
8 | hhhhhhhhhhhhhhhhhhhh |
9 | Iiiiiiiiiiiiiiiiiiii |
10 | Jjjjjjjjjjjjjjjjjjjj |
SELECT EmpId, EmpName, AuditFlag FROM DummyTable1_Audit GO
EmpID | EmpName | AuditFlag |
1 | aaaaaaaaaaaaaaaaaaaa | N |
2 | bbbbbbbbbbbbbbbbbbbb | N |
3 | cccccccccccccccccccc | N |
4 | dddddddddddddddddddd | N |
5 | eeeeeeeeeeeeeeeeeeee | N |
6 | Ffffffffffffffffffff | N |
7 | gggggggggggggggggggg | N |
8 | hhhhhhhhhhhhhhhhhhhh | N |
9 | Iiiiiiiiiiiiiiiiiiii | N |
10 | Jjjjjjjjjjjjjjjjjjjj | N |
As you would expect, the data we inserted earlier has been displayed.
Truncate Statement
Now, let’s execute the following commands to clear the table. I have used “Truncate” statement instead of “Delete” because I do not want to rollback my data.
Truncate Table DummyTable1 Truncate Table DummyTable1_Audit GO
Trigger using Insert Statement – Part I
Let us begin with the Trigger statement. I have created an Insert Trigger for audit work. I have divided the DML statement task in two objects. One using Trigger and another using direct INSERT statement using query analyser. Part I will focus on insert statement for DummyTable1 table using query analyzer and the audit work for DummyTable1_Audit table using trigger. Part I will not differentiate from stored procedure. Let’s start create the trigger.
CREATE TRIGGER TR_DummyTable1 ON DummyTable1 FOR INSERT AS INSERT INTO DummyTable1_Audit SELECT *, 'N' FROM inserted
The Trigger has been created successfully. The trigger will execute automatically and insert a row in the DummyTable1_Audit table for every insert in the DummyTable1 table.
Now, let’s execute the following commands to clear the cache.
DBCC FREEPROCCACHE GO
Freeing the procedure cache prevents an ad-hoc SQL statement from being reused, assuming that it is currently in the cache. This means that the next time we run the same ad-hoc statement, that it must be newly recompiled.
I have selected three human beings from Team A. I have asked them to execute the insert statement for DummyTable1 using query analyzer. They have executed the insert statement in their own way.
Now, member I of team-A has execute the insert statement and following commands to display cache information for the trigger we created and that is now stored in SQL Server’s syscacheobjects system table.
Insert Into DummyTable1
Values (1, Replicate ('a',20))
GO
SELECT cacheobjtype, refcounts, usecounts, sql
FROM master.dbo.syscacheobjects
GO
The result will display many columns, but we are only interested in four of them, as shown below.
Cacheobjtype | Refcounts | Usecounts | Sql |
Executable Plan | 1 | 1 | TR_DummyTable1 |
Compiled Plan | 2 | 1 | TR_DummyTable1 |
Here’s what the information displayed means:
Cacheobjtype: The type of object stored in the cache, which can include:
- Compiled Plan
- Executable Plan
- Parse Tree
- Cursor Parse Tree
- Extended Stored Procedure
We will be concentrating mainly on the Compiled Plan and the Executable Plan cacheobjtype type objects.
Refcounts: Number of other cache objects referencing this cache object. A count of 1 is the base.
Usecounts: Number of times this cache object has been used since inception.
Sql: Text of the statement.
Now, let’s execute the same INSERT statement with a different value and verify the result.
Insert Into DummyTable1 Values (2, Replicate ('b',20))
GO
SELECT cacheobjtype, refcounts, usecounts, sql
FROM master.dbo.syscacheobjects
GO
Cacheobjtype | Refcounts | Usecounts | Sql |
Executable Plan | 1 | 2 | TR_DummyTable1 |
Compiled Plan | 2 | 1 | TR_DummyTable1 |
The value of Usecounts has been incremented. SQL Server has used the same compiled plan for the TR_DummyTable1 sql statement and incremented the Usecounts of the executable plan. N number user will use the same compiled plan when we execute the Trigger.
Member II of team-A has executed the insert statement with the username on the INSERT statement and following commands to display cache information.
Insert Into dbo.DummyTable1 Values (3, Replicate ('c',20))
GO
SELECT cacheobjtype, refcounts, usecounts, sql
FROM master.dbo.syscacheobjects
GO
Cacheobjtype | Refcounts | Usecounts | Sql |
Executable Plan | 1 | 3 | TR_DummyTable1 |
Compiled Plan | 2 | 1 | TR_DummyTable1 |
No difference at all. SQL Server has used the same compiled plan for the stored procedure and incremented the Usecounts of the executable plan.
Member III of team-A has executed the insert statement with the database and username on the INSERT statement and following commands to display cache information.
Insert Into vijay.dbo.DummyTable1 Values (4, Replicate ('d',20))
GO
SELECT cacheobjtype, refcounts, usecounts, sql
FROM master.dbo.syscacheobjects
GO
Cacheobjtype | Refcounts | Usecounts | Sql |
Executable Plan | 1 | 4 | TR_DummyTable1 |
Compiled Plan | 2 | 1 | TR_DummyTable1 |
No difference at all. SQL Server has used the same compiled plan for the trigger and incremented the Usecounts of the executable plan. Part I has explained one way to do audit work with two entities having one as “Trigger” and another as “Insert” statement using query analyzer. The INSERT statement could be written from different ways. It could be from a MS-SQL SERVER Job, MS-SQL SERVER DTS using VB Script, Application and using Query Analyzer. Different set of entities has incorporated the INSERT statement in different ways. How do we club these entities into one object? That’s how Part II has cropped. Let us move on to Part II.
Trigger using Stored Procedure – Part II
I have experiment the last one using INSERT statement for DummyTable1 table in Query analyzer. I decided to incorporate the INSERT statement [DummyTable1 table] in stored procedure. Different set of entities will use INSERT statement in different ways. I clubbed those entities in a stored procedure to upgrade Network circulation.
I have created a stored procedure to encompass the INSERT statement for DummyTable1 table.
Create PROC spAddDummyTable1 (@EmpID Int, @EmpName Varchar(8000)) AS Begin Insert Into DummyTable1 Values (@EmpID, Replicate (@EmpName,20)) IF @@Error > 0 Begin Return 1 End End Return 0
Truncate Statement
Now, let’s execute the following commands to clear the table. I have used “Truncate” statement instead of “Delete” because I do not want to rollback my data.
Truncate Table DummyTable1 Truncate Table DummyTable1_Audit GO
Now, let’s execute the following commands to clear the cache before the Trigger using stored procedure experiment.
DBCC FREEPROCCACHE GO
I have selected three human beings from the same team. I have asked them to execute the spAddDummyTable1 using query analyzer. They have executed the spAddDummyTable1 stored procedure in their own way. Now, member I of team-A has execute the spAddDummyTable1 and following commands to display cache information for the table.
Exec spAddDummyTable1 1, 'a' GO SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.syscacheobjects GO
The result will display many columns, but we are only interested in four of them, as shown below.
Cacheobjtype | Refcounts | Usecounts | Sql |
Executable Plan | 1 | 1 | TR_DummyTable1 |
Compiled Plan | 2 | 1 | TR_DummyTable1 |
Executable Plan | 1 | 1 | spAddDummyTable1 |
Compiled Plan | 2 | 1 | spAddDummyTable1 |
Now, Let’s execute the same spAddDummyTable1 stored procedure with a different value and verify the result.
Exec spAddDummyTable1 2, 'b' GO SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.syscacheobjects GO
Cacheobjtype | Refcounts | Usecounts | Sql |
Executable Plan | 1 | 2 | TR_DummyTable1 |
Compiled Plan | 2 | 1 | TR_DummyTable1 |
Executable Plan | 1 | 2 | spAddDummyTable1 |
Compiled Plan | 2 | 1 | spAddDummyTable1 |
The value of Usecounts has been incremented. SQL Server has used the same compiled plan for the TR_DummyTable1 and spAddDummyTable1 sql statement and incremented the Usecounts of the executable plan. N number user will use the same compiled plan when we execute the statement.
Member II of team-A has executed the spAddDummyTable1 stored procedure with the username on the stored procedure and following commands to display cache information.
Exec dbo.spAddDummyTable1 3, 'c'
GO
SELECT cacheobjtype, refcounts, usecounts, sql
FROM master.dbo.syscacheobjects
GO
Cacheobjtype | Refcounts | Usecounts | Sql |
Executable Plan | 1 | 3 | TR_DummyTable1 |
Compiled Plan | 2 | 1 | TR_DummyTable1 |
Executable Plan | 1 | 3 | spAddDummyTable1 |
Compiled Plan | 2 | 1 | spAddDummyTable1 |
No difference at all. SQL Server has used the same compiled plan for the stored procedure, trigger and incremented the Usecounts of the executable plan.
Member III of team-A has executed the spAddDummyTable1 stored procedure with the database and username on the stored procedure and following commands to display cache information.
Exec vijay.dbo.spAddDummyTable1 4, 'd'
GO
SELECT cacheobjtype, refcounts, usecounts, sql
FROM master.dbo.syscacheobjects
GO
Cacheobjtype | Refcounts | Usecounts | Sql |
Executable Plan | 1 | 4 | TR_DummyTable1 |
Compiled Plan | 2 | 1 | TR_DummyTable1 |
Executable Plan | 1 | 4 | spAddDummyTable1 |
Compiled Plan | 2 | 1 | spAddDummyTable1 |
No difference at all. SQL Server has used the same compiled plan for the trigger and incremented the Usecounts of the executable plan. We have finished the experiment for the last two parts. Let us move on to Stored Procedures – Part III.
Stored Procedures – Part III
Part I has executed different INSERT statement using Query analyzer and for every insert statement, trigger has been executed automatically for audit work. Part II has executed stored procedure using Query analyzer and for every execution, trigger has been executed automatically for audit work. I realized that why do we need two objects separately. I decided to club two objects into one. I have decided to drop the trigger and move the INSERT statement for both DummyTable1 & DummyTable1_Audit table in a stored procedure.
Now, let’s execute the following commands before creating Stored procedure experiment. Drop the existing stored procedure using the following command.
Drop Proc spAddDummyTable1 GO
Drop the existing trigger using the following command.
Drop Trigger TR_DummyTable1 GO
Truncate Statement
Now, let’s execute the following commands to clear the table. I have used “Truncate” statement instead of “Delete” because I do not want to rollback my data.
Truncate Table DummyTable1 Truncate Table DummyTable1_Audit GO
Now, let’s execute the following commands to clear the cache before the Trigger using stored procedure experiment.
DBCC FREEPROCCACHE
GO
We will create a stored procedure with two parameters, and see how it is differs from the Part I and Part II.
Create PROC spAddDummyTable1 (@EmpID Int, @EmpName Varchar(8000)) AS Begin Insert Into DummyTable1 Values (@EmpID, Replicate (@EmpName,20)) IF @@Error > 0 Begin Return 1 End Insert Into DummyTable1_Audit (EmpID, EmpName, AuditFlag) Select EmpID, EmpName, 'N' From DummyTable1 Where EmpID = @EmpID IF @@Error > 0 Begin Return 1 End End Return 0
I have selected three human beings from the Team B. I have asked them to execute the spAddDummyTable1 using query
analyzer. They have executed the spAddDummyTable1-stored procedure in their own way.
Now, member I of team-B has execute the spAddDummyTable1 and following commands to display cache information for the spDummyTable1 we created.
EXEC spAddDummyTable1 1, 'a'
GO
SELECT cacheobjtype, refcounts, usecounts, sql
FROM master.dbo.Syscacheobjects
GO
Cacheobjtype | Refcounts | Usecounts | Sql |
Executable Plan | 1 | 1 | spAddDummyTable1 |
Compiled Plan | 2 | 1 | spAddDummyTable1 |
SQL Server displays the compiled and executable plan for the spAddDummyTable1 stored procedure. Let’s execute the same stored procedure with a different parameter value and view the cache details.
EXEC spAddDummyTable1 2, 'b' GO SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects GO
Cacheobjtype | Refcounts | Usecounts | Sql |
Executable Plan | 1 | 2 | spAddDummyTable1 |
Compiled Plan | 2 | 1 | spAddDummyTable1 |
The value of Usecounts has been incremented. Though, we have given different parameter value, SQL Server has used the same compiled plan for the stored procedure and incremented the Usecounts of the executable plan. N number user will use the same compiled plan when we execute the stored procedure.
Now, let us execute the stored procedure with the username and see the cache details. Member II of team-B has executed the spAddDummyTable1 stored procedure with the username with different value on the stored procedure and following commands to display cache information.
Exec dbo.spAddDummyTable1 3, 'c'
GO
SELECT cacheobjtype, refcounts, usecounts, sql
FROM master.dbo.syscacheobjects
GO
Cacheobjtype | Refcounts | Usecounts | Sql |
Executable Plan | 1 | 3 | spAddDummyTable1 |
Compiled Plan | 2 | 1 | spAddDummyTable1 |
No difference at all. SQL Server has used the same compiled plan for the stored procedure and incremented the Usecounts of the executable plan. Now, member III of Team B has executed the spAddDummyTable1 stored procedure with the database and username with different values on the stored procedure and following commands to display cache information.
Exec vijay.dbo.spAddDummyTable1 4, 'd' GO SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.syscacheobjects GO
Cacheobjtype | Refcounts | Usecounts | Sql |
Executable Plan | 1 | 4 | spAddDummyTable1 |
Compiled Plan | 2 | 1 | spAddDummyTable1 |
No difference at all. SQL Server has used the same compiled plan for the stored procedure and incremented the Usecounts of the executable plan.
Summary
All three parts has shown no difference in the article. Part I has executed different INSERT statement using Query Analyzer and for every insert statement, trigger has been executed automatically for audit work. Part II has executed stored procedure using Query analyzer and for every execution, trigger has been executed automatically for audit work. I realized that why we need two objects separately. I decided to club two objects into one. I have decided to drop the trigger and move the INSERT statement for both DummyTable1 & DummyTable1_Audit table in a stored procedure. Part III has executed stored procedure using Query analyzer that will INSERT a row in DummyTable1 & DummyTable1_Audit table.
Part I is not good for programmer practice to have an INSERT statement in different places. We will avoid using Part I method. The Part II and Part III method has difference. I would straight way recommend and conclude Part III is better than Part II method. You mind would start asking how? And why? Let me derive in short note.
Part II and Part III both are applicable for Maintainability, Flexibility and Manual Intervention. I have created DummyTable1 table. I should have audit work for every DML action for DummyTable1 table. I have created three trigger and stored procedure for Part II method. Insert, Update and Delete trigger and spAddDummyTable1, spUpdateDummyTable1 and spDeleteDummyTable1 stored procedure. Insert trigger will execute automatically and will insert a post row in DummyTable1_Audit table for every INSERT action DummyTable1 table via spAddDummyTable1stored procedure. Update trigger will execute automatically and will insert a pre and post row in DummyTable1_Audit table for every UPDATE action DummyTable1 table via spUpdateDummyTable1 stored procedure. Delete trigger will execute automatically and will insert a pre row in DummyTable1_Audit table for every DELETE action DummyTable1 table via spDeleteDummyTable1 stored procedure. We have created three trigger and stored procedure to do set of DML action. It is expensive. Yes, I agree with your point. We have created totally six objects in our database. As you assume, I have a database that has hold 150 tables. I would be creating three sets of trigger and stored procedure object [150 * (3+3) = 900 objects] for tables in my database and we need manual intervention to execute the stored procedure to execute the trigger automatically.
Part III could have three stored procedures and contains DML statements. spAddDummyTable1, spUpdateDummyTable1 and spDeleteDummyTable1 stored procedure. spAddDummyTable1 contain the INSERT statement for DummyTable1 and post INSERT statement for DummyTable1_Audit table. spUpdateDummyTable1 contain the pre and post INSERT statement for DummyTable1_Audit table and UPDATE statement for DummyTable1 table. spDeleteDummyTable1 contain the pre INSERT statement for DummyTable1_Audit table and DELETE statement for DummyTable1 table. Part III has got only three objects. I have a database that has hold 150 tables. I would be creating three sets of stored procedure object [150 * 3 = 450 objects] for tables in my database. Part III would have only manual intervention to execute the stored procedures. No automatic execution.
As you assume, we would be incorporating the same stored procedure [i.e. spUpdateDummyTable1] in more than one stored object [stored procedure]. As you assume, we have changed the signature of the stored procedure [i.e. spUpdateDummyTable1]. Maintenance work has to happen for the stored procedure in reference place and no difference in Part II and Part II. It has an impact on Maintenance work and Part II and Part III both are applicable for maintenance work. But, Flexibility is better in Part III comparatively to Part II because of less stored objects available in database. Part II and Part III both need Manual Intervention to executed the stored procedure.
Note: You should try the UPDATE and DELETE trigger and stored procedure yourself.
About Author
G. Vijayakumar has worked in client server and web application. He is currently working for Transworld, which is located in Bangalore, India, working on an e-banking product.
