admin – Page 3 – IT Integrated Business Solutions

[adsense id=”0514458240″ width=”468″ height=”60″]

MySQL does not provide a way of enabling or disabling triggers natively. Dropping the trigger and recreating it later on is one solution, however, this can be complex and someone could forget to re-create a trigger after an operation has been performed.

An alternative way is to write the trigger with a built in check to see whether it should trigger on not. This paper will provide a scenario and the code which explains this process.

Imagine we have a table emp which has an UPDATE trigger on this table.

mysql> CREATE TABLE emp (name varchar(100), salary decimal(5,0));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO emp (name, salary) VALUES ('Tom',1000),('Dick', 1500),('Harry', 1500), ('King',2500);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from emp;
+-------+--------+
| name  | salary |
+-------+--------+
| Tom   |   1000 |
| Dick  |   1500 |
| Harry |   1500 |
| King  |   2500 |
+-------+--------+
4 rows in set (0.00 sec)

Now we create an UPDATE trigger on the emp table which INSERT’s a record into an emp_audit table which tracks all updates on the table.

mysql> CREATE TABLE emp_audit(name varchar(100), salary decimal(5,0), user varchar(100));
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER $$
mysql> CREATE TRIGGER emp_upd
    -> BEFORE UPDATE ON emp
    -> FOR EACH ROW
    -> BEGIN
    ->   INSERT INTO emp_audit(name, salary, user) VALUES (OLD.name, OLD.salary, user());
    -> END;
    -> $$
Query OK, 0 rows affected (0.01 sec)

This trigger will fire each time an update is performed on the table.

mysql> SELECT * FROM emp_audit;
Empty set (0.01 sec)

mysql> UPDATE emp SET salary = 1250 WHERE name = 'Tom';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM emp_audit;
+------+--------+--------------------+
| name | salary | user               |
+------+--------+--------------------+
| Tom  |   1000 | user@localhost     |
+------+--------+--------------------+
1 row in set (0.01 sec)

mysql> DROP TRIGGER emp_upd;
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER $$
mysql> CREATE TRIGGER emp_upd
    -> BEFORE UPDATE ON emp
    -> FOR EACH ROW
    -> BEGIN
    ->   IF @emp_trigger IS NULL OR @emp_trigger = 1 THEN
    ->     INSERT INTO emp_audit(name, salary, user) VALUES (OLD.name, OLD.salary, user());
    ->   END IF;
    -> END;
    -> $$
Query OK, 0 rows affected (0.01 sec)

The new trigger is searching for a session variable @emp_trigger. If this does not exist OR if it does exist and the value is set to 1 then we run the code within the trigger.

mysql> UPDATE emp SET salary = 1750 WHERE name = 'Harry';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM emp_audit;
+-------+--------+--------------------+
| name  | salary | user               |
+-------+--------+--------------------+
| Tom   |   1000 | rcashell@localhost |
| Harry |   1500 | rcashell@localhost |
+-------+--------+--------------------+
2 rows in set (0.00 sec)

Now let’s disable the trigger code by setting the session variable @emp_trigger to 0

mysql> SET @emp_trigger = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE emp SET salary = 1500 WHERE name = 'King';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM emp_audit;
+-------+--------+--------------------+
| name  | salary | user               |
+-------+--------+--------------------+
| Tom   |   1000 | rcashell@localhost |
| Harry |   1500 | rcashell@localhost |
+-------+--------+--------------------+
2 rows in set (0.00 sec)

You can see that the trigger code did not run. To re-enable the trigger code set @emp_trigger to 1

mysql> SET @emp_trigger = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE emp SET salary = salary + 250;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> SELECT * FROM emp_audit;
+-------+--------+--------------------+
| name  | salary | user               |
+-------+--------+--------------------+
| Tom   |   1000 | rcashell@localhost |
| Harry |   1500 | rcashell@localhost |
| Tom   |   1250 | rcashell@localhost |
| Dick  |   1500 | rcashell@localhost |
| Harry |   1750 | rcashell@localhost |
| King  |   1500 | rcashell@localhost |
+-------+--------+--------------------+
6 rows in set (0.00 sec)

Author: admin

MySQL – how to simulate enable/disabled triggers