What is trigger in DBMS and its types with syntax and example

In database management systems (DBMS), a trigger is a set of SQL statements that automatically execute in response to a specific event or action in a database. Triggers are useful for enforcing business rules, performing audit trails, and maintaining data integrity. Triggers can be defined on different levels such as table-level, database-level, and server-level.

In this article, we will discuss triggers in DBMS and their types, syntax, and examples.

Syntax of Triggers in DBMS:

The basic syntax of a trigger in DBMS is as follows:

CREATE TRIGGER trigger_name    BEFORE/AFTER INSERT/UPDATE/DELETE    ON table_name    FOR EACH ROWBEGIN    -- SQL statementsEND;

The CREATE TRIGGER statement is used to create a new trigger, and it requires a name for the trigger, the event that triggers the trigger, and the table on which the trigger operates.

The BEFORE/AFTER clause specifies whether the trigger should execute before or after the event, such as INSERT, UPDATE, or DELETE.

The FOR EACH ROW clause indicates that the trigger should execute for each row affected by the event.

The BEGIN and END statements define the block of SQL statements that should be executed when the trigger is triggered.

Types of Triggers in DBMS:

There are two types of triggers in DBMS:

Row-Level Triggers:

Row-level triggers execute for each row affected by the event. These triggers can be defined for INSERT, UPDATE, or DELETE events.

Example of Row-Level Trigger:

CREATE TRIGGER sales_history_trigger    AFTER INSERT    ON sales_history    FOR EACH ROWBEGIN    INSERT INTO sales_audit (        salesperson_id,        sale_date,        item_sold,        quantity_sold,        sale_amount    ) VALUES (        NEW.salesperson_id,        NEW.sale_date,        NEW.item_sold,        NEW.quantity_sold,        NEW.sale_amount    );END;

This trigger is defined to insert data into the sales_audit table whenever a new row is inserted into the sales_history table. The NEW keyword refers to the new row that is being inserted into the sales_history table.

Statement-Level Triggers:

Statement-level triggers execute once for each event, regardless of the number of rows affected by the event. These triggers can be defined for INSERT, UPDATE, or DELETE events.

Example of Statement-Level Trigger:

CREATE TRIGGER sales_total_triggerAFTER INSERT, UPDATE, DELETE ON sales_historyBEGIN    UPDATE sales_totals    SET total_sales = (SELECT SUM(sale_amount) FROM sales_history);END;

This trigger is defined to update the total_sales column in the sales_totals table whenever a row is inserted, updated, or deleted from the sales_history table. The trigger uses a subquery to calculate the sum of the sale_amount column in the sales_history table.

Types of timing for triggers:

In addition to row-level and statement-level triggers, DBMS also provides two types of timing for triggers:

BEFORE Triggers:

BEFORE triggers execute before the event is processed, and they can be used to modify the data being inserted, updated, or deleted.

Example of BEFORE Trigger:

CREATE TRIGGER sales_history_triggerBEFORE INSERT ON sales_historyFOR EACH ROWBEGIN    SET NEW.sale_amount = NEW.quantity_sold * NEW.unit_price;END;

This trigger is defined to set the value of the sale_amount column in the sales_history table to the product of the quantity_sold and unit_price columns for each new row being inserted into the sales_history table.

AFTER Triggers:

AFTER triggers execute after the event is processed, and they can be used to perform additional actions on the data.

Example of AFTER Trigger:

CREATE TRIGGER sales_history_triggerAFTER DELETE ON sales_historyFOR EACH ROWBEGIN    INSERT INTO deleted_sales (salesperson_id, sale_date, item_sold, quantity_sold, sale_amount)    VALUES (OLD.salesperson_id, OLD.sale_date, OLD.item_sold, OLD.quantity_sold, OLD.sale_amount);END;

This trigger is defined to insert data into the deleted_sales table whenever a row is deleted from the sales_history table. The OLD keyword refers to the row that is being deleted from the sales_history table.

The trigger will insert the values of the salesperson_id, sale_date, item_sold, quantity_sold, and sale_amount columns of the deleted row into the deleted_sales table. This can be useful for keeping track of deleted data for audit purposes or for undoing accidental deletions.

Conclusion

In conclusion, triggers in DBMS are a powerful tool for enforcing business rules, maintaining data integrity, and performing audit trails. There are two types of triggers in DBMS, row-level and statement-level triggers, and two timing options, BEFORE and AFTER triggers. These triggers can be used to execute SQL statements automatically in response to specific events or actions in a database. By defining triggers, we can automate various database tasks and ensure data consistency, thus making database management more efficient and reliable.