Create Trigger in MySql Database

Create Two Table

First Table : employees

 CREATE TABLE employees (
employeeNumber INT(11) ,
lastName VARCHAR(200),
firstName VARCHAR(200),
extension VARCHAR(200),
email VARCHAR(100),
officeCode VARCHAR(100),
reportsTo INT(11),
jobTitel VARCHAR(100)
 )

Insert value in the 1st table employees

insert  into `employees`(`employeeNumber`,`lastName`,`firstName`,`extension`,`email`,`officeCode`,`reportsTo`,`jobTitel`) values (1,'sahoo','Raj','120','raj@gmail.com','201200',22,'HR');


Second Table : employees

CREATE TABLE employees_audit (
    id INT(11) NOT NULL AUTO_INCREMENT,
    employeeNumber INT(11) NOT NULL,
    lastname VARCHAR(50) NOT NULL,
    changedon DATETIME DEFAULT NULL,
    ACTION VARCHAR(50) DEFAULT NULL,
    PRIMARY KEY (id)
 )

Update Trigger

Here we create a trigger before_employee_update.

when we update employees table it will automatically insert data into employees_audit on behave of
matching employeeNumber.

DELIMITER $$
CREATE TRIGGER before_employee_update
    BEFORE UPDATE ON employees
    FOR EACH ROW BEGIN
    INSERT INTO employees_audit
    SET ACTION = 'update',
     employeeNumber = OLD.employeeNumber,
        lastname = OLD.lastname,
       changedon = NOW();
END$$
DELIMITER ;

After create trigger we run this query

UPDATE employees SET lastName = 'Phan' WHERE employeeNumber = 1

It insert into employees_audit table.


Delete Trigger

Create a another table empsalary

CREATE TABLE `empsalary` (
  `employeeNumber` int(20) DEFAULT NULL,
  `salary` varchar(4000) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Create trigger delete_employees


DELIMITER $$
CREATE TRIGGER delete_employees
    AFTER DELETE ON employees
    FOR EACH ROW BEGIN

    DELETE FROM employees_audit
    WHERE employeeNumber = OLD.employeeNumber;
 
     DELETE FROM empsalary
    WHERE employeeNumber = OLD.employeeNumber;
     
END$$
DELIMITER ;

After create this trigger we run following command

DELETE FROM employees WHERE employeeNumber=1

It will delete records from three tables where employeeNumber=1


BEFORE INSERT TRIGGER

create trigger product_Trigger

CREATE TRIGGER product_Trigger BEFORE INSERT ON employees
FOR EACH ROW
UPDATE employees_audit SET lastname=New.lastName
WHERE employeeNumber=New.employeeNumber


After run this query you run following command

INSERT INTO employees(employeeNumber,lastName,firstName,extension,email,officeCode,reportsTo,jobTitel) VALUES(1,"amit",'kumar','samal','patta','0012',200,'fsf')

After run this command it insert these values into employees table and update employees_audit  table
set lastname=amit where it found employeeNumber is same that is 1.


AFTER INSERT TRIGGER

create trigger insert_multiple_tables

CREATE TRIGGER insert_multiple_tables AFTER INSERT ON employees
FOR EACH ROW
INSERT INTO employees_audit(employeeNumber,lastname,changedon)
VALUES(NEW.employeeNumber,NEW.lastName,NOW())

here your trigger is created

After run this command you run following command

INSERT INTO employees(employeeNumber,lastName,firstName,extension,email,officeCode,reportsTo,jobTitel) VALUES(10,"amitas",'kumar','samal','patta','0012',200,'fsf')

it insert the data into employees table and employees_audit table