Mysql Trigger Update & Insert

18th Mar 2011 at 11:21 AM | Posted in Uncategorized | Leave a comment

SHOW TRIGGERS;

CREATE TABLE `employees` (  `employeeNumber` int(11) NOT NULL,  `lastName` varchar(50) NOT NULL,  `firstName` varchar(50) NOT NULL,  `extension` varchar(10) NOT NULL,  `email` varchar(100) NOT NULL,  `officeCode` varchar(10) NOT NULL,  `reportsTo` int(11) default NULL,  `jobTitle` varchar(50) NOT NULL,  PRIMARY KEY  (`employeeNumber`))

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) )
Insert:

DROP TRIGGER before_employee_update;DELIMITER $$CREATE TRIGGER before_employee_update BEFORE UPDATE ON employeesFOR EACH ROW BEGININSERT INTO employees_auditSET action = ‘update’,lastname = OLD.lastname,changedon = NOW(); END$$DELIMITER ;
Update:

DROP TRIGGER before_employee_update;DELIMITER $$CREATE TRIGGER before_employee_update BEFORE UPDATE ON employeesFOR EACH ROW BEGINUPDATE employees_auditSET action = ‘update’,employeeNumber = NEW.employeeNumber,lastname = NEW.lastname,changedon = NOW() WHERE employeeNumber=NEW.employeeNumber; END$$DELIMITER ;