SQL Triggers: The Essential Guide

Triggers are database entities in SQL Server. Technically, they are a particular class of function calls that responds to specific database operations.

This essential guide will give you in-depth information about SQL Triggers that can be quite useful in your profession. Let’s get started!

What are SQL Triggers?

The word “trigger” describes a statement that a server automatically executes the query each time the content in the database is altered.

A trigger is a group of specially named SQL queries that are stored in memory space. It is a specific type of function call that is immediately invoked whenever any database event takes place. Every trigger has a table assigned to it.

For instance, a trigger could be activated whenever a new column is added to a particular table or if specific records are altered.

According to Microsoft Developer Network, triggers are a particular class of stored procedures. In a trigger statement, we initially define when the trigger should be run, and then we provide the action that should be taken after the trigger has been activated.

Syntax:

CREATE TRIGGER trigger_name
BEFORE/AFTER
INSERT/UPDATE/DELETE
ON tableName
FOR EACH ROW SET operation [trigger_body];

Explanation of each parameter

• CREATE TRIGGER trigger_name – It is used to construct a trigger or to change the name of an existing trigger.
• BEFORE/AFTER – This query is used to define the trigger’s execution time ( before or after a certain event).
• INSERT/UPDATE/DELETE – This describes the action we wish to take on the tables.
• ON tableName – Here, we define the table name to set up a trigger.
• FOR EACH ROW – This statement relates to row trigger, meaning that triggers will be executed whenever a row is altered.
• trigger_body – It specifies the action to be taken when the trigger is activated.

Triggers are stored functions with distinctive identities that enable us to reuse queries that have already been executed and stored securely in memory. Now let’s try to understand why SQL needs them.

Triggers are mostly used to regulate the deployment of code whenever an event takes place. To put it another way, using triggers is the ideal choice if you require a specific code fragment to be constantly run in response to a specified event.

The following are some benefits of utilizing triggers in SQL database operations.

1. Performs additional checks when inserting, updating, or deleting data from the impacted table.
2. Reduces response times, which aids in raising computing expenses.
3. Enables the encoding of sophisticated default parameters that are inaccessible by initial constraints.

Referential integrity is a key core property of relational database systems. This signifies that the data kept in the database system must always be accurate for each transaction and each operation.

If two tables are located on separate databases or systems, there isn’t a way to ensure data validation within them using constraint values. In such a situation, triggers are the only option for execution.

Combination of Trigger Arguments

For every table, we can specify six different trigger types. These are the combination of Trigger arguments included in SQL row-level Triggers.

BEFORE INSERT: These triggers execute the action on the rows prior to performing any INSERT operations in the specified table or on the database.

AFTER INSERT: It executes the action on rows immediately following any database INSERT activity.

BEFORE UPDATE: With these triggers, a function on the rows is carried out prior to an UPDATE action being performed on the database.

AFTER UPDATE: It executes the action on rows immediately following any database or specific table UPDATE activity.

BEFORE DELETE: It executes a certain operation on rows even before the database or table is subjected to a DELETE action.

AFTER DELETE: These triggers execute the action on the rows following every DELETE transaction.

Types of SQL Triggers

SQL triggers are stored functions that run immediately when specific events occur. It resembles event-driven scheduling. The subsequent situations can start the execution of triggers.

DML Triggers – DML stands for Data Manipulation Language. Code execution in reaction to data modification is made possible using DML Triggers. This trigger is activated when DML commands like INSERT, UPDATE, and DELETE are executed. These are also called “Table Level Triggers”.

DDL Triggers – DDL stands for Data Definition Language. DDL Triggers enable us to run code in reaction to database schema changes, such as adding or deleting tables, or server events, such as when a user checks in. These are called “Database Level Triggers”.

These Triggers can be activated, When certain DDL statements such as CREATE, ALTER, or DROP are executed in the active database. These also can be utilized to keep an eye on and manage the activities that are executed.

LOGON Triggers – Whenever any LOGON (Startup, login, Logout, shutdown) event occurs, Logon Triggers are immediately invoked. They are only carried out following a user authentication process even before the user transaction is initiated. The LOGON triggers won’t be triggered if authorization fails.

These triggers can be used to record login history or establish an event restriction for a particular login, among other auditing and identity management functions for server connections.

CLR Triggers – CLR stands for Common Language Runtime. CLR triggers are indeed a unique subset of triggers built mostly on CLR within the .NET technology. These triggers are helpful if the trigger needs to perform a lot of calculations or needs to relate to an entity other than SQL.

DML and DDL triggers can indeed be constructed by enabling the coding of supported CLR triggers in .NET technologies including Visual Basic, C#, and F-sharp.

Sample SQL Server Trigger

Let’s understand these trigger concepts with an example.

First, let’s create a database using SQL statements.

CREATE DATABASE testdb;
use testdb;

Here, I have given a “testdb” as the name of the database. And the next step is to create a table.

CREATE TABLE student(
  name varchar(25),
  id int(2),
  maths int(2),
  physics int(2),
  biology int(2),
  social int(2),
  total int(2)
 );

I have created a table for storing the student details. And here is the command to describe the structure of the table. Here “student” is the table name I have given.

DESC student;

Below is the structure of the table I have created.

 --------- ------------- ------ ----- --------- ------- 
| Field   | Type        | Null | Key | Default | Extra |
 --------- ------------- ------ ----- --------- ------- 
| name    | varchar(25) | YES  |     | NULL    |       |
| id      | int         | YES  |     | NULL    |       |
| maths   | int         | YES  |     | NULL    |       |
| physics | int         | YES  |     | NULL    |       |
| biology | int         | YES  |     | NULL    |       |
| social  | int         | YES  |     | NULL    |       |
| total   | int         | YES  |     | NULL    |       |
 --------- ------------- ------ ----- --------- ------- 
7 rows in set (0.00 sec)

After creating a table, the next step is to set up a trigger. Let’s try using the BEFORE INSERT argument.

The name of the trigger I have created is “marks”. As soon as the table is modified with the student’s grades, the trigger below tries to determine the student’s overall grade automatically.

CREATE TRIGGER marks
BEFORE INSERT
ON
student
FOR EACH ROW
set new.total=new.maths new.physics new.biology new.social;

Since we are required to replace rows data rather than working with the old ones, we have defined “total” using a new class name, and all subsequent expressions are prefixed with new keywords after total using the dot operator. Now, we will add values to each row and see the results. Initially, the total mark are 0 for every student.

INSERT INTO student VALUES("George",02,99,87,92,91,0);
INSERT INTO student VALUES("James",03,91,81,94,90,0);
INSERT INTO student VALUES("Harry",04,86,70,73,88,0);
INSERT INTO student VALUES("John",05,73,89,78,92,0);
INSERT INTO student VALUES("Lisa",01,94,75,69,79,0);

The trigger statement will be automatically triggered when data is inserted into the student table in this case. Each student’s total marks will be calculated by the trigger. Now, let’s see whether the trigger is invoked or not using a SELECT statement.

SELECT * FROM table_name;

And here is the final output.

mysql> select * from student;
 -------- ------ ------- --------- --------- -------- ------- 
| name   | id   | maths | physics | biology | social | total |
 -------- ------ ------- --------- --------- -------- ------- 
| George |    2 |    91 |      81 |      94 |     90 |   356 |
| James  |    3 |    86 |      70 |      73 |     88 |   317 |
| Harry  |    4 |    73 |      89 |      78 |     92 |   332 |
| John   |    5 |    94 |      75 |      69 |     79 |   317 |
| Lisa   |    1 |    99 |      87 |      92 |     91 |   369 |
 -------- ------ ------- --------- --------- -------- ------- 
5 rows in set (0.00 sec)

In the above result, you can see that all subject marks are automatically added for each student. So, we can conclude the trigger was invoked successfully.

Additional Trigger Operations

We can perform many operations using triggers. Some may be simple and some may be a little complex, but once we go through the queries it’s easy to understand. By employing Transact-SQL statements, you can enable, disable or delete the triggers using the following commands.

Query to check whether a specific Trigger is there or not

This command checks for the specified trigger in the whole database.

SELECT * FROM [sys].[triggers] WHERE [name] = 'Trigger_name'

Query to display triggers

All the triggers that are available in the active database will be shown by the following statement.

SHOW TRIGGERS;

Query to Disable Trigger

The below command deactivates the trigger in the working database.

DISABLE TRIGGER trigger_name ON DATABASE;

You can also specify a certain table name to disable a trigger.

DISABLE TRIGGER trigger_name ON table_name;

Query to Enable Trigger

The following command first deactivates a specific trigger that was defined on the specified table in the active database before re-enabling it.

ALTER TABLE table_name DISABLE TRIGGER trigger_name

ALTER TABLE table_name ENABLE TRIGGER trigger_name

The trigger must be disabled, before trying to enable it,

Query to enable or disable all triggers in a table

Using the above SQL statement, we can deactivate or activate all the table triggers at a time by substituting “ALL” in place of a specific trigger name.

ALTER TABLE table_name DISABLE TRIGGER ALL 

ALTER TABLE table_name ENABLE TRIGGER ALL

Query to Delete or Drop Trigger

A trigger can be eliminated by deleting it or the whole table. Every related trigger is also deleted when a table is deleted.

DROP TRIGGER [trigger_name];

Whenever a trigger is deleted, the related data is eliminated from the sys.objects data table.

Advantages of Triggers

• It’s simple to build triggers and the trigger itself can invoke stored functions and methods.
• Users can implement simple auditing using triggers.
• Tragically, you cannot create constraints across entities in database systems with SQL Server, although you can emulate the operation of constraints by utilizing triggers.
• Integrity constraints can be implemented across databases using triggers.
• When group validation is required rather than row-by-row verification of newly entered or changed data, triggers can be helpful.

Disadvantages of Triggers

SQL Triggers may not be the best choice in some situations due to their limitations.

• Triggers must be accurately documented.
• Due to simultaneous database execution that might not be accessible to application components, triggers can be challenging to debug.
• DML statements become more complex when triggers are used.
• Even a minor trigger issue has the potential to lead to logical faults in the statement.

Conclusion

Triggers are very helpful components of Transact-SQL and SQL, and you can use them in Oracle as well. The use of triggers is crucial when calling stored methods. These SQL Triggers allow us to analyze the activity timelines and determine how to respond to them if necessary. We can also check for a certain table that is connected to a trigger to acquire data.

Recursion can be enabled by triggers. Whenever a trigger on a table executes a command on the parent table, the second iteration of the trigger is set off, and this is known as a recursive trigger. This helps while trying to solve an identity correlation.

Additionally, triggers regulate the updating pattern that the database is permitted to accept. It is highly beneficial to keep the data integrity constraints in the database system if SQL constraint keys don’t exist mainly the primary key and foreign key.

I hope you found this article helpful in learning about SQL Triggers.

If you wish to learn databases in-depth, here are some excellent resources to learn SQL and NoSQL.