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!

MySQL 5.0 – Source Documentation

Wow what a hectic month – just logged on and noticed I haven’t updated the site in a month! I have a part done entry on triggers that I haven’t had time to finish yet, look out for that in a couple of days. Work has been hectic, a lot of work going in to the MySQL Module of NORAD (a system/database monitoring tool that we distribute.. But that’s a story for another day), which I’m responsible for building the collections / window definitions for at the moment.

One of the windows I was building today was for “Open Tables” – sourced from SHOW OPEN TABLES; – yet I wasn’t entirely sure on what all of the output meant. Specifically “Name_locked”. The documentation doesn’t comment on this at all (the documentation for SHOW OPEN TABLES is particularly poor, even noting a “Comment” field that simply doesn’t exist (on 4.1 at least). “In_use” was not entirely clear either at first glance, “Does it mean being selected/inserted etc. from, waiting for a lock..?“.

After a little testing it’s easy to see that “In_use” is incremented for each current table lock, or lock request against each table. For instance, if you “LOCK TABLE t1 WRITE;” in session 1, and then “LOCK TABLE t1 READ;” in session 2, “In_use” will show a value of 2 for t1, even though session 2 is still hanging waiting to actually use the table. Fair enough..

However, through all of the playing around I did, I couldn’t get “Name_locked” to deviate from 0. So I had a chat with Dean (Therion) Ellis to see if he might know, who messed around in the source, and had a chat with some of the other MySQL AB guys, and eventually came to the conclusion all round that Name_locked is used for specific operations such as RENAME TABLE – when they need a totally exclusive lock against the table to perform the administrative function.

This peeked my interest, and to get back to the title of this entry, tired of poking around “blindly” in the source I decided to load up Doxygen, a GPL documentation system for C, C++ etc. projects, and run the MySQL source through it for a better look around. Rather than having copies of this at work and home, I’ve decided to upload it here, and provide a link so that the community can use it as well. You can browse it here.

I’ll try to keep this as up to date as possible, following fully released beta versions. If I don’t have the latest version feel free to send me an email (leithal at cool-tools.co.uk), and I’ll fire an update up. It’s currently about 450mb, with ~9500 files, so we’ll see what bandwidth usage is like as well ;)

So did it help?” – “Yup!

Here’s where the open table list is generated. I’ll let you poke around to see if you can figure out how locked_by_name is set. Some interesting files to look at, to start with, are /sql/lock.cc, and going back to RENAME TABLE /sql/sql_rename.cc

Hopefully it helps all of you other curious people out there!

Posted in Uncategorized /