Click here to Skip to main content
Click here to Skip to main content
Go to top

Overview of SQL Server database Triggers

, 7 Aug 2009
Rate this:
Please Sign up or sign in to vote.
Overview of SQL Server database Triggers

Table of Contents

Overview

Implementing business rules as well as performing validation or data modifications, triggers are the best use for this purpose when other methods are not sufficient. Triggers are normally used in two areas: creating audit records and reflecting changes to crucial business tables, and validating changes against a set of business rules coded in T-SQL.

In this article, I would like to demonstrate how to create triggers, use of triggers, different types of triggers and performance considerations.

What is a Trigger?

A database trigger is a stored procedure that is invoked automatically when a predefined event occurs. Database triggers enable DBAs (Data Base Administrators) to create additional relationships between separate databases. In other ways, a trigger can be defined to execute before or after an INSERT, UPDATE, or DELETE operation, either once per modified row, or once per SQL statement. If a trigger event occurs, the trigger's function is called at the appropriate time to handle the event.

Triggers can be assigned to tables or views. However, although there are two types of triggers, INSTEAD OF and AFTER, only one type of trigger can be assigned to views. An INSTEAD OF trigger is the one that is usually associated with a view, and runs on an UPDATE action placed on that view. An AFTER trigger fires after a modification action has occurred.
From a performance viewpoint, the fewer the triggers, the better, as we will invoke less processes. Therefore, do not think that having one trigger per action to make things modular will not incur performance degradation. A trigger's main overhead is referencing either two specialist tables that exist in triggers – deleted and inserted or other tables for business rules. Modularizing triggers to make the whole process easier to understand will incur multiple references to these tables, and hence a greater overhead.

Note: It is not possible to create a trigger to fire when a data modification occurs in two or more tables. A trigger can be associated only with a single table.

Why Use Triggers?

To improve data integrity, trigger can be used. When an action is performed on data, it is possible to check if the manipulation of the data concurs with the underlying business rules, and thus avoids erroneous entries in a table. For example:

  • We might want to ship a free item to a client with the order, if it totals more than $1000. A trigger will be built to check the order total upon completion of the order, to see if an extra order line needs to be inserted.
  • In a banking scenario, when a request is made to withdraw cash from a cash point, the stored procedure will create a record on the client's statement table for the withdrawal, and the trigger will automatically reduce the balance as required. The trigger may also be the point at which a check is made on the client's balance to verify that there is enough balance to allow the withdrawal. By having a trigger on the statement table, we are secure in the knowledge that any statement entry made, whether withdrawal or deposit, will be validated and processed in one central place.

Note: We discuss only data integrity here, and not referential integrity.

Another use of triggers can be to carry out an action when a specific criterion has been met. One example of this is a case where an e-mail requesting more items to be delivered is sent, or an order for processing could be placed, when stock levels reach a preset level. However, if we insert data into another table from within a trigger, we have to be careful that the table we insert into doesn't have a trigger that will cause this first trigger to fire. It is possible to code triggers that result in an endless loop, as we can define a trigger on TableA, which inserts into TableB, and a trigger for TableB, which updates TableA. This scenario will ultimately end in an error being generated by the SQL Server. The following diagram will demonstrate this:

Diagram

Figure 1
  1. A stored procedure, A, updates TableA.
  2. This fires a trigger from TableA.
  3. The defined trigger on TableA updates TableB.
  4. TableB has a trigger which fires.
  5. This trigger from TableB updates TableA.

Creating and Using a Trigger  

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. More details can be found at this link.

CREATE TRIGGER Statement

The CREATE TRIGGER statement defines a trigger in the database.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

  • ALTER privilege on the table on which the BEFORE or AFTER trigger is defined
  • CONTROL privilege on the view on which the INSTEAD OF TRIGGER is defined
  • Definer of the view on which the INSTEAD OF trigger is defined
  • ALTERIN privilege on the schema of the table or view on which the trigger is defined
  • SYSADM or DBADM authority and one of:
    • IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the trigger does not exist
    • CREATEIN privilege on the schema, if the schema name of the trigger refers to an existing schema

If a trigger definer can only create the trigger because the definer has SYSADM authority, the definer is granted explicit DBADM authority for the purpose of creating the trigger.

Syntax of a Trigger

CREATE TRIGGER name ON table
   [WITH ENCRYPTION]
   [FOR/AFTER/INSTEAD OF]
   [INSERT, UPDATE, DELETE]
   [NOT FOR REPLICATION]
AS
BEGIN
--SQL statements
...
END     

Sample Example  

Let’s take an example. We have a table with some columns. Our goal is to create a TRIGGER which will be fired on every modification of data in each column and track the number of modifications of that column. The sample example is given below:

-- ==========================================================
-- Author: Md. Marufuzzaman
-- Create date: 
-- Description: Alter count for any modification  
-- ===========================================================
CREATE TRIGGER [TRIGGER_ALTER_COUNT] ON [dbo].[tblTriggerExample] 
FOR INSERT, UPDATE
AS
BEGIN
DECLARE @TransID  VARCHAR(36)
 SELECT @TransID = TransactionID FROM INSERTED
 UPDATE [dbo].[tblTriggerExample] SET AlterCount = AlterCount + 1 
          ,LastUpdate = GETDATE()
    WHERE TransactionID = @TransID
END

SQL Table

Figure 2 (SQL table)

Types of Triggers

There are three main types of triggers that fire on INSERT, DELETE, or UPDATE actions. Like stored procedures, these can also be encrypted for extra security. More details can be found at this link.

Good Practice

The most important point is to keep the trigger as short as possible to execute quickly, just like stored procedures. The longer a trigger takes to fire, the longer the locks will be held on the underlying tables. To this end, we could place cursors within a trigger, but good practice dictates that we don't. Cursors are not the fastest of objects within a database, and we should try and revisit the problem with a different solution, if we feel the need for cursors. One way around the problem may be to run two, or perhaps, three updates, or even use a temporary table instead.

Use triggers to enforce business rules, or to complete actions that have either a positive effect on the organization, or if an action will stop problems with the system. An example of this is creation of a trigger that will e-mail a client when an order is about to be shipped, giving details of the order, and so on.

Note: Use an @@ROWCOUNT, where required, to check the number of rows that have been affected.

Conclusion

I hope that this article might be helpful to you. Enjoy!

History

  • 7th August 2009: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Md. Marufuzzaman
CEO
Bangladesh Bangladesh
A highly experienced leader with successful track record of software development, product innovations, brand management and corporate communication etc. Some successful product innovations have also achieved and awards “Most Valuable Professional” (MVP) at 2010 and 2011 by codeproject.com and also selected as a mentor of codeproject.com. Published over 100 technical articles in various software development resource sites (i.e., codeprojetc.com, Microsoft MSDN, and IEEE & IBM (In progress)) and various IT Forums, Blogs etc.
 
Over ten years of professional experiences in ICT field having extensive experience in formulating corporate vision and long term strategy. Leading development related functions including design, development, services, data management and analytics, customer experience management, content services, digital analytics and optimization.
 
An individual with results-driven approach and relentless in pursuit of excellence from a business and organizational standpoint. Believes in transparency, commitment and teamwork.
 
Expertise: Software/Solution Architect, SaaS platform base application, Large scale win32/web based business software solutions, enterprise applications, integration, etc.
 
Technologies/Tools: Microsoft.Net version 05/08/10/12, Microsoft SQL Server version 7/2K/05/08/12 , Oracle version 10/11, MySql version 5.1, 5.5, PS2, Visual C#, R, VB.NET, ASP.NET, PHP, API, MVC, WebAPI , Add-In Visual Basic etc.,. I have also more than two years’ of strong experience in mobile-VAS (platform development).
 
Points of Interest: Technology and research & development especially focused on business functionalities and social business areas as well, few stuff including:
 
1.R&D on new techniques as required to increase business revenue.
2.Urban and rural sectors to improve people’s lifestyle, better medical facilities, education, social business etc., using innovative technical solutions.
3.Research and innovative product development.
4.Etc.
Follow on   Twitter   LinkedIn

Comments and Discussions

 
QuestionThank you! PinmemberMember 81401674-Jul-13 22:25 
GeneralMy vote of 5 Pinmemberseanmir23-Dec-12 23:04 
GeneralRe: My vote of 5 PinmentorMd. Marufuzzaman23-Dec-12 23:11 
GeneralMy vote of 5 Pinmembercsharpbd16-Nov-12 8:26 
GeneralRe: My vote of 5 PinmentorMd. Marufuzzaman16-Nov-12 14:49 
QuestionTriggers with respect to clients Pinmembernavanit_kamble27-Jun-12 4:14 
AnswerRe: Triggers with respect to clients PinmentorMd. Marufuzzaman27-Jun-12 5:35 
GeneralMy vote of 5 Pinmember9 from ap9-Mar-12 22:39 
GeneralRe: My vote of 5 PinmentorMd. Marufuzzaman27-Jun-12 5:36 
QuestionUpdate trigger with two database PinmemberSivaooty25-Mar-10 19:03 
AnswerRe: Update trigger with two database PinmvpMd. Marufuzzaman25-Mar-10 20:12 
GeneralGood Article ! Pinmembersognant1-Sep-09 21:44 
GeneralRe: Good Article ! PingroupMd. Marufuzzaman2-Sep-09 0:16 
Thanks for your comments and suggestion...Smile | :)
 
I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.


Don't forget to click [Vote] / [Good Answer] on the post(s) that helped you.
 
Thanks
Md. Marufuzzaman

GeneralNice.. Few Comments. PinmvpAbhijit Jana7-Aug-09 7:46 
GeneralRe: Nice.. Few Comments. [modified] PingroupMd. Marufuzzaman7-Aug-09 8:20 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 7 Aug 2009
Article Copyright 2009 by Md. Marufuzzaman
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid