| |
First, what is the trigger
Simply put, that is, a table that something happened (insert, delete, update operations), then automatically triggers a number of prewritten SQL statement executed;
Second, the characteristics and functions
Features: trigger action and a trigger SQL statement in the event that a transaction operation, atomic, either all executed or none is executed;
Role: to ensure data integrity, act as constraints;
Third, examples: create a trigger, recording of the table add, delete, change operation record
Next we will create the user and user_history tables, as well as three trigger tri_insert_user, tri_update_user, tri_delete_user, respectively corresponding user table to add, delete, change three things;
• Create a user table;
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`Id` bigint (20) NOT NULL AUTO_INCREMENT,
`Account` varchar (255) DEFAULT NULL,
`Name` varchar (255) DEFAULT NULL,
`Address` varchar (255) DEFAULT NULL,
PRIMARY KEY ( `id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
• Create a table of user operation history table;
DROP TABLE IF EXISTS `user_history`;
CREATE TABLE `user_history` (
`Id` bigint (20) NOT NULL AUTO_INCREMENT,
`User_id` bigint (20) NOT NULL,
`Operatetype` varchar (200) NOT NULL,
`Operatetime` datetime NOT NULL,
PRIMARY KEY ( `id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
• Create a user table insert corresponding event trigger tri_insert_user;
Some explanations:
DELIMITER: change the end character input, the input end of the default character is a semicolon (;), here turning it into a semicolon ;; two, that the purpose is to put multiple statements to be a package containing a semicolon, all after you have finished entering executed together, rather than experiencing a default semicolon terminator automatically executed;
new: When the trigger insert and update events are available, it points to the record being operated
old: when the trigger event deletes and updates available, point to the record being operated
DROP TRIGGER IF EXISTS `tri_insert_user`;
DELIMITER ;;
CREATE TRIGGER `tri_insert_user` AFTER INSERT ON` user` FOR EACH ROW begin
INSERT INTO user_history (user_id, operatetype, operatetime) VALUES (new.id, 'add a user', now ());
end
;;
DELIMITER;
• Create a user table update event corresponding trigger tri_update_user;
DROP TRIGGER IF EXISTS `tri_update_user`;
DELIMITER ;;
CREATE TRIGGER `tri_update_user` AFTER UPDATE ON` user` FOR EACH ROW begin
INSERT INTO user_history (user_id, operatetype, operatetime) VALUES (new.id, 'update a user', now ());
end
;;
DELIMITER;
• Create a user table delete the corresponding event trigger tri_delete_user;
DROP TRIGGER IF EXISTS `tri_delete_user`;
DELIMITER ;;
CREATE TRIGGER `tri_delete_user` AFTER DELETE ON` user` FOR EACH ROW begin
INSERT INTO user_history (user_id, operatetype, operatetime) VALUES (old.id, 'delete a user', now ());
end
;;
DELIMITER;
• So far, all the tables and triggers created, start the verification results, make respectively insert, edit, delete events, execute the following statement to see whether user_history automatically generated operating record;
INSERT INTO user (account, name, address) VALUES ( 'user1', 'user1', 'user1');
INSERT INTO user (account, name, address) VALUES ( 'user2', 'user2', 'user2');
UPDATE user SET name = 'user3', account = 'user3', address = 'user3' where name = 'user1';
DELETE FROM `user` where name = 'user2';
• observation results tables and user tables user_history, operation records generated, indicating that the trigger is working properly;
Fourth, malpractice
Increase the complexity of the program, some business logic in the code process, some business logic with the trigger handle, make post-maintenance becomes difficult; |
|
|
|