DML Triggers in SQL Server

A Trigger in SQL Server is a database object.SQL Server contains many different objects which either store data or provide access to the data.Other common database objects are:

• Table
• View
• Stored Procedures
• Function
• Index
• Constraint

Trigger has a similarity with a stored procedure.Both Stored Procedure and Trigger
contains T-SQL statements which are executed as a unit.But Triggers and Stored Procedures differs in how they are executed.

Stored Procedure needs to be explicitly executed.If Stored Procedure expects input parameters then they also needs to be explicitly passed.Unlike Stored Procedure Trigger is not explicitly called.Instead trigger is executed in response to certain actions in the database.

Some common uses of triggers are:

• To keep the record of the changes in a table.Keeping record of changes called audit
  trail or change tracking is commonly implemented using triggers.
• To verify the data that is being inserted or updated in a table.We can ensure that only
  valid data is inserted.
• Triggers are also commonly used to enforce referential integrity.

There are different types of triggers.Triggers can be classified based on the different types of SQL Statements against which they are fired.

DDL Triggers

DDL statements are those statements which are used to create or modify the database objects.Some of the DDL Statements are:

• Create
• Alter
• Drop

DDL Triggers are those triggers which fire in response to the DDL statements which modifies the structure of the database objects such as create table or drop table.

DML Triggers

DML Statements are the statements which are used to manage the data in the database.DML statements are:

• Insert
• Update
• Delete

DML Triggers are those triggers which fire in response to INSERT,UPDATE and DELETE statements.So DML triggers can be defined for these types of statements.

A DML Trigger is always attached to a table or a View.While creating a Trigger it is attached to a table or a view.Trigger fires when the data is modified or deleted from  the table or the view. DML Triggers can be of two types depending on the moment the Trigger fires.

Triggers can execute either after the triggering action or instead of the triggering action.If the Trigger executes after the SQL statement then they are called After Triggers while a Trigger which executes in place of the triggering action is called Instead of Trigger.
Let’s see how to define different types of DML triggers

AFTER TRIGGER

AFTER TRIGGER can be defined for each type of DML statment:

• AFTER INSERT Trigger  Fires after the data is inserted in the table
• AFTER UPDATE Trigger Fires after the data is updated in the table
• AFTER DELETE Trigger Fires after the data is deleted from the table

After trigger can not be defined for a view.

We can define After trigger as:

CREATE TRIGGER TriggerName
FOR [INSERT / UPDATE / DELETE]
AS
BODY OF THE TRIGGER
GO

We can use either the FOR or AFTER statement to create after trigger.Both FOR or AFTER  keyword can be used to create after trigger

Example of After Insert trigger

We can create a Trigger using the SQL Management Studio.As a Trigger is always associated with a table or a view so we find an option for triggers in a Triggers folder in the Tables node of the database

To create a Trigger we right click and select the “New Trigger” option

Clicking on the New Trigger option will display a template to create a trigger in the query editor window:

To create After Insert trigger we will modify the generated template.We will be creating after trigger which will insert values in the audit table whenever insert statement is fired on the Users table.We will be inserting values in the UserAudit table for every insert action.

CREATE TABLE [dbo].[Users](
[UserName] [varchar](50) NULL,
[Email] [varchar](50) NULL,
[Password] [varchar](50) NULL,
[Id] [int] NULL,
[IsActive] [bit] NULL
) ON [PRIMARY]

We can create the UserAudit table using the following script:

CREATE TABLE [dbo].[UserAudit](
[UserName] [varchar](50) NULL,
[UserAction] [varchar](50) NULL,
[ActionDate] [date] NULL
) ON [PRIMARY]

We create the trigger as:

CREATE TRIGGER Update_UserAudit
ON [dbo].[Users]
AFTER INSERT
AS
BEGIN
--SET NOCOUNT ON;

DECLARE @UserName varchar(50)
DECLARE @Email varchar(50)

SELECT @UserName = UserName
FROM INSERTED

INSERT INTO [dbo].[UserAudit](UserName,UserAction,ActionDate)
VALUES(@UserName,'INSERT',getdate())
END

Now if we insert a record in the Users table a new record will be inserted in the UserAudit table as well.If we execute the following insert statement
INSERT INTO [dbo].[Users]([UserName],[Email],[Password],[Id],[IsActive])
VALUES(‘Ashish’,’test@test.com’,’pwd’,1,1)

now if we check the UserAudit table we can see the following record

INSTEAD OF Triggers

These triggers are executed before the original DML statement.For example INSTEAD OF DELETE Trigger will be executed before the delete statement.In place of the delete statement INSTEAD OF DELETE Trigger will be executed.Similar to FOR Trigger INSTEAD OF Trigger can also be defined for each of the DML statements

INSTEAD OF INSERT          Trigger Trigger is fired in place of the actual insert statement
INSTEAD OF UPDATE         Trigger Trigger is fired in place of the actual update statement
INSTEAD OF DELETE          Trigger Trigger is fired in place of the actual delete statement

Example of Instead of Delete trigger

Instead of Delete trigger is used to enforce a check to verify if the valid rows are being deleted.We are defining a Instead of Delete trigger for the Users table.We are verifying the id of the row being deleted.If the id is 1 then the delete statement is rolled back.

CREATE  TRIGGER Delete_UserAudit
ON [dbo].[Users] 
INSTEAD OF DELETE
AS 
BEGIN
SET NOCOUNT ON;
DECLARE @Id INT

SELECT @Id =Id FROM DELETED

IF @Id=1
BEGIN
RAISEERROR('CAN NOT DELETE RECORD WITH ID 1',16,1);
ROLLBACK
END
ELSE
BEGIN
DELETE FROM Users where Id=@Id
END


END
GO

 

Follow on: