Database basic - Part 17: Trigger

Video on Youtube:

SQL Script:

USE JadeDb;

DROP TABLE IF EXISTS Employees;

CREATE TABLE IF NOT EXISTS Employees (

id INT PRIMARY KEY AUTO_INCREMENT,

    `name` VARCHAR(30),

    department_id INT DEFAULT 1,

    salary FLOAT

);

INSERT INTO Employees (`name`, department_id, salary)

VALUES

('Alice', 1, 70000),

('Bob', 2, 80000),

('Charlie', 3, 60000),

('David', 3, 75000),

('Eve', 2, 90000);

DROP TRIGGER IF EXISTS trigger_add_employees;

DELIMITER $$

CREATE TRIGGER trigger_add_employees 

    BEFORE INSERT ON Employees

    FOR EACH ROW

    BEGIN

IF NEW.salary > 100000 THEN

SIGNAL SQLSTATE '12345'

            SET MESSAGE_TEXT = 'salary is not greater than 100000';

END IF;

    END $$

DELIMITER ;

use JadeDb;

INSERT INTO Employees (`name`, department_id, salary)

VALUE

('Alice2', 2, 700000);