What are Triggers in SQL? Need, Definition, Types

Triggers in SQL are the programs that are executed automatically when a certain condition is met. The trigger can even be used to maintain the integrity of data. The triggers are created and stored in the database and then it is the responsibility of the database system to get the trigger executed whenever a certain condition is met.

In this section ahead we will discuss the need for triggers, how to create triggers, kinds of triggers in SQL, how you can replace and remove a trigger from the database system.

Need of Trigger

The triggers are used to implement integrity constraints i.e. the set of rules that maintain the integrity of data in the database. Implementing triggers perform the specified task automatically whenever certain conditions are met. This also reduces the need for human monitoring over the database.

Consider one scenario, that whenever a new employee joins the enterprise and the employee’s data is fed into the database. The company manager sends a welcome mail to that employee. This process will work fine if the recruitments are not made frequently and the number of recruited employees is manageable.

If the recruitments are made frequently then it would be a headache for the manager to check the database frequently to identify the new employee and send them a welcome mail. Instead, we can set a trigger whose action will be to send a welcome mail to the employees who has just joined the company.

What is Trigger?

Triggers are the set of PL/SQL statements or a subprogram that are created and stored in the database. It gets executed automatically at the occurrence of the specified event that causes modification in the database.

The syntax of creating a trigger is as follow:

CREATE [OR REPLACE] TRIGGER <trigger name>

[BEFORE/AFTER/INSTEAD OF]

[INSERT/UPDATE/DELETE [of column, ..]] ON <table name>

[REFERENCING [OLD [AS] <old name> | NEW [AS]

<new name>]

[FOR EACH STATEMENT/FOR EACH ROW]

[WHEN <condition>]

[BEGIN –PL/SQL block

END];

The CREATE OR REPLACE TRIGGER keywords informs that a trigger is being declared. BEFORE, AFTER or INSTEAD OF keyword declare whether the trigger must be executed before or after the occurrence of the trigger event. This is followed by the trigger event that can be declared with the keywords INSERT, UPDATE, DELETE etc. Next, the table name is specified to which the trigger is associated. Referencing clause is used to refer to the column values where old and new column values can be indicated using an old and new keyword.

FOR EACH ROW or FOR STATEMENT keyword specify that the trigger must be fired once of each row affected or only once even if no row is affected respectively. The WHEN clause specifies the condition that be satisfied for a trigger to be fired. The statement between BEGIN and END specifies the action to be taken when the trigger is fired and the condition is satisfied.

A trigger can be designed into three parts:

Defining Events
Defining a Condition
Defining Action

Defining Events

A triggering event can be defined as an SQL statement which can either be a database event or a user event such as update, delete, insert etc. that invokes the trigger. While defining the event you must specify the table associated with the trigger. The triggering event can be any of the following:

Data manipulation commands such as INSERT, UPDATE or DELETE operation on a specific table or view.
Data definition commands such as CREATE, ALTER or DROP on any schema object.
Activities such as startup and shut down of database.
User activities including logging in and logging out.
Error messages etc.

The figure below will show you the three triggers that are created and stored in the database and are associated with the SQL statement UPDATE, INSERT and DELETE.
So, whenever any of these data manipulation commands are executed the corresponding trigger gets executed automatically.

Defining a Condition

A triggering condition can be defined as a logical expression that can either result in TRUE or FALSE or UNKNOWN. For the trigger to be fired the result of the triggering expression must be TRUE. The declaration of triggering condition is preceded by the keyword WHEN.

Defining Action

Triggered action is defined using the set of SQL statements that specifies what action has to be taken when the triggering statement is issued and the triggering condition results in TRUE.

Types of Triggers

On the basis of when the trigger has been fired, the level at which the trigger has been fired and the event at which the trigger has been fired, we can classify the trigger as follow.

Types of Events
1. Trigger on System Event
2. Trigger on User Event
Level at Which Trigger is Executed
1. Row Level Trigger
2. Statement Level Trigger
Time at Which Trigger is Fired
1. BEFORE Trigger
2. AFTER Trigger
3. INSTEAD OF Trigger

1. Types of Events

a. Triggers on System Event

System Event triggers are the ones that are fired at the occurrence of system events such as startup of the system, shut down and error message.

b. Trigger on User Event

User event triggers are the ones that are fired at the occurrence of any user event such as INSERT, UPDATE and DELETE.

2. Level at Which Trigger is Executed

a. Row Level Trigger

A row-level trigger is a trigger that is fired once for each row in the table that will be affected by the triggering SQL Statement. If the triggering SQL statement does not affect any row in the table, the row-level trigger is not executed at all.

To specify the row-level trigger FOR EACH ROW clause is specified after the table name associated with the trigger. In the row-level trigger, the SQL statement has to access the column values of the row that is currently being processed.

b. Statement Level Trigger

Statement level trigger is fired only once no matter how many rows are being affected by the SQL statement. Even if the no rows are being affected by the triggering SQL statement.

To specify the statement level trigger FOR EACH Statement clause is specified after the table name in the definition of the trigger.

3. Type of Trigger

a. BEFORE Trigger

With the BEFORE trigger the defined trigger action is executed before the triggering statement gets executed. It derives column values of the table before the triggering SQL statement (DDL or DML) gets complete or verify whether the triggering statement can be completed or not.

b. AFTER Trigger

With the AFTER trigger the defined trigger action is executed after the triggering statement is executed. We use the AFTER trigger if we want to execute some additional logic before executing the trigger action.

c. INSTEAD OF Trigger

The INSTEAD OF trigger is used to perform any action instead of the action that executes the trigger.

Replacing Triggers

You can not modify any defined trigger directly instead you must replace the trigger to specify a new definition of it. The command used to replace the old definition of trigger with new one is as follow:

CREATE OR REPLACE TRIGGER trigger_name AS/IS

<new_trigger_definition>;

You can even drop the entire trigger and create a new one if you don’t want to replace it.

Dropping Triggers

As we can drop views, procedures, tables in the database, you can even drop triggers in SQL. Though the developer must have DROP ANY TRIGGER system privilege to drop the command from the database structure. The command used to drop trigger is as follow:

DROP TRIGGER trigger_name;

Advantages of Trigger

Trigger plays an important role in maintaining the integrity of data in the database system.
Implementing triggers also helps in performing certain operations automatically.

So this is all about the triggers we have learned about its need and how it can be created. We have also learned about the basis on which triggers can be classified. Later we have learned to replace or drop the comment from the database system.