Table of contents
1.
Introduction 
1.1.
What are the Triggers in SQL?
2.
Usages of Triggers 
3.
Types of Triggers 
3.1.
LEVEL TRIGGERS
3.1.1.
ROW LEVEL TRIGGERS
3.1.2.
STATEMENT LEVEL TRIGGERS
3.2.
EVENT TRIGGERS
3.2.1.
DDL EVENT TRIGGER
3.2.2.
DML EVENT TRIGGER
3.2.3.
DATABASE EVENT TRIGGER 
3.3.
TIMING TRIGGERS
3.3.1.
BEFORE TRIGGER 
3.3.2.
AFTER TRIGGER 
4.
Syntax of Creating Triggers in SQL
4.1.
MY SQL
5.
Demo Table
5.1.
MY SQL
6.
Triggering Triggers in PL/SQL
6.1.
MY SQL
6.2.
MY SQL
6.3.
MY SQL
6.4.
Disable and Enable Trigger 
6.5.
MY SQL
6.6.
MY SQL
7.
Advantages of Triggers in SQL
8.
Disadvantages of Triggers in SQL
9.
Frequently Asked Questions
9.1.
What are triggers in SQL?
9.2.
What are the 4 types of triggers in SQL?
9.3.
What is trigger and its syntax?
10.
Conclusion
Last Updated: Mar 27, 2024
Medium

Triggers in SQL Server

Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction 

In databases, a trigger is a stored procedure that is automatically invoked whenever a particular event occurs in the Database. A trigger, for example, can be invoked when a row is inserted into a specified table or when certain table columns are updated, and so forth. 

Wonder how? 

Let us get started with triggers in PL/SQL right away:

Triggers in SQL

In this article, we will discuss the overview of the triggers, how they can be implemented, and their uses in databases.

Recommended topics, Coalesce in SQL and Tcl Commands in SQL

What are the Triggers in SQL?

In Sql, triggers are database objects that allow the automatic execution of a predefined set of actions or operations when specific events occur within a database. These events generally involve changes to data, like INSERT, DELETE, or UPDATE operations. Triggers provide a method to respond to these events without requiring manual intervention. Triggers are essential in enforcing data integrity, automating tasks, and implementing complex business rules within a database system.

Events can be any of the following –

  1. Database manipulation (DML): statement like - INSERT, DELETE, or UPDATE 
     
  2. Database definition ( DDL )    : statement like - CREATE, ALTER, or DROP 
     
  3. Database operation                : statement like - LOGON, STARTUP, or SHUTDOWN
     

Unlike a stored procedure, a trigger can be enabled and disabled but not explicitly invoked. By explicitly invoked means, whenever we want to fire something after/before an event, we need to define or store the trigger beforehand in the database. 

When a trigger is enabled, the database will automatically invoke it—that is, the trigger will fire—when its triggering event occurs. When a trigger is disabled, it does not fire.

Moreover, the trigger is said to be created or defined on the item, which could be a table, a view, a schema, or the database. We can also specify the timing point, which determines whether the trigger fires before or after the triggering statement runs and whether it fires for each row that the triggering statement affects. By default, a trigger is created in the enabled state.

Usages of Triggers 

Triggers can be used for the following purposes - 

  • To gain strong Control over Security. 
     
  • Enforcing referential integrity - To put it simply, it is a concept of foreign key constraint which depicts that you cannot add value to the child table if the master table is empty. 
     
  • Event logging and storing information on table access. 
     
  • Auditing. 
     
  • Synchronous replication of tables. 
     
  • Preventing invalid transactions. 
     
  • Accumulates information on table usage.
     
  • It monitors the critical information. 

Types of Triggers 

There are three types of Triggers: 

  1. Level Triggers 
  2. Event Triggers 
  3. Timing Triggers 
     

types of Triggers

Let us discuss each one of them:

LEVEL TRIGGERS

Level triggers are divided into two parts: 

ROW LEVEL TRIGGERS

  • It fires for each record that was affected by the execution of DML statements such as INSERT, UPDATE, DELETE, and so on.
  • FOR EACH ROW clause is always used in a triggering statement.

STATEMENT LEVEL TRIGGERS

  • It fires once for each executed statement.

EVENT TRIGGERS

Event triggers are divided into three parts: 

DDL EVENT TRIGGER

  • It is triggered by the execution of every DDL statement (CREATE, ALTER, DROP, TRUNCATE).

DML EVENT TRIGGER

  • It is triggered by the execution of every DML statement (INSERT, UPDATE, DELETE).

DATABASE EVENT TRIGGER 

  • It is triggered by the execution of any database operation, such as LOGON, LOGOFF, SHUTDOWN, SERVERERROR, and so on.

TIMING TRIGGERS

Timing triggers are divided into two parts: 

BEFORE TRIGGER 

  • It fires before the DML statement is executed.

AFTER TRIGGER 

  • It fires after the DML statement has been executed.

To understand the above methodology, we need to consider the syntax: 

Syntax of Creating Triggers in SQL

  • MY SQL

MY SQL

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF}
{ INSERT [OR] | UPDATE [OR] | DELETE}
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]

DECLARE
-- Declaration-statements
BEGIN
-- Executable-statements
EXCEPTION 
-- Error handling statements 
END;

Let us now understand each line written above in the syntax: 

  • CREATE [OR REPLACE] TRIGGER trigger_name 

This statement is for creating, updating or replacing a trigger having a name as trigger_name where the trigger_name is the user-defined name.

  • {BEFORE | AFTER| INSTEAD OF }

This statement specifies when the trigger will be executed. INSTEAD OF is usually used with views. 

  • { INSERT [OR] | UPDATE [OR] | DELETE}

This statement specifies on which DML operation the trigger must be fired. 

  • ON table_name

This statement specifies the table’s name associated with the trigger. 

  • [REFERENCING]

It is an optional keyword used to provide reference to old and new values for the DML statements. 

  • [FOR EACH ROW]

It specifies a row-level trigger which we've discussed above.  In this case, the trigger will be executed for each affected row. Else, the trigger will execute once when the SQL statement is executed, which is called a level trigger. 

  • DECLARE, BEGIN, EXCEPTION, END

They are the various sections of the PL/SQL code block that contain a variable declaration, executable statements, error handling statements, and marking the end of the PL/SQL block, where DECLARE and EXCEPTION are optional.

 

Let us now understand it with an example: 

Demo Table

Let us take an example of a table named EMPLOYEE, having the following attributes EID, ENAME, and SALARY. 

There are six tuples containing some values as shown below: 

EID

ENAME

SALARY 

1

Manthan 

30000

2

Harsh 

31000

3

Shivani

42000

4

Jasmine

43000

5

Bob

50000

6

Garima 

23000

 

Let us now create a row-level trigger for the EMPLOYEE table that would get executed by the DML statement like UPDATE/INSERT/DELETE. The trigger will compute and display the salary difference between the new and old salaries. 

Code Implementation:

  • MY SQL

MY SQL

CREATE TRIGGER Salary_difference
BEFORE INSERT OR DELETE OR UPDATE ON EMPLOYEE
FOR EACH ROW
DECLARE Salary_difference number;
BEGIN
Salary_difference := :new.SALARY - :old.SALARY;
dbms_output.put_line('Old Salary: ' || :old.SALARY);
dbms_output.put_line('New Salary: ' || :new.SALARY);
dbms_output.put_line('Salary difference: ' || salary_difference);
END;

 

OUTPUT :

Trigger created. 

 

Explanation: 

NEW and OLD Clause 

Here, a new clause is used to store the current new value for the attributes of the table for the trigger execution. It is usually used in row-level triggers. In contrast, an old clause is used to store the old value for the attributes of the table for the trigger execution. Like the NEW clause, it is also used in row-level triggers. 

The above code has some important following characteristics: 

  • For a table-level trigger, OLD and NEW addresses are not available. We can only use these references for row-level triggers as shown above. 
  • If we want to apply another query in the same trigger, we need to use the keyword AFTER which can modify a table again only after the previous modifications are properly implied. 
  • The trigger discussed above is getting executed prior to any INSERT, UPDATE, or DELETE action on the table. However, we can also design a trigger that gets fired on a single operation. 

Triggering Triggers in PL/SQL

To fire the above trigger, we require to perform any DML operation like INSERT, DELETE, or UPDATE:

Let's perform some operations now: 

  • Insert a new tuple with the following data:

EID - 7

ENAME - NISHANT 

SALARY - 46000

Let us write the query:

  • MY SQL

MY SQL

INSERT INTO EMPLOYEE VALUES(7, 'Nishant', 46000);

 

Once the INSERT operation is completed in the EMPLOYEE table, the trigger Salary_difference gets fired.

The Output will be as follows: 

Old salary:
New Salary: 46000
Salary Difference: 

1 row created. 


From the above output, we can conclude that the trigger is fired but the values for the Old salary and Salary difference is empty since there were no past records of this employee. 

Let us now discuss the DELETE statement: 

  • Delete the employee whose EID is 7. 
     

Let us write the query:

  • MY SQL

MY SQL

DELETE from EMPLOYEE where EID = 7;

 

The Output will be as follows: 

Old salary: 46000
New Salary: 
Salary Difference: 

1 row deleted. 

The new salary becomes the old salary now and there is no new salary available. 

            Now, let us alter a record with an UPDATE statement: 

  • Update the salary to the 60000 of the Employee whose EID is 1. 
     

Let us write the query:

  • MY SQL

MY SQL

UPDATE EMPLOYEE SET SALARY = 60000 WHERE EID = 1;

 

The Output will be as follows: 

Old salary: 30000
New Salary: 60000
Salary Difference: 30000

1 row updated.

Let us now discuss how to enable and disable a trigger: 

Disable and Enable Trigger 

By default, the trigger is enabled. However, if you want to enable/disable it manually, DDL statement ALTER can be utilised. 

Syntax:

ALTER TRIGGER trigger_name [ENABLE|DISABLE];

 

Here, trigger_name is the user-defined name of the trigger. 

  • Disable the trigger
     
  • MY SQL

MY SQL

ALTER TRIGGER Salary_difference DISABLE;

Output

Trigger altered. 

Now, let us enable the same trigger: 

  • Enable the trigger
     
  • MY SQL

MY SQL

ALTER TRIGGER Salary_difference ENABLE;

 

Output

Trigger altered. 

Advantages of Triggers in SQL

  • Triggers maintain accurate and reliable data by enforcing rules automatically.
     
  • They record changes, showing how the data has been modified over time.
     
  • They save time by performing actions automatically, reducing manual work.
     
  • Triggers help catch and handle errors, preventing data issues.
     
  • Triggers update linked data automatically when a main record changes.

Disadvantages of Triggers in SQL

  • Triggers can make the database more complicated and harder to manage.
     
  • Triggers can create interlinked dependencies that complicate changes.
     
  • Mistakes in triggers can cause unexpected outcomes.
     
  • Poorly designed triggers can slow down operations.
     
  • Determination trigger problems can be complex.

Frequently Asked Questions

What are triggers in SQL?

Triggers in SQL are database objects that automatically perform predefined actions when specific events, such as INSERT, UPDATE, or DELETE operations, occur within a database. They help to automate tasks and maintain data integrity.

What are the 4 types of triggers in SQL?

The four types of triggers in SQL are BEFORE UPDATE, AFTER UPDATE, BEFORE INSERT, or AFTER INSERT. Each type corresponds to a specific timing point during the event's execution.

What is trigger and its syntax?

A trigger is a predefined action that automatically responds to specific events in a database. Its syntax includes specifying the event, for example, AFTER INSERT, the table, and the actions to execute when the event occurs.

Conclusion

To wrap up the discussion, we’ve discussed how triggers work in PL/SQL with different queries followed by their uses in the database. It is often seen that triggers trigger the students, but nothing is difficult after having in-depth knowledge of something. 

For more information, you can check out the DBMS course taught by the top-notch faculties. 

Also check out - Strong Number

The discussion is not over yet, Ninja. Get yourself enrolled in Top-100 SQL problems designed just for you. 

Happy Learning Ninja! 

Live masterclass