Triggers in SQL: Syntax, Operations, Advantages, & More

According to data, SQL is the most popular database language with almost 74% of developers using it. SQL is used in a variety of industries ranging from banking, healthcare, finance, technology, etc. This huge demand for SQL makes the job of SQL developers more lucrative, where an SQL developer earns an average of $115000 per annum. The demand for SQL is high because of its ease of use and its versatility. One such feature of SQL is triggers which help in the development process. Let’s have an in-depth look at what are triggers in SQL.

What is a Trigger?

Triggers in SQL servers are special procedures that execute automatically when a specified event occurs. These events can be any database operation such as insert, update, and delete. Triggers can also be used to enforce data integrity rules or perform calculations on data before it is saved in the database. You can learn more about triggers in SQL by pursuing a SQL course.

Trigger Syntax in SQL and Example

Let’s have a look at the trigger syntax in SQL:

CREATE TRIGGER Trigger_Name
[BEFORE | AFTER] [INSERT | UPDATE | DELETE]
ON Table_Name
[FOR EACH ROW | FOR EACH STATEMENT]
[trigger_body]

Now let’s understand each part of the syntax in separate parts.

• Create trigger – This is used to declare that a trigger block is going to be declared.
• Trigger name – This is unique and cannot be repeated.
• Before/After – This tells us when the trigger will be executed.
• Insert/Update/Delete – They are DML operations and we can use them in a trigger.
• On table name – This helps to specify the table name where the trigger is going to be applied.
• For each row/ column – A row-level trigger is activated either before or after a row’s column values change. The selected column changes either before or after the Column Level Trigger is invoked.
• Trigger body – It consists of logic and action that is executed when a trigger is called.

Let’s have a look at an SQL Trigger example.

CREATE TRIGGER UpdateTotalAmount
AFTER INSERT, UPDATE
ON Orders
FOR EACH ROW
BEGIN
    UPDATE Customers
    SET TotalAmount = TotalAmount + NEW.Amount - OLD.Amount
    WHERE Customers.CustomerID = NEW.CustomerID;
END;

Also Read: Schema in SQL

Operations in Triggers

There are multiple operations that can be performed using triggers. Let’s have a look at some of them:

1. Drop a Trigger:

DROP TRIGGER trigger name;

2. Display a Trigger:

This will display all the codes that are present.

SHOW TRIGGERS;

Now let’s understand with an example how before insert and after insert work. Let’s create a table with several columns

CREATE TABLE Student(
studentID INT NOT NULL AUTO_INCREMENT,
FName VARCHAR(20),
LName VARCHAR(20),
Address VARCHAR(30),
City VARCHAR(15),
Marks INT,
PRIMARY KEY(studentID)
);

We will get this table after executing the query above:

Student ID	FName	LName	Address	City	Marks
INT	varchar(20)	varchar(20)	varchar(30)	varchar(15)	INT

Now let’s use “before insert” in the table

CREATE TRIGGER before_insert
BEFORE INSERT
ON students
FOR EACH ROW
BEGIN
  -- Set the default value for the `grade` column.
  SET NEW.grade = 0;
END;

Before any new rows are added to the student’s table, this trigger will be activated. The grade column’s default value is set to 0 in the trigger body’s first statement. In other words, any new student will be given a grade of 0 up until their grade is manually changed.

Now let’s use the “after insert” trigger.

CREATE TRIGGER after_insert
AFTER INSERT
ON students
FOR EACH ROW
BEGIN
  -- Update the `last_login` column to the current timestamp.
  SET NEW.last_login = CURRENT_TIMESTAMP();
END;

Any time a new row is added to the “students” table, this trigger will be activated. The trigger body’s initial statement changes the last_login column’s value to the current timestamp. As a result, even if the student doesn’t log in to the system after they are originally created, the last_login column will always reflect the most recent information.

Advantages of Triggers

Some advantages of using triggers are:

1. Saves time – Triggers can save developers a lot of time as they automate long-running tasks which would otherwise have to be manually coded into an application.
2. Makes development faster – This makes development faster and easier for developers, allowing them to focus on other important aspects of the project.
3. Data consistency – This ensures that all data entered in the database is valid and consistent with established standards, making it easier to access and manipulate later on.
4. Customizable – triggers are customizable when it comes to logging all changes made to certain tables or creating an audit trail for auditing purposes.
5. Alternative to scheduled tasks – Triggers are automatically called before or after changes are made to data in a table, so we don’t have to wait for the planned events to complete.

Disadvantages of Triggers

Some disadvantages of triggers are as follows:

1. Debug – Triggers can be difficult to debug since the code is executed automatically without any user input. 
2. Detecting errors – As there is no control over when and how the trigger will be executed, finding and solving errors is difficult. 
3. Multiple triggers – Triggers can sometimes conflict with each other if multiple triggers are set up on the same table which can lead to unexpected results that are difficult to troubleshoot.
4. Extra resources – The database server needs additional resources in order for the trigger code to be executed, thus increasing the load on the system and potentially leading to slower performance.

Conclusion

Triggers in SQL can be a powerful tool for developers when used correctly. They provide the ability to automate complex business rules and tasks which would otherwise have to be manually coded into an application program.

FAQs