Tech notes: best practice for trigger

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

A stored procedure, A, updates TableA.
This fires a trigger from TableA.
The defined trigger on TableA updates TableB.
TableB has a trigger which fires.
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

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.

Tech notes

Tuesday, June 19, 2012

best practice for trigger

Table of Contents