SQL - Trigger

SQL Server Triggers: Understanding and Alternatives

SQL Server provides us with two main types of triggers: the DML Triggers and the DDL triggers. 

1.Data Definition Language (DDL) events:

 CREATE, ALTER, DROP, GRANT, DENY, and REVOKE T-SQL statements. 

2.Data Manipulation Language (DML) events:

INSERT, UPDATE or DELETE action

The DML triggers can be used to maintain data integrity and enforce company business rules, same as table check and foreign keys constraints functionality, by performing auditing processes and other post DML actions. 

You can use the DML triggers to query other tables and perform complex T-SQL queries.

2.a.There are two types of DML triggers: AFTER or FOR trigger and INSTEAD OF trigger. 

 The AFTER trigger: fired and executed after performing the INSERT, UPDATE or DELETE action that fires it successfully. 

Also, any referential cascade actions and constraint checks should succeed before firing the trigger. 

The AFTER trigger can be defined on the table level only without the possibility define it on views. 

2.b.The INSTEAD OF trigger is used to override the statement of the action that fires the trigger with the statement provided in the trigger, rolling back that statement after raising an error when someone is trying to perform an action that breaks a specific policy, such as updating a critical financial column or writing the change into an audit table before performing the change. 

3.The triggers differ from the stored procedures in that the triggers are fired automatically when a predefined data modification occurs. 

4.If the trigger is fired, a special type of virtual tables called Inserted and Deleted tables will be used to keep the data values before and after the modification.

a.The trigger statement will work under the scope of the same transaction that fires that trigger. 
b.This means that the transaction will not be committed completely until the trigger statement is completed successfully.
c.On the other hand, the transaction will be rolled back if the trigger statement fails.

5.Trigger Alternative - Enforce Integrity

For example, rather than using the triggers to enforce the entity integrity, it should be enforced at the lowest level by using the PRIMARY KEY and UNIQUE constraints. 

The same is applied to the domain integrity that should be enforced through CHECK constraints, and the referential integrity that should be enforced through the FOREIGN KEY constraints.

You can use the DML triggers only if the features supported by a specific constraint cannot meet your application requirements.

6.Example

CREATE TRIGGER TRGR_EmployeeSalary ON EmployeeSalaryTrigger

AFTER INSERT

AS

DECLARE @EmpSal AS INT

SET @EmpSal = (SELECT TOP 1 inserted.Emp_Salary FROM inserted)

IF @EmpSal<0

BEGIN

RAISERROR  ('Cannot insert negative salary',16,10);

  ROLLBACK

END

CHECK constraint:

CREATE TABLE EmployeeSalaryConstraint

(

   ID INT IDENTITY (1,1) PRIMARY KEY,

   Emp_First_name VARCHAR (50),

   Emp_Last_name VARCHAR (50),

   Emp_Salary INT CONSTRAINT EmpSal CHECK (Emp_Salary >=0)

  )

GO

the trigger method weight is three times the CHECK constraint method weight, as shown in the execution plan 

This is due to the fact that the trigger will extend the transaction life and will rollback the query that fires the trigger after executing it when an integrity violation is found, causing a performance degradation due to the rollback process. The case is different when using the CHECK constraint, where the constraint will do its job before doing any modification in the data, requiring no rollback in the case of violation.

Also, to compare the execution time consumed by each one, let us run each one 1000 times using the T-SQL statements below:

INSERT INTO EmployeeSalaryTrigger VALUES('Ali', 'Fadi',-4)

GO 10000  

INSERT INTO EmployeeSalaryConstraint VALUES ('Ali', 'Fadi',-4)

GO 10000

7.Trigger Alternatives ... Auditing

Create a Simple SQL Server Trigger to Build an Audit Trail

Overview of SQL Server database Triggers