[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)
Follow us on Twitter for more useful articles.