ASSIGNMENT TRIGGERS

MariaDB [(none)]> create database

nividb; Query OK, 1 row affected (0.006

sec)

MariaDB [(none)]> connect nividb;

Connection id: 10

Current database: nividb

BEFORE INSERT
MariaDB [nividb]> CREATE TABLE school (

-> id INT AUTO_INCREMENT PRIMARY KEY,

-> first_name VARCHAR(50),

-> last_name VARCHAR(50),

-> submission_date DATE

-> );

Query OK, 0 rows affected (0.025

sec) MariaDB [nividb]> DELIMITER //

MariaDB [nividb]> CREATE TRIGGER before_insert_school

-> BEFORE INSERT ON school

-> FOR EACH ROW

-> BEGIN

-> IF NEW.submission_date IS NULL THEN

-> SET NEW.submission_date = CURDATE();

-> END IF;

-> END;

-> //

Query OK, 0 rows affected (0.015 sec)

MariaDB [nividb]> DELIMITER ;

MariaDB [nividb]> INSERT INTO school (first_name, last_name) VALUES ('Niranjan', 'Salim');

Query OK, 1 row affected (0.015 sec)

MariaDB [nividb]> SELECT * FROM school

-> SELECT * FROM school;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to
your MariaDB server version for the right syntax to use near 'SELECT * FROM school' at line 2

MariaDB [nividb]> SELECT * FROM school;

+ + + + +

| id | first_name | last_name | submission_date |

+ + + + +

| 1 | Niranjan | Salim | 2023-10-10 |

+ + + + +

1 row in set (0.007 sec)

AFTER INSERT
MariaDB [nividb]> CREATE TABLE teachers(

-> id INT AUTO_INCREMENT,

-> name VARCHAR(100) NOT NULL,

-> email VARCHAR(255),

-> birthdate DATE,

-> PRIMARY KEY (id)

-> );

Query OK, 0 rows affected (0.022 sec)

MariaDB [nividb]> CREATE TABLE message (

-> id INT AUTO_INCREMENT,

-> teacherId INT,

-> message VARCHAR(255) NOT NULL,

-> PRIMARY KEY (id, teacherId)

 -> );

Query OK, 0 rows affected (0.017 sec)

MariaDB [nividb]> DELIMITER $$

MariaDB [nividb]> CREATE TRIGGER after_insert_teachers

-> AFTER INSERT

-> ON teachers FOR EACH ROW

-> BEGIN

-> IF NEW.birthdate IS NULL THEN

-> INSERT INTO message (teacherId, message)

-> VALUES (new.id,CONCAT('HELLO',NEW.name,',please update uyour birth date.'));

-> END IF;

-> END $$

Query OK, 0 rows affected (0.015 sec)

MariaDB [nividb]> DELIMITER ;

MariaDB [nividb]> INSERT INTO teachers(name, email, birthdate)

-> VALUES

-> ('Nivedita Krishnan','niv@gm.com', NULL),

-> ('Santhwana jubet', 'sand@gmail.com','2002-10-31');

Query OK, 2 rows affected (0.012 sec)

Records: 2 Duplicates: 0 Warnings: 0

MariaDB [nividb]> SELECT * FROM teachers;

+ + + + +

| id | name | email | birthdate |

+ + + + +

| 1 | Nivedita Krishnan | niv@gm.com | NULL |

| 2 | Santhwana jubet | sand@gmail.com | 2002-10-31 |

+ + + + +

2 rows in set (0.001 sec)

MariaDB [nividb]> select * from message;

+ + + +

| id | teacherId | message |

+ + + +

| 1| 1 | HELLONivedita Krishnan,please update uyour birth date. |

+ + + +

1 row in set (0.001 sec)

BEFORE DELETE

MariaDB [nividb]> CREATE TABLE teachersal(

-> teacher_num INT PRIMARY KEY,

-> valid_from DATE NOT NULL,

-> amount DEC(8,2) NOT NULL DEFAULT 0

-> );

Query OK, 0 rows affected (0.021 sec)

MariaDB [nividb]> INSERT INTO teachersal (teacher_num, valid_from, amount)

-> VALUES

-> (102, '2020-01-10', 45000),

-> (103, '2020-01-10', 65000),

-> (105, '2020-01-10', 55000),

-> (107, '2020-01-10', 70000),

-> (109, '2020-01-10', 40000);

Query OK, 5 rows affected (0.006

sec) Records: 5 Duplicates: 0

Warnings: 0

MariaDB [nividb]> CREATE TABLE teacherdetails (

-> id INT PRIMARY KEY AUTO_INCREMENT,

-> teacher_num INT,

-> valid_from DATE NOT NULL,

 -> amount DEC(18,2) NOT NULL DEFAULT 0,

-> deleted_time TIMESTAMP DEFAULT NOW()

-> );

Query OK, 0 rows affected (0.019 sec)

MariaDB [nividb]> DELIMITER $$

MariaDB [nividb]> CREATE TRIGGER before_delete_teachersal

-> BEFORE DELETE

-> ON teachersal FOR EACH ROW

-> BEGIN

-> INSERT INTO teacherdetails(teacher_num, valid_from, amount)

-> VALUES (OLD.teacher_num, OLD.valid_from, OLD.amount);

-> END$$

Query OK, 0 rows affected (0.013 sec)

MariaDB [nividb]> DELIMITER ;

MariaDB [nividb]> DELETE FROM teachersal WHERE teacher_num = 105;

Query OK, 1 row affected (0.015 sec)

MariaDB [nividb]> DELIMITER ;

MariaDB [nividb]> SELECT * FROM teacherdetails;

+ + + + + +

| id | teacher_num | valid_from | amount | deleted_time |

+ + + + + +

| 1| 105 | 2020-01-10 | 55000.00 | 2023-10-10 11:47:28 |

+ + + + + +

1 row in set (0.001 sec)

MariaDB [nividb]> DELETE FROM

teachersal; Query OK, 4 rows affected

(0.004 sec)
MariaDB [nividb]> SELECT * FROM teacherdetails;

+ + + + + +

| id | teacher_num | valid_from | amount | deleted_time |

+ + + + + +

| 1| 105 | 2020-01-10 | 55000.00 | 2023-10-10 11:47:28 |

| 2| 102 | 2020-01-10 | 45000.00 | 2023-10-10 11:49:29 |

| 3| 103 | 2020-01-10 | 65000.00 | 2023-10-10 11:49:29 |

| 4| 107 | 2020-01-10 | 70000.00 | 2023-10-10 11:49:29 |

| 5| 109 | 2020-01-10 | 40000.00 | 2023-10-10 11:49:29 |

+ + + + + +

5 rows in set (0.001 sec)

AFTER DELETE

MariaDB [nividb]> CREATE TABLE teachersalary(

-> teacher_num INT PRIMARY KEY,

-> salary DECIMAL(10,2) NOT NULL DEFAULT 0

-> );

Query OK, 0 rows affected (0.019 sec)

MariaDB [nividb]> INSERT INTO teachersalary (teacher_num, salary)

-> VALUES

-> (1002,5000),

-> (1056,7000),

-> (1076,8000);

Query OK, 3 rows affected (0.007

sec) Records: 3 Duplicates: 0

Warnings: 0
MariaDB [nividb]> DROP TABLE IF EXISTS

salarybudgets; Query OK, 0 rows affected, 1 warning

(0.001 sec)

MariaDB [nividb]> CREATE TABLE salarybudgets (

-> total DECIMAL(15,2) NOT NULL

-> );

Query OK, 0 rows affected (0.017 sec)

MariaDB [nividb]> INSERT INTO salarybudgets (total)

-> SELECT SUM(salary)

-> FROM teachersalary;

Query OK, 1 row affected (0.006 sec)

Records: 1 Duplicates: 0 Warnings: 0

MariaDB [nividb]> SELECT * FROM salarybudgets;

+ +

| total |

+ +

| 20000.00 |

+ +

1 row in set (0.001 sec)

MariaDB [nividb]> CREATE TRIGGER after_delete_teachersalary

-> AFTER DELETE

-> ON teachersalary FOR EACH ROW

-> UPDATE salarybudgets

-> SET total = total - OLD.salary;

Query OK, 0 rows affected (0.012 sec)

MariaDB [nividb]> DELETE FROM teachersalary

-> WHERE teacher_num = 1002;

Query OK, 1 row affected (0.010 sec)

MariaDB [nividb]> SELECT * FROM salarybudgets;

+ +

| total |

+ +

| 15000.00 |

+ +

1 row in set (0.001 sec)

MariaDB [nividb]> DELETE FROM teachersalary;

Query OK, 2 rows affected (0.007 sec)

MariaDB [nividb]> SELECT * FROM salarybudgets;

+ +

| total |

+ +

| 0.00 |

+ +

1 row in set (0.001 sec)

BEFORE UPDATE
MariaDB [nividb]> CREATE TABLE sales(

-> id INT AUTO_INCREMENT,

-> product VARCHAR(100) NOT NULL,

-> quantity INT NOT NULL DEFAULT 0,

-> fiscalYear SMALLINT NOT NULL,

-> fiscalMonth TINYINT NOT NULL,

-> CHECK(fiscalMonth >= 1 AND fiscalMonth <= 12),

-> CHECK(fiscalYear BETWEEN 2000 and 2050),

-> CHECK (quantity >=0),

 -> UNIQUE(product, fiscalYear, fiscalMonth),

-> PRIMARY KEY(id)

-> );

Query OK, 0 rows affected (0.023 sec)

MariaDB [nividb]> INSERT INTO sales(product, quantity, fiscalYear, fiscalMonth)

-> VALUES('2003 Harley-Davidson Eagle Drag Bike',120, 2020,1),('1969 Corvair Monza',

150,2020,1),('1970 Plymouth Hemi Cuda', 200,2020,1);

Query OK, 3 rows affected (0.006

sec) Records: 3 Duplicates: 0

Warnings: 0

MariaDB [nividb]> SELECT * FROM sales;

+ + + + + +

| id | product | quantity | fiscalYear | fiscalMonth |

+ + + + + +

| 1 | 2003 Harley-Davidson Eagle Drag Bike | 120 | 2020 | 1|

| 2 | 1969 Corvair Monza | 150 | 2020 | 1|

| 3 | 1970 Plymouth Hemi Cuda | 200 | 2020 | 1|

+ + + + + +

3 rows in set (0.001 sec)

MariaDB [nividb]> DELIMITER //

MariaDB [nividb]> CREATE TRIGGER before_sales_update

-> BEFORE UPDATE

-> ON sales FOR EACH ROW

-> BEGIN

-> DECLARE errorMessage VARCHAR(255);

-> SET errorMessage = CONCAT('The new quantity ',

-> NEW.quantity,

-> ' cannot be 3 times greater than the current quantity ',

-> OLD.quantity);
 ->

-> IF new.quantity > old.quantity * 3 THEN

-> SIGNAL SQLSTATE '45000'

-> SET MESSAGE_TEXT = errorMessage;

-> END IF

-> END //

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to
your MariaDB server version for the right syntax to use near 'END' at line 15

MariaDB [nividb]> DELIMITER //

MariaDB [nividb]> CREATE TRIGGER before_sales_update

-> BEFORE UPDATE

-> ON sales FOR EACH ROW

-> BEGIN

-> DECLARE errorMessage VARCHAR(255);

-> SET errorMessage = CONCAT('The new quantity ',

-> NEW.quantity,

-> ' cannot be 3 times greater than the current quantity ',

-> OLD.quantity);

-> IF new.quantity > old.quantity * 3 THEN

-> SIGNAL SQLSTATE '45000'

-> SET MESSAGE_TEXT = errorMessage;

-> END IF;

-> END //

Query OK, 0 rows affected (0.017 sec)

MariaDB [nividb]> DELIMITER ;

MariaDB [nividb]> UPDATE sales SET quantity = 150 WHERE id = 1;

Query OK, 1 row affected (0.007 sec)

Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [nividb]> SELECT * FROM sales;

+ + + + + +

| id | product | quantity | fiscalYear | fiscalMonth |

+ + + + + +

| 1 | 2003 Harley-Davidson Eagle Drag Bike | 150 | 2020 | 1|

| 2 | 1969 Corvair Monza | 150 | 2020 | 1|

| 3 | 1970 Plymouth Hemi Cuda | 200 | 2020 | 1|

+ + + + + +

3 rows in set (0.001 sec)

MariaDB [nividb]> SHOW ERRORS;

Empty set (0.001 sec)

MariaDB [nividb]> UPDATE sales SET quantity = 500 WHERE id = 1;

ERROR 1644 (45000): The new quantity 500 cannot be 3 times greater than the current quantity 150

MariaDB [nividb]> UPDATE sales SET quantity = 500 WHERE id = 1;

AFTER UPDATE
MariaDB [ddukkdb]> CREATE TABLE prod_details(id INT PRIMARY KEY AUTO_INCREMENT,name
VARCHAR(100),quantity INT(10));

Query OK, 0 rows affected (0.033 sec)

MariaDB [ddukkdb]> INSERT INTO prod_details(name,quantity)VALUES("Face wash",23),("Face

mask", 39),("Hair shampoo",29),("Hair Gel",53);

Query OK, 4 rows affected (0.022

sec) Records: 4 Duplicates: 0

Warnings: 0

MariaDB [ddukkdb]> SELECT * FROM prod_details;

+ + + +

| id | name | quantity |

+ + + +

| 1 | Face wash | 23 |
| 2 | Face mask | 39 |

| 3 | Hair shampoo | 29 |

| 4 | Hair Gel | 53 |

+ + + +

4 rows in set (0.001 sec)

MariaDB [ddukkdb]> CREATE TABLE q_details(id INT PRIMARY KEY AUTO_INCREMENT,prod_id

INT,old_q INT,new_q INT,updated_at DATETIME);

Query OK, 0 rows affected (0.031 sec)

MariaDB [ddukkdb]> DELIMITER //

MariaDB [ddukkdb]> CREATE TRIGGER after_update

-> AFTER UPDATE ON prod_details

-> FOR EACH ROW

-> BEGIN

-> IF OLD.quantity!=NEW.quantity THEN

-> INSERT INTO q_details(prod_id,old_q,new_q,updated_at)

-> VALUES(OLD.id,OLD.quantity,NEW.quantity, NOW());

-> END IF;

-> END //

Query OK, 0 rows affected (0.027 sec)

MariaDB [ddukkdb]> DELIMITER ;

MariaDB [ddukkdb]> UPDATE prod_details SET quantity = 30 WHERE id = 1;

Query OK, 1 row affected (0.010 sec)

Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [ddukkdb]> SELECT * FROM prod_details;

+ + + +

| id | name | quantity |

+ + + +
| 1 | Face wash | 30 |

| 2 | Face mask | 39 |

| 3 | Hair shampoo | 29 |

| 4 | Hair Gel | 53 |

+ + + +

4 rows in set (0.001 sec)

MariaDB [ddukkdb]> SELECT * FROM q_details;

+ + + + + +

| id | prod_id | old_q | new_q | updated_at |

+ + + + + +

| 1| 1 | 23 | 30 | 2023-10-10 12:40:32 |

+ + + + + +

1 row in set (0.001 sec)

MariaDB [ddukkdb]> UPDATE prod_details SET quantity = 90 WHERE id = 4;

Query OK, 1 row affected (0.021 sec)

Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [ddukkdb]> SELECT * FROM q_details;

+ + + + + +

| id | prod_id | old_q | new_q | updated_at |

+ + + + + +

| 1| 1 | 23 | 30 | 2023-10-10 12:40:32 |

| 2| 4 | 53 | 90 | 2023-10-10 12:41:22 |

+ + + + + +

2 rows in set (0.001 sec)