Triggers in SQL: An Overview and Example

Introduction

What is Trigger in SQL?

Tiggers in SQL are codes that are automatically executed in response to certain events on a particular table. These triggers in SQL  are used to maintain the integrity of data. It is a special procedure because it cannot be called directly like other stored procedures in SQL. For instance, a trigger in SQL is invoked when a row is added, a column is added, and updates are performed on the table.

Syntax of Trigger in SQL:

Explanation of Syntax of Trigger in SQL:

To create a trigger in SQL firstly we need to name the trigger.

• [before | after ]: indicates when the trigger has to take action before the operation or after the operation specified below.

  - Before Triggers are triggers that are executed before the triggering statement is executed.

  - After Triggers are the triggers that are executed after the triggering statement is executed.

• {Operation}: This indicates the kind of operation needed to trigger the SQL. These operations are mostly DML operations like INSERT, DELETE, and UPDATE.
• [table-name]: This indicated the database table on which the above operation has to be performed.
• [for each row]: This indicates whether the trigger has to be executed for each row. This indicates the row-level trigger.
• [Operation]: This is the body of the trigger which represents the action to be performed once the trigger has been invoked.

SQL Trigger Examples:

Employee’s database is provided and we need to find the maximum hike an employee got in the past years. Every time you insert a record of an Employee’s detail, it should find the sum of all hikes until that year and update it in the table. This can be performed using triggers in SQL. The employee’s schema is as follows:

The below-mentioned code snippet explains how a trigger in SQL has to be created for the given operation to be performed.

To invoke the trigger an insert statement has to be executed in the following way:

The output obtained from the action of the trigger is shown by printing the whole Employee table using the following query:

The output of the Employee table is :

Conclusion

Tiggers in SQL are codes that are automatically executed in response to certain events on a particular table. These triggers in SQL  are used to maintain the integrity of data. Triggers are useful while handing huge amount of data.