Automatically generate mysql trigger code for data synchronization-MySQL

Automatically generate mysql trigger code for data synchronization bitsCN.com

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.

$ 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 = "insert trigger for table {$ tab1 ";
$ Inser_str. = "create trigger". $ tab1. "_ insert
AFTER INSERT
On ". $ tab1 ."
";
$ Inser_str. = "for each row
Insert into {$ tab2} (". rtrim ($ str1,).) VALUES (. rtrim ($ str2 ,,).);;
// Update trigger
$ Update_str = "update trigger for {$ tab1} Table ";
$ Update_str. = "create trigger". $ tab1. "_ update
";
$ Update_str. = "after update
";
$ Update_str. = "on". $ tab1.
;
$ Update_str. = "for each row
";
$ 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 trigger for {$ tab1} Table ";
$ Delete_str. = "create trigger". $ tab1. "_ delete
";
$ Delete_str. = "after delete
";
$ Delete_str. = "on". $ tab1 ."
";
$ Delete_str. = "for each row
";
$ 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
";
Echo $ inser_str;
Echo $ update_str;
Echo $ delete_str;
Echo

Note:
1. triggers can be used for InnoDB or MyISAM tables.
2. when inserting, use after insert to better ensure the corresponding data ID. if you use before INSERT, it is possible that the ID does not match.
3. for example, new. id indicates the fields in the master table.
4. too many SQL statements. Use begin .. end
5. use the show triggers statement to view the triggers in the database.
6. delete the trigger drop trigger if exists 'test'
7. author: star of China;
Echo < Syntax:
Create trigger <触发器名称>

{Before | after}

{Insert | update | delete}

On <表名>

For each row

<触发器sql语句>

The parameters are described as follows:

Create trigger <触发器名称> : Create a new trigger and specify the trigger name.

{Before | after}: Used to specify whether to trigger the insert, update, or delete statement before or after the statement is executed.

On <表名> : Specifies the name of the table that responds to the trigger.

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.

<触发器sql语句> : The SQL statement to be executed by the trigger. if the trigger wants to execute multiple SQL statements, put multiple statements in begin... In the end block.

For example, begin... End block (it cannot run on phpadmin. Because phpmyadmin does not parse begin... end .)

Mysql_query ("

Create trigger user_delete

After delete

On user

For each row BEGIN

Delete from user_bak where id = OLD. id;

Delete from aaa where id = OLD. id;

END ;");

EOT;
?>
PHP and MYSQL triggers

BitsCN.com