mysql触发器数据库同步数据方法

mysql教程触发器数据库教程同步数据方法

数据库名



驱动表


被触发的表

/**
* -------------------文档注释------------------------------------------
* 作者*达达*QQ 85089479*email qianshuei@163.com*
* 创建时间*2006-012-*
* 功能描述*数据同步触发器生成工具*
* 最后修改时间*2006-12-*
* -------------------------------------------------------------------
*/
$dbName = "localhost"; //主机名
$dbUserName = "root"; // 用户名
$dbpassword = "admin"; //密码
$dbTbaleName = $_POST['dbTbaleName']; //数据库名
$tab1 = $_POST['tab1']; //执行的表
$tab2 = $_POST['tab2']; //被触发的表
$conn = mysql_connect($dbName, $dbUserName, $dbpassword) or die("请检查你的主机名数据库用户名和密码");
mysql_select_db($dbTbaleName, $conn) or die("数据库还没有连接");
mysql_select_db("$dbTbaleName");
$result = mysql_query("select * from $tab1")//得到表查询的结果
or die("Query failed: " . mysql_error());
echo("
");
$i = 1;//这里是为了替换最后一个列名的，符号所以=1 <>0
echo "create trigger " . $tab1 . "_insert BEFORE INSERT on $tab1
";
echo "for each row BEGIN
";
echo "INSERT INTO $tab2 SET
";
while ($i < mysql_num_fields($result)){//列的数量的循环

$meta = mysql_fetch_field($result);//得到一个关于列的信息的对角
if (!$meta){
echo "No information available
n";
}
//下面输出的是触发器程序列信息
echo "

  `$meta->name`=NEW.$meta->name,

";
$i++;
}
$meta = mysql_fetch_field($result);
echo "

  `$meta->name`=NEW.$meta->name;

";
mysql_free_result($result);
echo("END;");

// -----------------------------------------------------------------
$result = mysql_query("select * from $tab1")
or die("Query failed: " . mysql_error());
$i = 1;
if ($i < mysql_num_fields($result)){

$meta = mysql_fetch_field($result);

// -----------------------------------------------------------------
/**
* create trigger `tri_del` BEFORE DELETE on `aaa`
* for each row BEGIN
* DELETE FROM bbb WHERE id =OLD.id;
*/
echo("
");


echo "create trigger " . $tab1 . "_del BEFORE DELETE on $tab1
";
echo "for each row BEGIN
";

echo "DELETE FROM $tab2 WHERE $meta->name =OLD.$meta->name;
";
echo("END;");

}
// -----------------------------------------------------------------
echo("
");
/**
* create trigger `up_date` BEFORE UPDATE on `aaa`
* for each row BEGIN
* UPDATE bbb set a1 = new.a1 , a2=new.a2 where id = new.id;
* END;
*/
$result = mysql_query("select * from $tab1")
or die("Query failed: " . mysql_error());
/**
* get column metadata
*/
$i = 1;
echo "create trigger " . $tab1 . "_update BEFORE UPDATE on $tab1
";
echo "for each row BEGIN
";
echo "UPDATE $tab2 SET
";
while ($i < mysql_num_fields($result)){

$meta = mysql_fetch_field($result);
if (!$meta){
echo "No information available
n";
}
echo "

  `$meta->name`=NEW.$meta->name,

";
$i++;
}
$meta = mysql_fetch_field($result);
echo "

  `$meta->name`=NEW.$meta->name

";
mysql_free_result($result);
// -----------------------------
$result = mysql_query("select * from $tab1")
or die("Query failed: " . mysql_error());
$i = 1;
if ($i < mysql_num_fields($result)){

$meta = mysql_fetch_field($result);
echo("where $meta->name = new.$meta->name;
");


echo("END;");

}
?>