Different Types of Triggers In SQL Server

Triggers In SQL Server

Triggers in SQL Server are database objects. Triggers in SQL Serverare automated processes that carry out in reaction to particular DDL and DML SQL instructions, such as those that insert, update, or remove records from a table. SQL Server triggers help keep data accurate and make sure rules are followed automatically, without needing extra code. They are great for tracking changes and enforcing business rules directly within the database.

This SQL Server tutorialwill help us to understand what are the Triggers in SQL, including the syntax of triggers in SQL Server,when do we use triggers,the types of SQL triggers, How to show triggers in SQL Server, and many more.

What are the Triggers in SQL Server?

Triggers in SQL Server are reactions that happen automatically anytime information in a table is added, changed, or removed. They aid in making sure that duties are completed without the need for manual intervention and that certain regulations are followed. Triggers in SQL Server can be of two types: INSTEAD OF triggers, which substitute a custom action for the standard one, and AFTER triggers, which execute after the data changes.

We will explain to you some important points for Triggers in SQL Server:

• Triggers automatically run when you add, update, or delete data in a table.
• There are two types: AFTER triggers that run after changes and INSTEAD OF triggers that take over the usual action.
• They help make sure that important rules are followed when data is changed.
• Triggers can keep a record of changes, which is useful for checking what happened and when.
• They can slow things down if they are too complex or used too often, so they should be used carefully.

When do we use triggers in SQL Server?

We should use the Triggers SQL server when:

• With triggers, we want to have actions taken automatically when data is added, changed, or removed from a table.
• We want to enforce important rules, such as preventing the entry of invalid data.
• We want to track changes by logging who made updates and when they occurred.
• We want to ensure data consistency by making related changes together.
• We want to implement complex business logic that cannot be managed with regular SQL queries.

Triggers in SQL Syntax

create trigger [trigger_name] 
[before | after]  
{insert | update | delete}  
on [table_name]  
[for each row]  
[trigger_body] 

Explanation

• Create trigger [trigger_name]: Creates or updates a trigger with the name trigger_name.
• [before | after]: Decides if the trigger runs before or after the action happens.
• {insert | update | delete}: Specifies if the trigger runs when data is inserted, updated, or deleted.
• On [table_name]: The name of the table where the trigger applies.
• [for each row]: Runs the trigger for every row that is changed.
• [trigger_body]: Defines the task that the trigger will perform when it is activated.

Example of Triggers in SQL

Here, we are creating a table named Employee by using the SQL Statement:

CREATE TABLE Employee  
(  
  Id INT PRIMARY KEY,  
  Name VARCHAR(45),  
  Salary INT,  
  Gender VARCHAR(12),  
  DepartmentId INT  
)  

Employee Table-

After creating the table, we insert the values into the table by using the SQL commands:

INSERT INTO Employee VALUES (1,'Aman', 82000, 'Male', 3),  
(2,'Ankita', 52000, 'Female', 2),  
(3,'Shailesh', 25000, 'Male', 1),  
(4,'Rakesh', 47000, 'Male', 2),  
(5,'Monika', 46000, 'Female', 3)  

After inserting these values into that element, are added to that table or not by using the following statement

SELECT * FROM Employee;  

Employee Table-

Now, we will create another table named Employee_Test to automatically keep track of every operation's transaction records, including INSERT, UPDATE, and DELETE, in the Employee table:

CREATE TABLE Employee_Test  
(    
Id int IDENTITY,   
Audit_Action text   
)  

In order to save transaction records of each insert operation on the Employee table into the Employee_Test table, we will now develop a trigger. Using the following line, we will now build the insert trigger:

CREATE TRIGGER trInsertEmployee   
ON Employee  
FOR INSERT  
AS  
BEGIN  
  Declare @Id int  
  SELECT @Id = Id from inserted  
  INSERT INTO Employee_Test  
  VALUES ('New employee with Id = ' + CAST(@Id AS VARCHAR(10)) + ' is added at ' + CAST(Getdate() AS VARCHAR(22)))  
END  

We will attempt to add the following record to the table after establishing a trigger:

INSERT INTO Employee VALUES (6,'Mohan', 62000, 'Male', 3)  

Now, we will create another trigger to enter transaction data into the Employee_Test table for every delete operation performed on the Employee table. The following sentence may be used to generate the delete trigger:

CREATE TRIGGER trDeleteEmployee   
ON Employee  
FOR DELETE  
AS  
BEGIN  
  Declare @Id int  
  SELECT @Id = Id from deleted  
  INSERT INTO Employee_Audit_Test  
  VALUES ('An existing employee with Id = ' + CAST(@Id AS VARCHAR(10)) + ' is deleted at ' + CAST(Getdate() AS VARCHAR(22)))  
END  

The data will be deleted from the table:

Types of Triggers

In SQL Server, we can create four types of triggers:

1. DDL Triggers

In SQL Server, we can create triggers for DDL statements (like CREATE, ALTER, and DROP) and certain system-defined stored procedures that perform DDL-like operations.

Example

If you are going to execute the CREATE LOGIN statement or the sp_addlogin stored procedure to create a login user, then both these can execute/fire a DDL trigger that you can make on the CREATE_LOGIN event of SQL Server.

We can use only the FOR/AFTER clause in DDL triggers, not the INSTEAD OF clause, which means we can only make After Trigger on DDL statements.

DDL trigger can be used to observe and control actions performed on the server and to audit these operations. DDL triggers can be used to manage administrative tasks such as auditing and regulating database operations.

2. DML Triggers

In SQL Server, DML triggers are like automatic responses in a database that run whenever you add, update, or delete data in a table. They help keep the data correct and follow specific rules, such as stopping certain changes or recording what got updated. This way, you don’t have to worry about doing these tasks manually each time something changes in the data.

1. After Triggers

An AFTER trigger runs automatically after you add, update, or delete data in a table. It helps to perform extra tasks like updating other data or keeping records once the main action is completed. This makes sure that essential follow-up steps happen without you having to do them manually.

There are three types of After Triggers in SQL Server that are following below:

1. After Insert Trigger
2. After Update Trigger
3. After Delete Trigger

Example

If you insert a record/row in a table, then the trigger related/associated with the insert event on this table will fire only after the row passes all the constraints, like the primary key constraint and some rules. If the record/row insertion fails, the SQL Server will not fire the After Trigger.

Syntax of After Triggers in SQL Server

CREATE TRIGGER trigger_name
ON table_name
AFTER INSERT, UPDATE, DELETE  -- Specify one or more actions (INSERT, UPDATE, DELETE)
AS
BEGIN
    -- Trigger logic goes here
END

2. Instead of Trigger (using INSTEAD OF CLAUSE)

INSTEAD OF triggers in SQL Server are special triggers that replace the usual action, like inserting, updating, or deleting data in a table. Instead of the database performing the action directly, the trigger runs custom logic in its place. This allows you to control or modify what happens when someone tries to change the data, giving you more flexibility to handle things like complex validations or prevent certain changes.

There are three types of Instead of Triggers that are following below:

1. INSTEAD OF INSERT Trigger
2. INSTEAD OF UPDATE Trigger
3. INSTEAD OF DELETE Trigger

Example

If you insert a record/row in 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, the SQL Server will fire it instead of Trigger.

Syntax of Instead of Trigger

CREATE TRIGGER trigger_name
ON table_name
INSTEAD OF INSERT, UPDATE, DELETE  -- Specify one or more actions (INSERT, UPDATE, DELETE)
AS
BEGIN
    -- Custom logic goes here
END

3. CLR Triggers

CLR triggers are a special type of triggers based on the CLR (Common Language Runtime) in the .NET framework. CLR integration of triggers has been introduced with SQL Server 2008 and allows for triggers to be coded in one of the .NET languages like C#, Visual Basic, and F#.

We coded the objects(like triggers) in the CLR that have heavy computations or need references to objects outside the SQL Server. We can write code for both DDL and DML triggers using a supported CLR language like C#, Visual Basic, and F#. I will discuss the CLR trigger later.

4. Logon Triggers

Logon triggers are a special type of trigger that fire when the LOGON event of SQL Server is raised. This event is raised when a user session is being established with SQL Server, which is made after the authentication phase finishes but before the user session is established. Hence, all messages 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.

Example

A logon trigger can be used to restrict access to a SQL Server database outside of business hours. For instance, it can prevent users from logging in if the current time is not between 9 AM and 6 PM, ensuring that only authorized access occurs during specified times.

Syntax for Logon Trigger

CREATE TRIGGER trigger_name
ON ALL SERVER
FOR LOGON
AS
BEGIN
    -- Custom logic goes here
END

How does the SQL Server show Triggers?

We will use the SQL commands to show the Triggers in the SQL Server:

1. Triggers for Specific Table

To show the triggers in a specific table, we use the following commands that are mentioned below:

SELECT 
    name AS TriggerName,
    OBJECT_NAME(parent_id) AS TableName,
    type_desc AS TriggerType,
    is_disabled AS IsDisabled
FROM 
    sys.triggers
WHERE 
    parent_id = OBJECT_ID('YourTableName');

List All the Triggers

We will use the following commands to list all the triggers in a table

SELECT 
    name AS TriggerName,
    OBJECT_NAME(parent_id) AS TableName,
    type_desc AS TriggerType,
    is_disabled AS IsDisabled
FROM 
    sys.triggers;

The SQL Server Management Studio makes it extremely simple to display or list every trigger that is accessible in a given table. To do this, we may use the following steps:

• Select the relevant database from the Databases menu, then click on it to expand.
• Click the Tables menu and make it larger.
• Expand any particular table by selecting it.

Here, we'll have a range of choices. All of the triggers that are present in this table are displayed when we select the Triggers option.

How do we update Triggers in SQL Server?

1. Update Triggers using SSMS

In order to change the trigger, first enter the Management Studio. Proceed to the database and, subsequently, the table containing the trigger. Right-click on the trigger that you want to alter or modify now. The context menu will appear, and you may select the Modify option there:

A new query window with ALTER TRIGGER code that has been generated automatically will appear when you choose the Modify option. We are able to modify it to suit our needs.

2. Update Triggers Using SQL Commands

Because SQL Server does not support direct trigger modification, upgrading a trigger in SQL Server requires deleting the old trigger and generating a new one with the modified logic. This is a step-by-step guide on updating a trigger:

1. Check the Existing Trigger

It is advisable to go over the trigger definition as it stands first. To view it, use this query.

sp_helptext 'trigger_name';

2. Drop the Old Trigger

DROP TRIGGER must be used to remove the current trigger before you can make any changes:

DROP TRIGGER trigger_name;

3. Create the Updated Trigger

Create a new trigger and make the required modifications after removing the previous one. The fundamental trigger creation syntax is:

CREATE TRIGGER trigger_name
ON table_name
AFTER INSERT, UPDATE, DELETE  -- Choose the action type (INSERT, UPDATE, DELETE)
AS
BEGIN
    -- Your updated trigger logic here
END;

Advantages of Triggers

• Automatic execution: Triggers automatically run when an event such as INSERT, UPDATE, or DELETE occurs.
• Enforces business rules: Triggers help apply business rules consistently within the database.
• Data validation: Triggers ensure that only valid data is inserted or updated in the tables.
• Audit logging: Triggers track changes to data, making it easier to maintain audit logs.
• Reduces manual work: Triggers automate tasks, reducing the need for manual intervention, such as updating related data.
• Consistent updates: Triggers help maintain consistency by ensuring related data across different tables is updated together.
• Centralized logic: Triggers centralize business rules and logic directly within the database.

Disadvantages of Triggers

• Need for proper documentation: Triggers require thorough documentation to ensure that their purpose and logic are clear to developers and database administrators.
• Add overhead to DML statements: Triggers can introduce additional processing time, which may negatively impact the performance of Data Manipulation Language (DML) statements.
• Limited to extended validations: Triggers are restricted to enforcing extended validations and may not be suitable for implementing more complex business logic.
• Difficulty in debugging: Debugging triggers can be challenging because they execute automatically without user input, making it hard to identify issues within their logic.
• Lack of control over execution: There is no control over when and how triggers execute, which complicates tracing and resolving errors in the data flow or logic.

Triggers in SQL Server are powerful tools that automate actions when data changes, or certain events occur, like logins. They help enforce rules, maintain data integrity, and handle special conditions without manual intervention. While triggers are useful, they should be used carefully to avoid performance issues and unnecessary complexity in the database. If you are interested in Full-Stack development, ScholarHat provides you with complete guidance in the Full-Stack Java Developer Certification Training Course and Full-Stack .NET Developer Certification Training Course.

Take our Sqlserver skill challenge to evaluate yourself!

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET FREE CHALLENGE