If there are hundreds of tables in the company to trigger, if you manually write code. Very tired, so today I wrote a small program to automatically generate the mysql trigger code.
<? Php
$ Dbname = test; // Database
$ Tab1 = user; // executed table
$ Tab2 = user_bak; // triggered table
$ Conn = mysql_connect ("localhost", "root", "root", $ dbname) or die ("check your host name database username and password ");
Mysql_select_db ($ dbname, $ conn) or die ("the database is not connected ");
$ Query = mysql_query ("show columns from $ tab1"); mysql_query ("");
While ($ row = mysql_fetch_array ($ query, MYSQL_NUM) {// obtain the field array of the table
$ Temp [] = $ row [0];
$ Str1. = '. $ row [0].',;
$ Str2. = new... $ row [0].,;
$ Str3. = $ row [0]. = new... $ row [0].,;
}
// Insert trigger
$ Inser_str = "
$ Inser_str. = "create trigger". $ tab1. "_ insert <br> after insert <br> on". $ tab1. "<br> ";
$ Inser_str. = "for each row <br> insert into {$ tab2 }(". rtrim ($ str1 ,,).) VALUES (. rtrim ($ str2 ,,).);;
// Update trigger
$ Update_str = "
$ Update_str. = "create trigger". $ tab1. "_ update <br> ";
$ Update_str. = "after update <br> ";
$ Update_str. = "on". $ tab1. <br>;
$ Update_str. = "for each row <br> ";
$ Update_str. = "update ". $ tab2. "set ". rtrim ($ str3 ,,). "where id = new. id; // where id = new. you need to manually change the id. Change the field name of the primary key ";
// Delete trigger
$ Delete_str = "
$ Delete_str. = "create trigger". $ tab1. "_ delete <br> ";
$ Delete_str. = "after delete <br> ";
$ Delete_str. = "on". $ tab1. "<br> ";
$ Delete_str. = "for each row <br> ";
$ Delete_str. = "delete from". $ tab2. "where id = OLD. id // here, where id = new. id must be changed manually. Change the field name of the primary key <br> ";
Echo $ inser_str;
Echo $ update_str;
Echo $ delete_str;
Echo <p> Note: <br> 1. triggers can be used for InnoDB or MyISAM tables <br> 2. when inserting, use after insert to better ensure the corresponding data ID. If you use before INSERT, it may cause the ID mismatch <br> 3. such as new. id indicates the fields in the master table <br> 4. too many SQL statements. Use begin .. end <br> 5. use the show triggers statement to view the triggers in the database. <Br> 6. Delete trigger drop trigger if exists 'test' <br> 7. Author: <a href = "http://hi.baidu.com/woaidelphi"> Star of China </a>;
Echo <EOT
<H3> Syntax: Create trigger <trigger Name> <br>
{Before | after} <br>
{Insert | update | delete} <br>
On <Table Name> <br>
For each row <br>
<Trigger SQL statement> <br>
The parameters are described as follows: <br>
Create trigger <trigger Name>: create a New trigger and specify the trigger name. <Br>
{Before | after}: used to specify whether to trigger the insert, update, or delete statement before or after the statement is executed. <Br>
On <Table Name>: Specifies the name of the table that responds to the trigger. <Br>
For each row: The execution interval of the trigger. for each row notifies the trigger to execute an action every row instead of the entire table. <Br>
<Trigger SQL statement>: the SQL statement to be executed by the trigger. If the trigger needs to execute multiple SQL statements, Place multiple statements in begin... In the end block.
<Br> for example, begin... End Block (it cannot run on phpadmin. Because phpmyadmin does not parse begin... end .) <Br>
Mysql_query ("<br>
Create trigger user_delete <br>
After delete <br>
On user <br>
For each row BEGIN <br>
Delete from user_bak where id = OLD. id; <br>
Delete from aaa where id = OLD. id; <br>
END; "); <br>
<Br>
EOT;
?>
PHP and MYSQL triggers