TRIGGERS IN SQL
What is a Trigger?
A trigger is similar to a stored procedure that can invoke automatically whenever a special event occurs in the database.
It is executed whenever there is any change or modification in the database
It will execute if any of the operations are performed in the database.
DML — Data Manipulation Language (INSERT | UPDATE | DELETE)
DDL — Data Definition Language (CREATE | ALTER | DROP)
LogOn — It is executed in order to occur of LogOn Event
However, triggers and procedures differ in the way they invoked
What is the difference between a Stored Procedure and a Trigger
Types of Triggers:
In General, there are two types of triggers
Before and After Triggers:
Triggers on INSERT, UPDATE, and DELETE Statements are as follows:
CREATE TRIGGER [trigger_name]
[trigger_time] (BEFORE | AFTER)
[trigger_event] (INSERT | UPDATE | DELETE)
FOR each row
— variable declarations
— trigger code
For example, the below statement showcases the creation of a trigger function and a PostgreSQL trigger.
The following statement create a new table called Passenger:
The first step is create a new function called passenger_prest()
This function inserts the new charges into the passenger table including name, id, and address. Whenever new charges are added to the passenger table, it reduces the cost by 10.
Second, bind the trigger function to the passenger table. The trigger name is passenger_prest. Before the value of the charges column is inserted, the trigger function is automatically invoked to passenger_prest.
Third, insert some rows into the passenger table
Fourth, examine the content of the passenger table
In this tutorial, we have learned how to use the PostgreSQL CREATE TRIGGER to create a new trigger. Similarly, Triggers can be set up for AFTER INSERT or BEFORE/AFTER UPDATE or BEFORE/AFTER DELETE.