Triggers in SQL Tutorial | SQL Trigger Examples & Advantages

In this tutorial, we will learn about the triggers in SQL. Let us now dive deep into the triggers world.

What are SQL Triggers?

Triggers are programs that are available in the memory, with unique names made up of SQL queries which we need to fire on our database on and off.

Triggers can be made to insert, update and delete statements in SQL. We have two types of triggers:

1. Row Level Triggers

In a row-level trigger, the changes are put on all the rows on which the insert, delete, or update transaction is performed.

If we have 1000 rows in a database and a delete operation is being run on them, then the trigger would also run 1000 times automatically. It is accessible in MySQL.

2. Statement Level Triggers

In the statement-level triggers, the operation is under execution only once no matter how many rows are involved in the operation. These triggers are not accessible by MySQL.

Why do we need Triggers in SQL?

We have seen what are triggers, now let us try to understand the need for triggers in SQL. Some of the aspects of using triggers in SQL database transactions are as follows:

1. Allows the reuse of code.

2. Helps us increase the computational costs by reducing result times.

Syntax:

CREATE TRIGGER triggerName 
BEFORE/AFTER 
INSERT/UPDATE/DELETE
ON tableName
FOR EACH ROW SET operation;

We have six different variations of triggers.

1. BEFORE INSERT: In these triggers, the operation on rows is under execution before any INSERT operation on the database.

2. AFTER INSERT: In these triggers, the operation on rows is under execution after any INSERT operation on the database.

3. BEFORE UPDATE: In these triggers, the operation on rows is under execution before an UPDATE operation on the database.

4. AFTER UPDATE: In these triggers, the operation on rows is under execution after an UPDATE operation on the database.

5. BEFORE DELETE: In these triggers, the operation on rows is under execution before any DELETE operation is run on the database.

6. AFTER DELETE: In these triggers, the operation on rows is under execution after any DELETE operation on the database.

Let us now look at how we use triggers with the help of an example.

Example: Let us create a trigger on our table DataFlair whenever an update operation is run on our database.
Let us first initially view the contents of the DataFlair database.
Query:

Use DataFlair;
SELECT * FROM Dataflair;

Output:

Syntax:

Use DataFlair;
-- Trigger being created
CREATE TRIGGER increase_experience AFTER UPDATE ON DataFlair
FOR EACH ROW SET @experience = @experience + 1;
-- Trigger put to use
UPDATE DataFlair
SET experience = 1
WHERE experience = 0;
-- View the result of Trigger
SELECT * FROM DataFlair;

Output:

Advantages of Triggers in SQL

Some of the prominent advantages of triggers are as follows:

1. Helps us to automate the data alterations.

2. Allows us to reuse the queries once written.

3. Provides a method to check the data integrity of the database.

4. Helps us to detect errors on the database level.

5. Allows easy auditing of data.

Disadvantages of Triggers

Some of the disadvantages of triggers in SQL are as follows:

1. Increases the overhead costs of the server.

2. Provides only extended validations i.e. not all validations are accessible in SQL triggers.

3. Troubleshooting errors due to triggers is a tedious job.

4. Can cause logical errors in the application even if a slight mistake in query exists.

5. We could lose the original data if we set a wrong trigger by mistake.

Summary

In this tutorial, we have seen in-depth all about triggers in SQL. We have seen we have two major triggers out of which one is functional in SQL.

Further, we have seen the six major types of triggers which we can fire and use in SQL.

Triggers are programs in the memory with unique names and help us to reuse the queries once written and kept safe in the memory.

Moving on we have seen an example of how to create and use a trigger and then how we can view the result of a trigger. At last, we have seen the advantages and disadvantages of triggers in SQL.