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.
CREATE
[DEFINER = {user | CURRENT_USER}
TRIGGER name_trigger time_trigger event_trigger
ON table_name FOR EACH ROW
BEGIN
declaration_trigger
END
SQLCopy
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 //
SQLCopy
The NEW
alias indicates the record being inserted.
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
#snippets #sql #sql trigger