Temporary disable triggers in MySQL

Ngày 20 tháng 12 năm 2011 Trương Chương Dương
For now, we not possible to temporary disable triggers in MySQL.

But there are several tricks:
  1. Drop triggers
  2. Do anything you need (import, update, etc)
  3. Create triggers

Drop triggers is verry easy, just execute: DROP TRIGGER IF EXISTS <trigger_name>;
But how to implement a script auto re-create all trigger? Because if you hardcode the trigger inside your code, we will meet big problem when want to change these trigger in the future, or if some one else add other trigger/change the trigger name in DB, our code will fail.
I suggest you a solution, this is the code which can delete all trigger and re-create it dynamically:

$conn = mysql_connect($config['host'], $config['username'], $config['password']) or
die("Could not connect: " . mysql_error());

mysql_select_db($config['dbname']);

//Lissting current triggers
$sql = "SHOW TRIGGERS WHERE `Table` = '<table name, wich we want to disable the trigger>'";
$trigger_rows_t = mysql_query($sql);

$trigger_rows = array();

//Disable current triggers
while ($row = mysql_fetch_array($trigger_rows_t, MYSQL_ASSOC)) {
    $trigger_rows[] = $row;
    
    $sql = "DROP TRIGGER IF EXISTS `{$row['Trigger']}`";
    
    mysql_query($sql);
}

mysql_free_result($trigger_rows_t);


    /*-----------------------------------------------------------
        Excute your querry here, every think you want
    ------------------------------------------------------------*/


//Re-create triggers
foreach ($trigger_rows as $item)
{
    $sql = "
    CREATE TRIGGER `{$item['Trigger']}` {$item['Timing']} {$item['Event']} ON `{$item['Table']}`
     FOR EACH ROW {$item['Statement']}
    ";
    
   mysql_query($sql);
}
mysql_close($conn);

Đang tải dữ liệu...
web site traffic statistics