Introduction
The first and most important thing in everyone's mind is, “What are triggers, and why do we use them?” So a trigger is nothing but a special kind of Stored Procedure.
Trigger in SQL
"A trigger is a special kind of Stored Procedure or stored program that is automatically fired or executed when some event (insert, delete and update) occurs."
If you write a trigger for an insert operation on a table, after firing the trigger, it creates a table named “INSERTED” in memory. Then it does the insert operation and then the statements inside the trigger execute. We can query the “INSERTED” table to manipulate or use the inserted row(s) from the trigger.
Similarly, if you write a trigger for a delete operation on a table, it creates a table in memory named “DELETED” and then deletes the row.
Why and when to use a trigger
We use a trigger when we want some event to happen automatically in certain desirable scenarios.
Let's see an example.
You have a table that changes frequently. Now you want to know how many times and when these changes take place.
In that case, you can create a trigger that will insert the desired data into another table whenever any change in the main table occurs.
Types of Triggers in SQL Server
In SQL Server, we can create the following 3 types of triggers:
- Data Definition Language (DDL) triggers
- Data Manipulation Language (DML) triggers
- Logon triggers
DDL Triggers
In SQL Server, we can create triggers on DDL statements (like CREATE, ALTER, and DROP) and certain system-defined Stored Procedures that do DDL-like operations.
DML Triggers
In SQL Server, we can create triggers on DML statements (like INSERT, UPDATE, and DELETE) and Stored Procedures that do DML-like operations. DML Triggers are of two types.
After trigger (using FOR/AFTER CLAUSE)
The After trigger (using the FOR/AFTER CLAUSE) fires after the SQL Server finishes the execution of the action successfully that fired it.
Example. If you insert a record/row into a table, then the trigger related/associated with the insert event on this table will fire only after the row passes all the constraints, such as the primary key constraint and some rules. If the record/row insertion fails, the SQL Server will not fire the After Trigger.
Instead of Trigger (using INSTEAD OF CLAUSE)
The Instead of Trigger (using the INSTEAD OF CLAUSE) fires before the SQL Server starts the execution of the action that fired it. This is different from the AFTER trigger, which fires after the action that caused it to fire. We can have an INSTEAD OF insert/update/delete trigger on a table that was successfully executed but does not include the actual insert/update/delete to the table.
Example- If you insert a record/row into a table, then the trigger related/associated with the insert event on this table will fire before the row passes all the constraints, such as the primary key constraint and some rules. If the record/row insertion fails, SQL Server will fire the Instead of Trigger.
Logon Triggers
Logon triggers are a special type of trigger that fire when a LOGON event of SQL Server is raised. This event is raised when a user session is being established with SQL Server that is made after the authentication phase finishes but before the user session is actually established. Hence, all messages that we define in the trigger, such as error messages, will be redirected to the SQL Server error log. Logon triggers do not fire if authentication fails. We can use these triggers to audit and control server sessions, such as to track login activity or limit the number of sessions for a specific login.
In short, the following are the various types of triggers.
DML Trigger
- Instead of Trigger: An Instead of the trigger is fired instead of the triggering action such as an insert, update, or delete
- After Trigger: An After trigger executes following the triggering action, such as an insert, update, or delete
DDL Trigger
This type of trigger is fired against DDL statements like Drop Table, Create Table, or Alter Table. DDL Triggers are always After Triggers.
Logon trigger
This type of trigger is fired against a LOGON event before a user session is established on the SQL Server.
Syntax
The following is the very easy and useful syntax of triggers.
CREATE TRIGGER triggerName ON table
AFTER INSERT |After Delete |After Update
AS BEGIN
INSERT INTO dbo.UserHistory............
END
Create a Table
CREATE TABLE Employee_Test
(
Emp_ID INT Identity,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2)
)
Insert records into it
INSERT INTO Employee_Test VALUES ('Anees',1000);
INSERT INTO Employee_Test VALUES ('Rick',1200);
INSERT INTO Employee_Test VALUES ('John',1100);
INSERT INTO Employee_Test VALUES ('Stephen',1300);
INSERT INTO Employee_Test VALUES ('Maria',1400);
Create another table to store transaction records, like records of insert, delete, and update on the Employee_Test Table
CREATE TABLE Employee_Test_Audit
(
Emp_ID int,
Emp_name varchar(100),
Emp_Sal decimal (10,2),
Audit_Action varchar(100),
Audit_Timestamp datetime
)
Create a Trigger in SQL
CREATE TRIGGER trgAfterInsert ON [dbo].[Employee_Test]
FOR INSERT
AS
declare @empid int;
declare @empname varchar(100);
declare @empsal decimal(10,2);
declare @audit_action varchar(100);
select @empid=i.Emp_ID from inserted i;
select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i;
set @audit_action='Inserted Record -- After Insert Trigger.';
insert into Employee_Test_Audit
(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@empid,@empname,@empsal,@audit_action,getdate());
PRINT 'AFTER INSERT trigger fired.'
GO
Now insert a record into Employee_Test
insert into Employee_Test values('Ravi',1500);
Then see the Employee_Test_Audit table
Select * from Employee_Test_Audit
Conclusion
In this article, we learned about Triggers in SQL and how to create Triggers in SQL with Code examples.