SQL Trigger

 Trigger: A trigger is a stored procedure in database which automatically invokes whenever a special event in the database occurs. For example, a trigger can be invoked when a row is inserted into a specified table or when certain table columns are being updated.

Syntax:

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

Explanation of syntax:

1. create trigger [trigger_name]: Creates or replaces an existing trigger with the trigger_name.
2. [before | after]: This specifies when the trigger will be executed.
3. {insert | update | delete}: This specifies the DML operation.
4. on [table_name]: This specifies the name of the table associated with the trigger.
5. [for each row]: This specifies a row-level trigger, i.e., the trigger will be executed for each row being affected.
6. [trigger_body]: This provides the operation to be performed as trigger is fired

BEFORE and AFTER of Trigger:
BEFORE triggers run the trigger action before the triggering statement is run.
AFTER triggers run the trigger action after the triggering statement is run.

Example:
Given Student Report Database, in which student marks assessment is recorded. In such schema, create a trigger so that the total and average of specified marks is automatically inserted whenever a record is insert.

Here, as trigger will invoke before record is inserted so, BEFORE Tag can be used.

Suppose the database Schema –

mysql> desc Student; 
+-------+-------------+------+-----+---------+----------------+ 
| Field | Type        | Null | Key | Default | Extra          | 
+-------+-------------+------+-----+---------+----------------+ 
| tid   | int(4)      | NO   | PRI | NULL    | auto_increment | 
| name  | varchar(30) | YES  |     | NULL    |                | 
| subj1 | int(2)      | YES  |     | NULL    |                | 
| subj2 | int(2)      | YES  |     | NULL    |                | 
| subj3 | int(2)      | YES  |     | NULL    |                | 
| total | int(3)      | YES  |     | NULL    |                | 
| per   | int(3)      | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+ 
7 rows in set (0.00 sec)

SQL Trigger to problem statement.

create trigger stud_marks 
before INSERT 
on 
Student 
for each row 
set Student.total = Student.subj1 + Student.subj2 + Student.subj3, Student.per = Student.total * 60 / 100;

Above SQL statement will create a trigger in the student database in which whenever subjects marks are entered, before inserting this data into the database, trigger will compute those two values and insert with the entered values. i.e.,

mysql> insert into Student values(0, "ABCDE", 20, 20, 20, 0, 0); 
Query OK, 1 row affected (0.09 sec) 

mysql> select * from Student; 
+-----+-------+-------+-------+-------+-------+------+ 
| tid | name  | subj1 | subj2 | subj3 | total | per  | 
+-----+-------+-------+-------+-------+-------+------+ 
| 100 | ABCDE |    20 |    20 |    20 |    60 |   36 | 
+-----+-------+-------+-------+-------+-------+------+ 
1 row in set (0.00 sec)

In this way trigger can be creates and executed in the databases.

ypes of SQL Server Triggers

We can categorize the triggers in SQL Server in mainly three types:

1. Data Definition Language (DDL) Triggers
2. Data Manipulation Language (DML) Triggers
3. Logon Triggers

DDL Triggers

DDL triggers are fired in response to the DDL events, such as CREATE, ALTER, and DROP statements. We can create these triggers at the database level or server level, depending on the type of DDL events. It can also be executed in response to certain system-defined stored procedures that do DDL-like operations.

The DDL triggers are useful in the following scenario:

• When we need to prevent the database schema from changing
• When we need to audit changes made in the database schema
• When we need to respond to a change made in the database schema

DML Triggers

DML triggers are fired in response to DML events like INSERT, UPDATE, and DELETE statements in the user's table or view. It can also be executed in response to DML-like operations performed by system-defined stored procedures.

The DML triggers can be classified into two types:

• After Triggers
• Instead Of Triggers

After Triggers

After trigger fires, when SQL Server completes the triggering action successfully, that fired it. Generally, this trigger is executed when a table completes an insert, update or delete operations. It is not supported in views. Sometimes it is known as FOR triggers.

We can classify this trigger further into three types:

1. AFTER INSERT Trigger
2. AFTER UPDATE Trigger
3. AFTER DELETE Trigger

Example: When we insert data into a table, the trigger associated with the insert operation on that table will not fire until the row has passed all constraints, such as the primary key constraint. SQL Server cannot fire the AFTER trigger when the data insertion failed.

The following is illustration of the After Triggers syntax in SQL Server:

1. CREATE TRIGGER schema_name.trigger_name  
2. ON table_name  
3. AFTER {INSERT | UPDATE | DELETE}  
4. AS  
5.    BEGIN  
6.       -- Trigger Statements  
7.       -- Insert, Update, Or Delete Statements  
8.    END  

Instead of Triggers

Instead of Trigger fires before SQL Server begins to execute the triggering operation that triggered it. It means that no condition constraint check is needed before the trigger runs. As a result, even if the constraint check fails, this trigger will fire. It is the opposite of the AFTER trigger. We can create the INSTEAD OF triggers on a table that executes successfully but doesn't contain the table's actual insert, update, or delete operations.

We can classify this trigger further into three types:

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

Example: When we insert data into a table, the trigger associated with the insert operation on that table will fire before the data has passed all constraints, such as the primary key constraint. SQL Server also fires the Instead of Trigger if the data insertion fails.

The following is an illustration of the Instead of Triggers syntax in SQL Server:

1. CREATE TRIGGER schema_name.trigger_name  
2. ON table_name  
3. INSTEAD OF {INSERT | UPDATE | DELETE}  
4. AS  
5.    BEGIN  
6.       -- trigger statements  
7.       -- Insert, Update, or Delete commands  
8.    END  

Logon Triggers

Logon triggers are fires in response to a LOGON event. The LOGON event occurs when a user session is generated with an SQL Server instance, which is made after the authentication process of logging is completed but before establishing a user session. As a result, the SQL Server error log will display all messages created by the trigger, including error messages and the PRINT statement messages. If authentication fails, logon triggers do not execute. These triggers may be used to audit and control server sessions, such as tracking login activity or limiting the number of sessions for a particular login.

How to SHOW Triggers in SQL Server?

When we have many databases with multiple tables, the show or list trigger comes in handy. When the table names in multiple databases are the same, this query is extremely helpful. Using the following command, we can see a list of all the triggers available in SQL Server:

1. SELECT name, is_instead_of_trigger  
2. FROM sys.triggers    
3. WHERE type = 'TR';  

If we are using the SQL Server Management Studio, it is very easy to show or list all triggers available in any specific table. We can do this using the following steps:

• Go to the Databases menu, select desired database, and then expand it.
• Select the Tables menu and expand it.
• Select any specific table and expand it.

We will get various options here. When we choose the Triggers option, it displays all the triggers available in this table.

How to UPDATE Triggers in SQL Server?

The data stored in the table can be changed over a period of time. In that case, we also need to make changes in the triggers. We can do this in two ways into the SQL Server. The first one is to use the SQL Server Management Studio, and the second one is the Transact-SQL Query.

Modify Triggers using SSMS

First, open the Management Studio to modify the trigger. Next, go to the database and then the table where the trigger is stored. Now, right-click on the trigger that you are going to change or update. It will open the context menu where you will choose Modify option:

When you select the Modify option, you will see a new query window with automatically generated ALTER TRIGGER code. We can change it according to our needs.

Modify Triggers using SQL Command

We can use the ALTER TRIGGER statement to modify the triggers in MS SQL. The following statement allows us to do modifications to the triggers:

1. ALTER TRIGGER [dbo].[triggers_in_sql]   
2.    ON  [dbo].[EmployeeTable]   
3.    AFTER INSERT  
4. AS   
5. BEGIN  
6.  -- Modify as per your needs  
7. END   

How to DELETE Triggers in SQL Server?

We can remove an existing trigger in SQL Server using the DROP TRIGGER statement. We must be very careful while removing a trigger from the table. Because once we have deleted the trigger, it cannot be recovered. If a trigger is not found, the DROP TRIGGER statement throws an error.

The following syntax removes DML triggers:

1. DROP TRIGGER [IF EXISTS] schema_name.trigger_name;  

If we want to remove more than one trigger at once, we must separate the trigger using the comma operator:

1. DROP TRIGGER schema_name.trigger_name1, trigger_name2.....n;  

We can use the DROP TRIGGER statement in the below format to delete one or more LOGON triggers:

1. DROP TRIGGER [ IF EXISTS ] trigger_name1, trigger_name2.....n  
2. ON { DATABASE | ALL SERVER };  

We can use the DROP TRIGGER statement in the below format to delete one or more DDL triggers:

1. DROP TRIGGER [ IF EXISTS ] trigger_name1, trigger_name2.....n    
2. ON ALL SERVER;  

If we are using the SQL Server Management Studio, it makes it very easy to delete triggers from the table. We can do this using the following steps:

• Go to the Databases -> Tables menu and expand it.
• Select any specific table, expand it, and choose the Triggers option

This option displays all the available triggers in this table. Now, right-click on any specific trigger that you want to remove and choose the Delete option from the drop-down menu.

Advantages of Triggers

The following are the advantages of using triggers in SQL Server:

• Triggers set database object rules and roll back if any change does not satisfy those rules. The trigger will inspect the data and make changes if necessary.
• Triggers help us to enforce data integrity.
• Triggers help us to validate data before inserted or updated.
• Triggers help us to keep a log of records.
• Triggers increase SQL queries' performance because they do not need to compile each time they are executed.
• Triggers reduce the client-side code that saves time and effort.
• Triggers are easy to maintain.

Disadvantages of Triggers

The following are the disadvantages of using triggers in SQL Server:

• Triggers only allow using extended validations.
• Triggers are invoked automatically, and their execution is invisible to the user. Therefore, it isn't easy to troubleshoot what happens in the database layer.
• Triggers may increase the overhead of the database server.
• We can define the same trigger action for multiple user actions such as INSERT and UPDATE in the same CREATE TRIGGER statement.
• We can create a trigger in the current database only, but it can reference objects outside the current database.