MySQL Triggers – Theory & Lab.
Exercise 8
Source: [Link]
In MySQL, 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. For example, you can define a trigger that is invoked
automatically before a new row is inserted into a 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.
There are 6 different types of triggers scenarios in MySQL:
1. Before Update Trigger 2. After Update Trigger
3. Before Insert Trigger 4. After Insert Trigger
5. Before Delete Trigger 6. After Delete Trigger
1
SET SQL_SAFE_UPDATES = 0;
-- 1. Before Update Trigger
CREATE TABLE customer (
acc_no INTEGER PRIMARY KEY,
cust_name VARCHAR(20),
avail_balance DECIMAL
);
CREATE TABLE mini_statement (
acc_no INTEGER,
avail_balance DECIMAL,
FOREIGN KEY(acc_no) REFERENCES customer(acc_no) ON DELETE CASCADE
);
INSERT INTO customer VALUES (1000, 'Fanny', 7000);
INSERT INTO customer VALUES (1001, 'Peter', 12000);
DELIMITER //
CREATE TRIGGER update_cus
BEFORE UPDATE ON customer
FOR EACH ROW
BEGIN
INSERT INTO mini_statement VALUES (OLD.acc_no, OLD.avail_balance);
END; //
DELIMITER ;
UPDATE customer SET avail_balance = avail_balance + 3000 WHERE acc_no =
1001;
UPDATE customer SET avail_balance = avail_balance + 3000 WHERE acc_no =
1000;
Output:
select *from mini_statement;
+--------+---------------+
| acc_no | avail_balance |
+--------+---------------+
| 1001 | 12000 |
| 1000 | 7000 |
+--------+---------------+
2 rows in set (0.0007 sec)
======================================================================
2
-- 2. After Update Trigger
CREATE TABLE customer2 (
acc_no INTEGER PRIMARY KEY,
cust_name VARCHAR(20),
avail_balance DECIMAL
);
CREATE TABLE micro_statement (
acc_no INTEGER,
avail_balance DECIMAL,
FOREIGN KEY(acc_no) REFERENCES customer2(acc_no) ON DELETE CASCADE
);
INSERT INTO customer2 VALUES (1002, 'Janitor', 4500);
DELIMITER //
CREATE TRIGGER update_after
AFTER UPDATE ON customer2
FOR EACH ROW
BEGIN
INSERT INTO micro_statement VALUES(NEW.acc_no, NEW.avail_balance);
END; //
DELIMITER ;
UPDATE customer2 SET avail_balance = avail_balance + 1500 WHERE acc_no =
1002;
Output:
select *from micro_statement;
+--------+---------------+
| acc_no | avail_balance |
+--------+---------------+
| 1002 | 6000 |
+--------+---------------+
1 row in set (0.0007 sec)
3
-- 3. Before Insert Trigger
CREATE TABLE contacts1 (
contact_id INT(11) NOT NULL AUTO_INCREMENT,
last_name VARCHAR(30) NOT NULL,
first_name VARCHAR(25),
birthday DATE,
created_date DATE,
created_by VARCHAR(30),
CONSTRAINT contacts1_pk PRIMARY KEY (contact_id)
);
DELIMITER //
CREATE TRIGGER contacts1_before_insert
BEFORE INSERT ON contacts1
FOR EACH ROW
BEGIN
DECLARE vUser VARCHAR(50);
-- Find username of person performing INSERT into table
SELECT USER() INTO vUser;
-- Update created_date field to current system date
SET NEW.created_date = SYSDATE();
-- Update created_by field to the username of the person performing the
INSERT
SET NEW.created_by = vUser;
END; //
DELIMITER ;
INSERT INTO contacts1 (last_name, first_name, birthday)
VALUES ('Newton', 'Enigma', STR_TO_DATE('19-08-1999', '%d-%m-%Y'));
Output:
select *from contacts1;
+------------+-----------+------------+------------+--------------+----------------+
| contact_id | last_name | first_name | birthday | created_date | created_by |
+------------+-----------+------------+------------+--------------+----------------+
| 1 | Newton | Enigma | 1999-08-19 | 2019-05-11 | root@localhost |
+------------+-----------+------------+------------+--------------+----------------+
4
-- 4. After Insert Trigger
CREATE TABLE contacts2 (
contact_id INT(11) NOT NULL AUTO_INCREMENT,
last_name VARCHAR(30) NOT NULL,
first_name VARCHAR(25),
birthday DATE,
CONSTRAINT contacts2_pk PRIMARY KEY (contact_id)
);
CREATE TABLE contacts2_audit (
contact_id INTEGER,
created_date DATE,
created_by VARCHAR(30)
);
DELIMITER //
CREATE TRIGGER contacts2_after_insert
AFTER INSERT ON contacts2
FOR EACH ROW
BEGIN
DECLARE vUser VARCHAR(50);
-- Find username of person performing the INSERT into table
SELECT USER() INTO vUser;
-- Insert record into audit table
INSERT INTO contacts2_audit
(contact_id,
created_date,
created_by)
VALUES
(NEW.contact_id,
SYSDATE(),
vUser);
END; //
DELIMITER ;
INSERT INTO contacts2 (last_name, first_name, birthday)
VALUES ('Kumar', 'Rupesh', STR_TO_DATE('20-06-1999', '%d-%m-%Y'));
Output:
select *from contacts2_audit;
+------------+--------------+----------------+
| contact_id | created_date | created_by |
+------------+--------------+----------------+
| 1 | 2019-05-11 | root@localhost |
+------------+--------------+----------------+
1 row in set (0.0006 sec)
5
-- 5. Before Delete Trigger
CREATE TABLE contacts3 (
contact_id INT(11) NOT NULL AUTO_INCREMENT,
last_name VARCHAR(30) NOT NULL,
first_name VARCHAR(25),
birthday DATE,
created_date DATE,
created_by VARCHAR(30),
CONSTRAINT contacts3_pk PRIMARY KEY (contact_id)
);
CREATE TABLE contacts3_audit (
contact_id INTEGER,
deleted_date DATE,
deleted_by VARCHAR(20)
);
DELIMITER //
CREATE TRIGGER contacts3_before_delete
BEFORE DELETE ON contacts3
FOR EACH ROW
BEGIN
DECLARE vUser VARCHAR(50);
-- Find username of person performing the DELETE from table
SELECT USER() INTO vUser;
-- Insert record into audit table
INSERT INTO contacts3_audit
(contact_id,
deleted_date,
deleted_by)
VALUES
(OLD.contact_id,
SYSDATE(),
vUser);
END; //
DELIMITER ;
INSERT INTO contacts3 (last_name, first_name, birthday, created_date,
created_by)
VALUES ('Bond', 'Ruskin', STR_TO_DATE('19-08-1995', '%d-%m-%Y'),
STR_TO_DATE('27-04-2018', '%d-%m-%Y'), 'xyz');
DELETE FROM contacts3 WHERE last_name = 'Bond';
Output:
select *from contacts3_audit;
+------------+--------------+----------------+
| contact_id | deleted_date | deleted_by |
+------------+--------------+----------------+
| 1 | 2019-05-11 | root@localhost |
6
-- 6. After Delete Trigger
CREATE TABLE contacts4 (
contact_id INT(11) NOT NULL AUTO_INCREMENT,
last_name VARCHAR(30) NOT NULL,
first_name VARCHAR(25),
birthday DATE,
created_date DATE,
created_by VARCHAR(30),
CONSTRAINT contacts4_pk PRIMARY KEY (contact_id)
);
CREATE TABLE contacts4_audit (
contact_id INTEGER,
deleted_date DATE,
deleted_by VARCHAR(20)
);
DELIMITER //
CREATE TRIGGER contacts4_after_delete
AFTER DELETE ON contacts4
FOR EACH ROW
BEGIN
DECLARE vUser VARCHAR(50);
-- Find username of person performing the DELETE from table
SELECT USER() INTO vUser;
-- Insert record into audit table
INSERT INTO contacts4_audit
(contact_id,
deleted_date,
deleted_by)
VALUES
(OLD.contact_id,
SYSDATE(),
vUser);
END; //
DELIMITER ;
INSERT INTO contacts4 (last_name, first_name, birthday, created_date,
created_by)
VALUES ('Newton', 'Isaac', STR_TO_DATE('19-08-1985', '%d-%m-%Y'),
STR_TO_DATE('23-07-2018', '%d-%m-%Y'), 'xyz');
DELETE FROM contacts4 WHERE first_name = 'Isaac';
Output:
select *from contacts4_audit;
+------------+--------------+----------------+
| contact_id | deleted_date | deleted_by |
+------------+--------------+----------------+
| 1 | 2019-05-11 | root@localhost |