1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131
|
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Document sans titre</title>
</head>
<body>
<?php
$i = 0;
$base_de_donnees = "kfdfd54";
$exclusion = " 'historique', 'tmp_card_import', 'tmp_card_export' ";
try {
$dbh = new PDO('mysql:host=192.168.2.156;dbname=rozskdk', 'kfdfd54','kllsodoo5454ldls54d4s5dsd45');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->beginTransaction();
$requ = "SELECT TABLE_NAME, TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '$base_de_donnees' AND TABLE_NAME NOT IN ($exclusion) ";
$sth = null;
$sth = $dbh->query($requ);
$result = $sth->fetchAll();
// Je récupère les données
foreach($result as $row) {
$tab_name = $row[0];
$up_tab_name = strtoupper($row[0]);
$tab_schema = $row[1];
$up_tab_schema = strtoupper($row[1]);
// Récupération du nombre de résultats
$count_sql = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '$tab_schema' AND TABLE_NAME = '$tab_name' LIMIT 0,1 ";
$tyh = null;
$tyh = $dbh->query($count_sql);
$count = $tyh->fetchColumn();
$var_list_if = "";
$j = 0;
while($j < $count) {
$sql = "SELECT COLUMN_NAME, COLUMN_KEY FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '$tab_schema' AND TABLE_NAME = '$tab_name' LIMIT $j, 1";
$rbh = null;
$rbh = $dbh->query($sql);
$rslt = $rbh->fetchAll();
foreach($rslt as $ft) {
$column_name = $ft[0];
$up_column_name = strtoupper($ft[0]);
$column_prim = $ft[1];
if(!empty($column_prim)) {
$id_key = $column_name;
}
$list_if = "IF(OLD.$column_name <> NEW.$column_name) THEN
INSERT INTO historique VALUES('', new.$id_key, '$up_tab_name', '$up_column_name', OLD.$column_name, NEW.$column_name, current_user, current_timestamp);
ELSE
SET listif = 1;
END IF; ";
}
$var_list_if = "$var_list_if $list_if";
$j++;
}
// Valeurs consolidées pour la table en cours
$all_value = $var_list_if;
$drp_sql = "DROP TRIGGER IF EXISTS update_in_".$up_tab_name;
$query = "CREATE TRIGGER update_in_".$up_tab_name." AFTER UPDATE ON ".$tab_name."
FOR EACH ROW
BEGIN
DECLARE listif varchar(255);
$all_value
END; ";
// Execution de la requête
$tgh = null;
$jth = null;
$tgh = $dbh->query($drp_sql);
$jth = $dbh->query($query);
}
$dbh->commit();
?>
<script language='javascript'>
alert("Creation de Triggers effective !");
</script>
<?php
} catch (PDOException $e) {
if ($dbh) {
$dbh->rollback();
}
echo "<font color=\"red\">Échec lors de la creation du trigger. </font><br>" . $e->getMessage();
exit;
}
?>
</body>
</html> |
Partager