Trigger

● A trigger is a stored program invoked automatically

in response to an event such as insert, update, or
delete that occurs in the associated table.

The SQL standard defines two types of triggers: row-level

triggers and statement-level triggers.

● A row-level trigger is activated for each row that is

inserted, updated, or deleted. For example, if a table
has 100 rows inserted, updated, or deleted, the
trigger is automatically invoked 100 times for the
100 rows affected.
● A statement-level trigger is executed once for each
transaction regardless of how many rows are
inserted, updated, or deleted.
● MySQL supports only row-level triggers. It doesn’t
support statement-level triggers.
 Trigger
Advantages of triggers Disadvantages of triggers
● Triggers provide another way to check the
● Triggers can only provide extended
integrity of data.
validations, not all validations. For simple
● Triggers handle errors from the database
validations, you can use the NOT NULL,
layer.
UNIQUE, CHECK and FOREIGN KEY
● Triggers give an alternative way to run
constraints.
scheduled tasks. By using triggers, you don’t
● Triggers can be difficult to troubleshoot
have to wait for the scheduled events to run
because they execute automatically in
because the triggers are invoked
the database, which may not be visible to
automatically before or after a change is
the client applications.
made to the data in a table.
● Triggers may increase the overhead of
● Triggers can be useful for auditing the data
the MySQL server.
changes in tables.
 Trigger ● trigger_name: Name of
the trigger.
● BEFORE or AFTER: Specifies
CREATE TRIGGER trigger_name when the trigger should be
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} executed.
● INSERT, UPDATE, or
ON table_name DELETE: Specifies the type
FOR EACH ROW of operation that activates
the trigger.
BEGIN ● table_name: Name of the
-- Trigger body (SQL statements) table on which the trigger is
defined.
END; ● FOR EACH ROW: Indicates
that the trigger should be
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name; executed once for each row
affected by the triggering
event.
● BEGIN and END: Delimit the
trigger body, where you
define the SQL statements
to be executed.
 Trigger example
(1) CREATE TABLE items (
id INT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price DECIMAL(10, 2) NOT NULL
);

(2) INSERT INTO items(id, name, price)

VALUES (1, 'Item', 50.00);

(3) CREATE TABLE item_changes (

change_id INT PRIMARY KEY AUTO_INCREMENT,
item_id INT,
change_type VARCHAR(10),
change_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (item_id) REFERENCES items(id)
);

(4) UPDATE items

SET price = 60.00 WHERE id = 1;

(5) SELECT * FROM item_changes;

1) Create a BEFORE INSERT trigger to maintain a summary table from another table.
BEFORE INSERT triggers are automatically fired before an insert event occurs on the table.

DELIMITER $$
CREATE TRIGGER before_workcenters_insert
BEFORE INSERT
ON WorkCenters FOR EACH ROW
BEGIN
DECLARE rowcount INT;
SELECT COUNT(*)
INTO rowcount
FROM WorkCenterStats;
IF rowcount > 0 THEN
UPDATE WorkCenterStats
SET totalCapacity = totalCapacity + [Link];
ELSE
INSERT INTO WorkCenterStats(totalCapacity)
VALUES([Link]);
END IF;
END $$
DELIMITER ;
2) Create an AFTER INSERT trigger to insert data into a table after inserting data into another table.
AFTER INSERT triggers are automatically invoked after an insert event occurs on the table.

DELIMITER $$

CREATE TRIGGER after_members_insert

AFTER INSERT
ON members FOR EACH ROW
BEGIN
IF [Link] IS NULL THEN
INSERT INTO reminders(memberId, message)
VALUES([Link],CONCAT('Hi ', [Link], ', please update your date of birth.'));
END IF;
END$$
DELIMITER ;
3) Create a BEFORE UPDATE trigger that validates data before it is updated to the table.

DELIMITER $$
CREATE TRIGGER before_sales_update
BEFORE UPDATE
ON sales FOR EACH ROW
BEGIN
DECLARE errorMessage VARCHAR(255);
SET errorMessage = CONCAT('The new quantity ', [Link],
' cannot be 3 times greater than the current quantity ', [Link]);
IF [Link] > [Link] * 3 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = errorMessage;
END IF;
END $$
DELIMITER ;
4) Create an AFTER UPDATE trigger to log the changes of data in a table.
AFTER UPDATE triggers are invoked automatically after an update event occurs on the table associated with the triggers.
DELIMITER $$
CREATE TRIGGER after_sales_update
AFTER UPDATE
ON sales FOR EACH ROW
BEGIN
IF [Link] <> [Link] THEN
INSERT INTO SalesChanges(salesId,beforeQuantity, afterQuantity)
VALUES([Link], [Link], [Link]);
END IF;
END$$
DELIMITER ;
5) Create a BEFORE DELETE trigger to add deleted rows into an archive table.
BEFORE DELETE triggers are fired automatically before a delete event occurs in a table.
DELIMITER $$
CREATE TRIGGER before_salaries_delete
BEFORE DELETE
ON salaries FOR EACH ROW
BEGIN
INSERT INTO
SalaryArchives(employeeNumber,validFrom,amount)
VALUES([Link],[Link],[Link] );
END$$
DELIMITER ;
6) Create a AFTER DELETE trigger to maintain a summary table of another table.
AFTER DELETE triggers are automatically invoked after a delete event occurs on the table.

CREATE TRIGGER after_salaries_delete

AFTER DELETE
ON Salaries FOR EACH ROW
UPDATE SalaryBudgets
SET total = total - [Link];
 7) Create Multiple Triggers
MySQL 5.7.2+ allowed you to create multiple triggers for a given table that have the same event and action time.
These triggers will activate sequentially when an event occurs.

● The FOLLOWS allows the new trigger to activate

after an existing trigger.
● The PRECEDES allows the new trigger to activate
before an existing trigger.
Use the products table in the sample database (classicmodels).
Suppose that you want to change the price of a product (column MSRP ) and log the old price in a separate table
named PriceLogs

Update the price of a product

Then, query data from both
PriceLogs and UserChangeLogs tables
And, show the results of PriceLogs,
UseChangeLogs tables.