MySQL 5.0 – Triggers

One of the major new pieces of functionality within the 5.0 release of MySQL is something called “Triggers”. Triggers are objects that are related to tables which are executed, or to show where their orginial name came from “fired”, when a triggering event is performed against its associated table. These events are those statements that modify the data within the table that the trigger is associated with – INSERT, UPDATE or DELETE – and can be fired either BEFORE or AFTER row the row is modified – or indeed both.

Triggers are very much like stored procedures, as if you want to execute multiple actions when a trigger is fired you can encapsulate these within a BEGIN … END construct. They also have a couple of extra keywords – OLD and NEW- which refer to the values of the columns before and after the statement was processed, respectively. INSERTs only allow NEW (as no value existed beforehand), UPDATEs allow both NEW and OLD, and DELETEs only allow OLD (as no value exists afterwards).

So back to the old question – “So what use are they to me?” – well let’s take a look at some common scenarios that triggers can be useful for to the DBA. I’ll be using the latest beta version of MySQL (5.0.10), as this brings a couple of new features to the table that I’ll discuss a little later.

One of the questions that gets asked a lot is “How can I tell who changed or deleted a row?”.. With a lack of native database auditing, or spending bunches of time mining log files to see when a DML statement was issued etc. – we could deploy a trigger to a table, that fires AFTER an UPDATE or DELETE statement, that records the values, as well as some other useful info, in to a “log table”. Let’s set up a quick test case:

CREATE TABLE `clients` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(100) default NULL,
`acc_mgr` varchar(10) default NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`)
ENGINE=InnoDB;

INSERT INTO clients (name, acc_mgr) VALUES ('Fredericks','Jim'),
('Walmart','Mark'),
('Radioshack','Mark'),
('Acme','John'),
('Smallco','Jim');

So we have a client table, listing the client, and the account manager that looks after those clients. Everyone knows that sales people are ruthless animals that vicously protect their “patch”! ( 😉 ) You, the DBA, are sick of getting called by the sales team and getting asked “WHO CHANGED [insert company here]’s ACCOUNT MANAGER!?!”, and trying to do the ground work to discover who changed what, when. You can now employ a trigger here! First, let’s create a simple audit/log table:

CREATE TABLE `client_audit` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(100) default NULL,
`old_acc_mgr` varchar(10) default NULL,
`user` varchar(40) default NULL,
`changed` datetime default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

And now a trigger that will fill this table, every time somebody runs an update against the table. You create triggers with the CREATE TRIGGER statement:

CREATE TRIGGER client_accmgr_audit AFTER UPDATE ON clients
FOR EACH ROW
INSERT INTO client_audit (name, old_acc_mgr, user, changed)
VALUES (OLD.name, OLD.acc_mgr, CURRENT_USER(), NOW());

So, Mark gets greedy, he thinks he’s the best salesman since Jesus sold a great story to the masses, and wants to take over “Acme” as his client as well. Let’s see what happens when Mark updates the row:

UPDATE clients SET acc_mgr = 'Mark' WHERE name = 'Acme';

select * from client_audit;

+----+------+-------------+----------------+---------------------+
| id | name | old_acc_mgr | user | changed |
+----+------+-------------+----------------+---------------------+
| 1 | Acme | John | Mark@localhost | 2005-07-31 00:05:56 |
+----+------+-------------+----------------+---------------------+

As you can see, the trigger fired on the update statement, and added the new row to the audit table – we even have the user that updated the row, and when they performed the update – Nice and easy! We could add an “action” column to the audit table, and insert a constant of “Update” along with the insert as well – and then change the trigger above to AFTER DELETE, and record deletes against the table in the same way as well..

Note: The above is only allowed from 5.0.10 onwards I believe, before hand you would need to explicitly issue LOCK TABLES client_audit WRITE; and UNLOCK TABLES; wrapped around the INSERT, within a BEGIN … END construct.

Note: You can now also call procedures from within triggers as well, another limitation that seems to have been lifted with 5.0.10!

Here’s a quick test case, we’ll set up a table for all sales, a procedure to set the commission rate for all people based upon the sale (a quick procedure sample – more on those later as well!), and a trigger that calls the procedure and sets the commission after the insert to the table:

delimiter //

create table sales (id int auto_increment primary key,
item varchar(10),
value decimal(9,2),
acct_mgr varchar(10),
commission decimal(7,2))//

CREATE PROCEDURE commission( value decimal(9,2) )
BEGIN
SET @comm := value / 10;
END;
//

CREATE TRIGGER sales_comm AFTER INSERT ON sales
FOR EACH ROW
BEGIN
CALL commission(NEW.value);
UPDATE sales SET commission = @comm WHERE id = LAST_INSERT_ID();
END;
//

Now lets insert a new sale and see what we get back:

INSERT INTO sales (item, value, acct_mgr) VALUES ('Cheese',300.00,'Mark')//

select * from sales//

+----+--------+--------+----------+------------+
| id | item | value | acct_mgr | commission |
+----+--------+--------+----------+------------+
| 2 | Cheese | 300.00 | Mark | 30.00 |
+----+--------+--------+----------+------------+

Yay – well done MySQL AB for getting this working! The documentation needs updating again to reflect this 😉

Now, quickly, a couple of other things were added in to 5.0.10.. There is now a new INFORMATION_SCHEMA table called INFORMATION_SCHEMA.TRIGGERS which gives us information as such:

select trigger_name, action_statement from information_schema.triggers
where trigger_name = 'sales_comm'//

-----------------------------------------+
| trigger_name | action_statement
-----------------------------------------+
| sales_comm |
begin
call commission(NEW.value);
update sales set commission = @comm where id = last_insert_id();
end |
-----------------------------------------+

You can also get this information, though not as detailed, from the new SHOW TRIGGERS statement.

I think that’s enough for one night! I hope you find this helpful!

7 thoughts on “MySQL 5.0 – Triggers”

  1. Thanks this is exactly what I was looking for and nicely enough cam up on google as the first hit for
    mysql trigger audit.

    R
    S

  2. Hi Mark!

    You are a great writer, you know?

    I had already send a proposal for MySQL User Conference 2006 called “How to Migrate Triggers from Oracle”… I want to present this talk there with you 🙂

    V.K.

  3. Hi Beat,

    Indeed they did change – and thanks for pointing this out. I had noticed that you were having some problems with triggers via your own blog already.

    In fact – I just pushed for this to be changed in the manual. you will now note that the 5.0.17 changelog lists this change as a “Known Issue”. This has also been added to the “Known problems” section for replication:

    http://dev.mysql.com/doc/refman/5.0/en/replication-features.html

    As it breaks replication at the moment when, for instance, replicating from a 5.0.16 master to a 5.0.17 slave.

    http://bugs.mysql.com/bug.php?id=16266

    Thanks for the note!

  4. I use mySQL 5.0.20 with windowsXP and i have following problem.

    ERROR 1442 (HY000): Can’t update tablse ‘sales’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

    ???

  5. Hi all,
    I am new for mysql 5.0.20. can any one give the exaple for creating trigger when we delete a row on particular table.

    need:
    —–
    i want to transfer deleting row from deleting table to temp table through trigger
    while doing delete operation.

Comments are closed.