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:

Syntax:

Triggers on INSERT, UPDATE, and DELETE Statements are as follows:

CREATE TRIGGER [trigger_name]

[trigger_time] (BEFORE | AFTER)

[trigger_event] (INSERT | UPDATE | DELETE)

ON [table_name]

FOR each row

BEGIN

 — variable declarations

 — trigger code

END

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.