Application of MySQL stored procedures and triggers

"MySQL stored procedures and triggers--don't An Shu" *********************

1. Under what circumstances are stored procedures used?

To complete some troublesome logic, such as multi-table in the MySQL side of the CPU is very idle, with the stored procedure is a good choice,

1.1. Simple stored Procedure example: simple Write

DELIMITER $$ Use' curl_test ' $$DROP PROCEDURE IF EXISTS' data_s ' $$CREATEDefiner=' Root ' @ ' localhost 'PROCEDURE' data_s ' ()BEGINDECLAREIINT DEFAULT 0; whileI<  + DoINSERT  intoalbum (' artist_id ', ' name ', ' url ')VALUESI'don't An Shu .','2015-10-01');SETI=I+ 1;END  while;END$ $DELIMITER;

1.2. Simple stored Procedure Example: association update

DROP PROCEDURE IF EXISTSTestProcedure;CREATE PROCEDURETestProcedure ()BEGINDECLAREFlagINT DEFAULT 0;DECLARETIDINT;DECLARETdeptCHAR(255);DECLARETaliasCHAR( -);DECLARECurCURSOR  for SELECTId,dept fromusers;DECLARE CONTINUEHANDLER for  notFOUNDSETFlag= 1;OPENcur;FETCHCur intotid,tdept; whileFlag<>1 DoSELECTAlias fromDeptWHEREName=Tdept intoTalias;UPDATEUsersSETDept_alias=TaliasWHEREId=TID;FETCHCur intotid,tdept;END  while;CLOSEcur;END

Advantages of stored procedures:

Pre-compilation, compared to the direct SQL efficiency will be high, while reducing the flow of SQL statements during the transmission of traffic;

Streamline business logic to transform requirements to professional DBAs (if any);

More convenient use of MySQL database things processing, especially shopping sites;

Security, user rights easier to manage;

Modify the stored procedure basically do not need to modify the program code, and directly write SQL to modify SQL generally have to modify the relevant program;

2. mysql Trigger

Trigger (Trigger): Monitors a situation and triggers an action.

Trigger creation syntax four elements:

2.1. Monitoring location (table)

2.2. Monitoring Events (Insert/update/delete)

2.3. Trigger Time (After/before)

2.4. Trigger Event (Insert/update/delete)

Grammar:

Create Trigger Triggername

After/before insert/update/delete on table name

For each row #这句话在mysql是固定的

Begin

SQL statements;

End

Trigger Example: "Update the NUM field of the artist table minus 3 when new data is added to the album table"

DELIMITER $$ Use' curl_test ' $$DROP TRIGGER /*!50032 IF EXISTS*/' Change_num ' $$CREATE/*!50017 definer = ' root ' @ ' localhost '*/TRIGGER' Change_num ' afterINSERT  on' album ' forEach ROWBEGINUPDATE' Artist 'SETNum=Num-3;END; $ $DELIMITER;

Application of MySQL stored procedures and triggers