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 ;

Advertisements

Leave a Comment »

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: