SQL Trigger Example | Triggers in SQL Tutorial

Posted on: August 25, 2020 by Amilcar Paco

SQL Trigger

Special procedures are activated when an insertion, update or deletion occurs in a table or view.

SQL Trigger differs from stored procedures in that they are not called directly by the user: when a certain event occurs in the table, they are executed.

Benefits:

Maintain data integrity: update associated tables.
Creates system logs: at each inclusion.
Notifications of changes to the database to users.
Validation of more complex integrity constraints than those directly supported by the DBMS.

Trigger Syntax

CREATE
[DEFINER = {user | CURRENT_USER}
TRIGGER name_trigger time_trigger event_trigger
ON table_name FOR EACH ROW
BEGIN
    declaration_trigger
END

Trigger – Example 1

Trigger updates the VALUE field of the SALES table every time the items table is changed.

delimiter //
CREATE TRIGGER InsItem AFTER INSERT ON Items
FOR EACH ROW
BEGIN
    UPDATE Sales set value = value + New.ValueTotal
     WHERE Order = New.Order;
END //

The NEW alias indicates the record being inserted.

Trigger – Example 2

Car rental system – Update of mileage driven by a car after its return.

DELIMITER //
CREATE TRIGGER tr_kmtraveled after updating reservations
FOR EACH LINE
UPDATE cars SET mileage = mileage + NEW.kilometerstraveled WHERE NEW.backup_car= Cars.id

Trigger – Example 3

Deleting an item from the table.

delimiter //
CREATE TRIGGER DelItem AFTER DELETE ON Items
BEGIN
    UPDATE Sales set value = value - OLD.ValueTotal
     WHERE Order = OLD.Order;
END //

The OLD alias indicates the record being deleted.

Trigger – Example 4

Data update trigger.

CREATE TRIGGER ItemUpdate AFTER UPDATE ON Items
BEGIN
    IF (New.TotalValue <> OLD.TotalValue) THEN
       UPDATE Sales set value = value - OLD.TotalValue+ New.TotalValue WHERE Order = OLD.Order;
END //