8Python Full Stack Road series of MySQL Trigger

Python full stack road series MySQL Trigger

L

When you want to 增/删/改 trigger a particular behavior before and after a table, triggers are used to customize the behavior of the user 增/删/改 before and after the rows of the table.

To create a trigger basic syntax

Before inserting

CREATE TRIGGER tri_before_insert_tb1 before insert on TB1 for each rowbegin ... END

After inserting

CREATE TRIGGER tri_after_insert_tb1 after insert on TB1 for each rowbegin ... END

Before deleting

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 update

CREATE TRIGGER tri_before_update_tb1 before update on TB1 for each rowbegin ... END

After the update

CREATE TRIGGER tri_after_update_tb1 after update on TB1 for each rowbegin ... END

Trigger instance

Create auser_infoTables anduser_info_backTable, Inside thereUID,Name,Password,E-milColumn

create table  ' User_info '   (   ' UID '  int (5)  not null auto_increment ,   ' Name '  char ( NOT NULL,   ' Password '  varchar (+)  default  NULL,   ' Email '  varchar (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 () ' is 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

user_info before inserting data into TRI_BEFORE_INSERT_TB1 Trigger, perform the action inside

Delimiter%create TRIGGER tri_before_insert_tb1 before insert on User_info for each rowbegin--if inserted name= "as" if NEW. Name = "Ansheng" then – then insert this data into the User_info_back table first, the same data insert into User_info_back (name,password,email) VALUES (NEW . Name,new. Password,new. Email); END IF; End%delimiter;

Using triggers

Triggers cannot be called directly by the user, but are known to be caused passively by operations on the table 增/删/改 .

user_infoinsert two data into the table

INSERT into User_info (name,password,email) VALUES ("Ansheng", "Ansheng", "[email protected]"), ("root", "R", "[email Protected] ");

View data in a table

mysql> select * from user_info;+-----+---------+----------+--------------------+|  uid | name    | password | email               |+-----+---------+----------+--------------------+|    1 | ansheng | ansheng  | [email protected] | |    2 | root    | r         | [email protected]    |+-----+---------+----------+--------------------+2  rows in set  (0.00 sec) mysql> select * from user_info_back;+-- ---+---------+----------+--------------------+| uid | name    |  password | email               |+-----+---------+----------+--------------------+|   1 | ansheng | ansheng  | [email  protected] |+-----+---------+----------+--------------------+1 row in set  (0.00  SEC)

Delete Trigger

DROP TRIGGER tri_after_insert_tb1;

NEWRepresents the data row that is about to be inserted, OLD represents the data row that is about to be deleted, only new is valid for the INSERT statement, only old is valid for the DELETE statement, and the UPDATE statement can be used in conjunction with new and old

#Python全栈之路

8Python Full Stack Road series of MySQL Trigger