日期:2014-05-16  浏览次数:20697 次

php批量生成mysql触发器定义语句
<?php
define('TRIGGER_AFTER_INSERT_SQL',"DELIMITER |
DROP TRIGGER IF EXISTS %s;
CREATE TRIGGER %s
AFTER INSERT ON %s
FOR EACH ROW BEGIN
    SET @result_set = (select clear_cache());
END |
DELIMITER ;\r\n\r\n");

define('TRIGGER_AFTER_UPDATE_SQL',"DELIMITER |
DROP TRIGGER IF EXISTS %s;
CREATE TRIGGER %s
AFTER UPDATE ON %s
FOR EACH ROW BEGIN
    SET @result_set = (select clear_cache());
END |
DELIMITER ;\r\n\r\n");

define('TRIGGER_AFTER_DELETE_SQL',"DELIMITER |
DROP TRIGGER IF EXISTS %s;
CREATE TRIGGER %s
AFTER DELETE ON %s
FOR EACH ROW BEGIN
    SET @result_set = (select clear_cache());
END |
DELIMITER ;\r\n\r\n");

mysql_connect('127.0.0.1:3307','root','') or die(mysql_error());
mysql_select_db('db');
mysql_query('SET NAMES UTF8');

$triggerDDL = '';

$rs = mysql_query('SHOW TABLES');
while($row = mysql_fetch_array($rs))
{
	$tblName = $row['Tables_in_db'];

	
	$triggerDDL .= "\r\n--\r\n-- 表的触发器 `".$tblName."`\r\n--\r\n";
	
	$triggerName = $tblName.'_insert';
	$triggerDDL .= sprintf(TRIGGER_AFTER_INSERT_SQL,$triggerName,$triggerName,$tblName);

	$triggerName = $tblName.'_update';
	$triggerDDL .= sprintf(TRIGGER_AFTER_UPDATE_SQL,$triggerName,$triggerName,$tblName);

	$triggerName = $tblName.'_delete';
	$triggerDDL .= sprintf(TRIGGER_AFTER_DELETE_SQL,$triggerName,$triggerName,$tblName);
}

file_put_contents('db_trigger.sql',$triggerDDL);
?>