Overview of SQL Server Database Triggers

By: Md. Marufuzzaman | Views: 1311 | Date: 10-Aug-2011

A database trigger is a stored procedure that is invoked automatically when a predefined event occurs.

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.

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.

Previous Page Next Page

People Searching On This Page:
  • database trigger insert data
  • Md. Marufuzzaman
  • trigger overview sql server
  • triggers databases

Related Pages


SSRS Interview Questions - SQL Server Reporting Services

SSRS Interview Questions - SQL Server Reporting Services

Articles | Programming | MS SQL
Date:
20-Aug-2011  Views: 1791

Here we have provided some interview question regarding SQL Server Reporting Services (SSRS) Technology. All the Best. ...
Overview of DTS Package

Overview of DTS Package

Articles | Programming | MS SQL
Date:
19-Jul-2011  Views: 1180

By using DTS tools to graphically build DTS packages or by programming a package with the DTS object model, you can create custom data movement soluti ...
DTS vs SSIS - Data Transformation Services vs SQL Server Integration Services

DTS vs SSIS - Data Transformation Services vs SQL Server Integration Services

Articles | Programming | MS SQL
Date:
19-Jul-2011  Views: 2167

Here we have provided some differences between DTS and SSIS. It may be useful to answer interview question related to SSIS (MSBI) ...
What Is Passivation of Stainless Steel –An Overview

What Is Passivation of Stainless Steel –An Overview

Articles | Business
Date:
13-Jul-2011  Views: 1481

The passivation of stainless steel is done to enhance its corrosion resistance properties. The passivation treatment helps to perk up the surface stat ...
Tricks to Identify Which Need Optimization In SQL Server

Tricks to Identify Which Need Optimization In SQL Server

Articles | Programming | MS SQL
Date:
17-Jan-2011  Views: 1738

some helpful stuff for identifying which need optimization in sql server ...
Post Your Comments (No Login Require)
Name : (required)
Email : (required)
Website :

Comment : (required)

98  + 1 =     
Comments
People Searched About:
Database Trigger Insert Data   |   Md. Marufuzzaman   |   Trigger Overview Sql Server   |   Triggers Databases   |  
Google : 265 times | Yahoo : 52 times | Bing : 258 times |