MySQL Flip-flop of 8Python full Stack path Series

MySQL Flip-flop of Python full Stack path Series

L

If you want to trigger a specific behavior before and after a table add / delete / change operation, you can use a trigger, which is used to customize the user's behavior before and after adding / deleting / changing the rows of the table.

Basic syntax for creating triggers

Before insertion

CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROWBEGIN... END

After insertion

CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROWBEGIN... END

Before deletion

CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROWBEGIN... END

After deletion

CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROWBEGIN... END

Before UPDAT

CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROWBEGIN... END

After updater

CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROWBEGIN... END trigger instance

Create a user_ info table and a user_info_back table with the UID,Name,Password,E-mil column in it

CREATE TABLE `UID` int (5) NOT NULL AUTO_INCREMENT, `Name` char (15) NOT NULL, `Password` varchar (32) DEFAULT NULL, `Email`varchar (255,255) DEFAULT NULL, PRIMARY KEY (`UID`, `Name`) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `user_info_ back` (`UID` int (5) NOT NULL AUTO_INCREMENT, `Name`char (15) NOT NULL, `Password` varchar (32) DEFAULT NULL, `Email`varchar (255) DEFAULT NULL, PRIMARY KEY (`UID`, `Name`) ENGINE=InnoDB DEFAULT CHARSET=utf8; create a pre-insert trigger

The function of the trigger is to enter the user_ trigger before inserting data into the tri_before_insert_tb1 info table and perform the operation inside.

Delimiter% CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON user_info FOR EACH ROWBEGIN-- if the Name= "as" IF NEW.Name = "ansheng" THEN at the time of insertion-then insert this data into the user_info_ backtable first, with the same data INSERT INTO user_info_back (Name,Password,Email) VALUES (NEW.Name,NEW.Password,NEW.Email); END IF;END%delimiter; use the trigger

The trigger cannot be called directly by the user, but it is caused by the passive operation of adding / deleting / modifying the table.

Insert two pieces of data into the user_ info table

INSERT INTO user_info (Name,Password,Email) VALUES ("ansheng", "ansheng", "ansheng@ansheng.me"), ("root", "r", "root@ansheng.me")

View the data in the table

Mysql > select * from user_info +-+ | UID | Name | Password | Email | +-+ | 1 | ansheng | ansheng | ansheng @ ansheng.me | | 2 | root | r | root@ansheng.me | +-+ 2 rows in set (0.00 sec) mysql > select * from user_info_back +-+ | UID | Name | Password | Email | +-+ | 1 | ansheng | ansheng | ansheng @ ansheng.me | +-+ 1 row in set (0.00 sec) delete trigger DROP TRIGGER tri_after_insert_tb1

NEW represents the data row to be inserted, OLD represents the data row to be deleted, for INSERT statements, only NEW is legal, for DELETE statements, only OLD is legal, and UPDATE statements can be used with NEW and OLD

# Python full Stack Road