Click here to Skip to main content
15,867,488 members
Articles / Database Development / SQL Server / SQL Server 2008

Overview of SQL Server database Triggers

Rate me:
Please Sign up or sign in to vote.
4.71/5 (42 votes)
7 Aug 2009CPOL6 min read 333.3K   1.5K   63   20
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

SQL
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:

SQL
-- ==========================================================
-- 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)



Comments and Discussions

 
QuestionTrigger Pin
Member 1305916614-Mar-17 11:29
Member 1305916614-Mar-17 11:29 
AnswerRe: Trigger Pin
Md. Marufuzzaman28-Apr-17 4:05
professionalMd. Marufuzzaman28-Apr-17 4:05 
Questiontrigger Pin
Member 1207345513-Nov-15 12:22
Member 1207345513-Nov-15 12:22 
AnswerRe: trigger Pin
Md. Marufuzzaman14-Nov-15 3:59
professionalMd. Marufuzzaman14-Nov-15 3:59 
QuestionThank you! Pin
Member 81401674-Jul-13 22:25
Member 81401674-Jul-13 22:25 
AnswerRe: Thank you! Pin
Md. Marufuzzaman14-Nov-15 4:03
professionalMd. Marufuzzaman14-Nov-15 4:03 
GeneralMy vote of 5 Pin
seanmir23-Dec-12 23:04
seanmir23-Dec-12 23:04 
GeneralRe: My vote of 5 Pin
Md. Marufuzzaman23-Dec-12 23:11
professionalMd. Marufuzzaman23-Dec-12 23:11 
GeneralMy vote of 5 Pin
csharpbd16-Nov-12 8:26
professionalcsharpbd16-Nov-12 8:26 
GeneralRe: My vote of 5 Pin
Md. Marufuzzaman16-Nov-12 14:49
professionalMd. Marufuzzaman16-Nov-12 14:49 
Thanks
Thanks
Md. Marufuzzaman


I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.

QuestionTriggers with respect to clients Pin
navanit_kamble27-Jun-12 4:14
navanit_kamble27-Jun-12 4:14 
AnswerRe: Triggers with respect to clients Pin
Md. Marufuzzaman27-Jun-12 5:35
professionalMd. Marufuzzaman27-Jun-12 5:35 
GeneralMy vote of 5 Pin
sravani.v9-Mar-12 22:39
sravani.v9-Mar-12 22:39 
GeneralRe: My vote of 5 Pin
Md. Marufuzzaman27-Jun-12 5:36
professionalMd. Marufuzzaman27-Jun-12 5:36 
QuestionUpdate trigger with two database Pin
Sivaooty25-Mar-10 19:03
Sivaooty25-Mar-10 19:03 
AnswerRe: Update trigger with two database Pin
Md. Marufuzzaman25-Mar-10 20:12
professionalMd. Marufuzzaman25-Mar-10 20:12 
GeneralGood Article ! Pin
sognant1-Sep-09 21:44
sognant1-Sep-09 21:44 
GeneralRe: Good Article ! Pin
Md. Marufuzzaman2-Sep-09 0:16
professionalMd. Marufuzzaman2-Sep-09 0:16 
GeneralNice.. Few Comments. Pin
Abhijit Jana7-Aug-09 7:46
professionalAbhijit Jana7-Aug-09 7:46 
GeneralRe: Nice.. Few Comments. [modified] Pin
Md. Marufuzzaman7-Aug-09 8:20
professionalMd. Marufuzzaman7-Aug-09 8:20 

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

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