Triggers in SQL

What are the Triggers in SQL?

You might have encountered situations where you have to constantly check a database table for a particular column to get updated, where, once updated, you have some tasks to do.

Here the first solution which comes to mind is making a select statement (to check for a specific condition) on the table in an interval of a few seconds or minutes. But this doesn’t seem to be a good choice, right? Instead of us asking the database whether the data has been updated or not, the database should tell us when the data gets updated. This is what exactly triggers do. Triggers, as the name tells us, are stored procedures (procedures are functions that contain SQL statements stored in the database and can return some output) that are executed or fired when some events occur. The user defines these events. The trigger may be set on a row insertion in a table or an update to an existing row and at other points, which we will discuss here. Before moving forward, it is important to discuss what DDL and DML operations are.

DDL stands for Data definition language, which is used in changing the structure of a table, i.e., creating a table, adding a column to an existing table, or deleting the whole table. However, DML stands for Data manipulation language, which is used to manipulate the data, i.e., insert some new data into the table or update existing data. Also, for deleting some rows, we make use of DML operations.

Types of Triggers

The following are the different types of triggers present in SQL.

DML Triggers

These triggers fire in response to data manipulation language (DML) statements like INSERT, UPDATE, or DELETE.

After Triggers

These triggers execute after the database has processed a specified event (such as an INSERT, UPDATE, or DELETE statement). AFTER triggers are commonly used to perform additional processing or auditing tasks after a data modification has occurred.

Instead Triggers

These triggers are used for views and fire instead of the DML statement (INSERT, UPDATE, DELETE) on the view.

DDL Triggers

These triggers fire in response to data definition language (DDL) statements like CREATE, ALTER, or DROP.

LOGON Triggers

These triggers fire when a user logs into the database.

LOGOFF Triggers

These triggers fire when a user logs out of the database.

SERVERERROR Triggers

These triggers fire when a server error occurs.

Trigger Points of a SQL Trigger

1. When any DDL operation is done. E.g., CREATE, ALTER, DROP
2. For a DML operation. e.g., INSERT, UPDATE, DELETE.
3. For a database operation like LOGON, LOGOFF, STARTUP, SHUTDOWN or SERVERERROR

Syntax of Creating Triggers in SQL

We can create triggers for various types of operations, as discussed above. In this article, we will focus on DML triggers as these are the most commonly and extensively used triggers and also, at the same time, the most important. A DML trigger can be created by using the following syntax.

Let’s discuss the different parts of the syntax:

• CREATE [OR REPLACE ] TRIGGER trigger_name: In the first line, we give the name of the trigger we are creating/updating. Here [trigger_name] is to be replaced by the name you want to give to your trigger.
• {BEFORE / AFTER / INSTEAD OF }: Here, we define when the trigger would run, i.e., before or after the DML operation. For example, if we want a trigger to be executed after insertion to a table, we will write after here.
• {INSERT [OR] / UPDATE [OR] / DELETE}: This is the operation or event we have to define, which will trigger a procedure to be executed. If we want a procedure to run after a deletion happens on the table, then we will consider writing delete here.
• on [table_name]: Here, we have to specify the name of the table on which we are attaching the trigger. SQL will listen to changes on this table.
• [for each row]: This line specifies that the procedure will be executed for each one of the rows present. Here we can set a condition for which rows to be impacted. This part is optional, though; in case we don’t use this, the trigger shall convert to a “statement-level” trigger rather than being a “row-level” one, i.e., instead of firing the trigger procedure for each row, it will only execute once for each applicable statement.
• WHEN (condition): Here, we mention some condition basis on which the trigger will run. In the absence of a when condition, you can expect it to run for all the eligible rows. This is very important as this will control which rows the trigger must run.
• [trigger_body]: This is the main logic of what to perform once the trigger is fired. In the previous statements, all we defined is when this trigger will be fired, but here we have to define what to do after the trigger is fired. This is the main execution code.

Let’s take an example. Let’s assume a student table with column id, first_name, last_name, and full_name.

Query 1:

Here we will create a trigger to fill in the full name by concatenating the first and last names. So while inserting the values, we will only feed the first name and last name, and we will expect the trigger to automatically update each row with an additional column attribute bearing the full name.

First, let’s create a SQL Trigger –

Query 2:

Here we can understand from the trigger query we have set a trigger after an insert is made to the table student. Once the insert is done, this procedure will be fired, which will run an update command to update the students' full names.

Let’s insert the students.

Query 3:

Here we have inserted five students’ data, and since we have a trigger created in our system to update the full_name, we are expecting the full name to be non-empty if we run a select query on this table.

Query 4:

Output:

Here we can see since we had an update statement in the trigger procedure in query 2, the full names are automatically updated immediately after the inserts are done.

Please note that we have created an after trigger, which means the trigger will run after the insert is done (which is a DML operation).

Display Triggers in SQL

If someone creates a trigger but forgets the trigger's name, then you can find the trigger by running a simple command.

Query 6:

This command will show you the list of all available triggers matching with the string ‘stu’. Note that the \G tag ends the statement just like a semicolon (;). However, since the output table is wide, the \G rotates the table visually to vertical mode.

Drop Triggers in SQL

The deletion of a trigger is quite straightforward. You need to run a simple query to delete a trigger from the database.

So in our case, as per the example above, the query will be

Query 5:

This will erase the trigger from the database.

Advantages of Triggers in SQL

• Using triggers, you can eliminate the use of schedulers mostly. Schedulers are applications that keep running in the background and help run a task at a specific time. These timely checks can be removed and instead checked immediately whenever a particular data changes using triggers. It is basically a better replacement for schedulers.
• These are useful in having additional security checks. There are many critical components in business software, and some of them might risk highly sensitive data if not taken care of properly. Many developers work on a critical component, so some security checks must be done for the data being inserted, updated, or deleted. Here triggers help in maintaining a healthy system by warning about security issues if they exist.
• One can eliminate the risk of inserting invalid data into a column by having a check for that column or a simple update. Let’s say in the student_name table (where first_name and middle_name are both nullable, i.e., both the columns can accept null values), someone has inserted a row with middle_name keeping the first_name as null. Here the insertion will be successful, but the data is invalid. We can log the error, or also we can move the middle_name value to first_name.
• These are useful in cleaning activities since we can run some cleanup procedures on listening to deletion events. For example, there are two tables, students and parents. Ideally, if we deleted some students' data from the student's table, the corresponding data from parents (entries of those parents whose children are getting removed) also should get deleted. Since we are prone to such mistakes where we forget to delete data from parent's tables deleting from students, we can handle this very easily using triggers. We can have a trigger on students deletion, wherein on each DELETE operation, we may proceed to remove the corresponding entry from the parents' table.

Disadvantages of Triggers in SQL

• These are difficult to troubleshoot because they are run automatically, and improper logging will result in unknown updates, which could be very time-consuming to debug in large-scale projects.
• Triggers increase the overhead of database DML queries. Since for every row we insert, update or delete, it may either do an update or an insertion – say into a new table, or even a row deletion altogether further that might not be required at the moment.
• Only limited validation can be done at triggers like Not Null checks, Equality checks, Unique checks, etc. Though we can use triggers to handle most of our business use cases, it doesn’t give a full proof solution for every case. Since SQL is a data manipulation language, we cannot expect it to replace programming language dependency for validations. We still need to depend on other validation along with this. Also, in some scenarios, data validation on SQL is costly compared to validation on the service side.