wetaba script.mysql

USE 171836_lab2_triggers;
SHOW CREATE TABLE employees;
-- Create duplicate table
CREATE TABLE `employees` (
`date_of_change` timestamp(2) NOT NULL DEFAULT CURRENT_TIMESTAMP(2)
COMMENT 'Records the date and time when the data was manipulated. This will
help to keep track of the changes made. The assumption is that no 2 users
will change the exact same record at the same time (with a precision of a
hundredth of a second, e.g., 4.26 seconds).',
`employeeNumber` int NOT NULL,
`lastName` varchar(50) DEFAULT NULL,
`firstName` varchar(50) DEFAULT NULL,
`extension` varchar(10) DEFAULT NULL,
`email` varchar(100) DEFAULT NULL,
`officeCode` varchar(10) DEFAULT NULL,
`reportsTo` int DEFAULT NULL,
`jobTitle` varchar(50) DEFAULT NULL,
`change_type` varchar(50) NOT NULL COMMENT 'Records the type of data
manipulation that was done, for example an insertion, an update, or a
deletion.',
PRIMARY KEY (`date_of_change`),
UNIQUE KEY `date_of_change_UNIQUE` (`date_of_change`)
) ENGINE=InnoDB;

-- Create trigger (Step 2);
CREATE TRIGGER TRG_BEFORE_UPDATE_ON_employees
BEFORE UPDATE ON employees FOR EACH ROW
INSERT INTO `employees_undo` SET
`date_of_change` = CURRENT_TIMESTAMP(2),
`employeeNumber` = OLD.`employeeNumber` ,
`lastName` = OLD.`lastName` ,
`firstName` = OLD.`firstName` ,
`extension` = OLD.`extension` ,
`email` = OLD.`email` ,
`officeCode` = OLD.`officeCode` ,
`reportsTo` = OLD.`reportsTo` ,
`jobTitle` = OLD.`jobTitle` ,
`change_type` = 'An update DML operation was executed';

-- Show triggers (Step 3);
SHOW TRIGGERS;