MySQL learning triggers

In the borrowing table and the reader table there is such a relationship, if you add a data in the borrowing table, the corresponding cumulative borrowing field in the reader table is increased by 1, if you delete a data in the borrowing table, the corresponding cumulative borrowing field in the reader table is reduced by 1, the method to achieve this function is as follows.

1. Create Reader table

1 CREATE TABLE' T_borrowbook ' (2' Borrowbookid 'int( One) not NULLAuto_increment,3' BookId 'int( One)DEFAULT NULL,4' ID 'int( One)DEFAULT NULL,5' Audienceid 'int( One)DEFAULT NULL,6' StartTime 'datetime DEFAULT NULL,7' EndTime 'datetime DEFAULT NULL,8' Borrowbookdesc 'text,9   PRIMARY KEY(' Borrowbookid ')Ten)

2. Create a borrowing form

1 CREATE TABLE' T_audience ' (2' Audienceid 'int(Ten) not NULLAuto_increment,3' Audiencename 'varchar( -) not NULL,4' Audiencetypeid 'int(Ten) not NULL,5' Audiencenumber 'varchar( -) not NULL,6' Sex 'varchar( -)DEFAULT NULL,7' Cellphone 'varchar( One)DEFAULT NULL,8' Borrowbooknum 'int(Ten)DEFAULT NULL,9' Audiencedesc 'text,Ten   PRIMARY KEY(' Audienceid ') One)

3. Create a plug-in self-increment trigger

1 DELIMITER $$2 3 CREATE4     /*[definer = {User | Current_User}]*/5     TRIGGER' Db_book '. ' Tr_borrowbook_insert ' afterINSERT6      on' db_book '. ' T_borrowbook '7      forEach ROWBEGIN8     UPDATET_audienceSETBorrowbooknum=Borrowbooknum+1 WHEREAudienceid=New.audienceid;9     END$$Ten  OneDELIMITER;

4. Create Delete auto-Subtract trigger

1 DELIMITER $$2 3 CREATE4     /*[definer = {User | Current_User}]*/5     TRIGGER' Db_book '. ' Tr_borrowbook_delete ' afterDELETE6      on' db_book '. ' T_borrowbook '7      forEach ROWBEGIN8     UPDATET_audienceSETBorrowbooknum=Borrowbooknum-1 WHEREAudienceid=Old.audienceid;9     END$$Ten  OneDELIMITER;

A little progress every day, everything is the best arrangement.

MySQL learning triggers