Triggers in MySQL 5.0
Posted by: Adam Plocher on April 05, 2006
WHAT ARE TRIGGERS?
Triggers are programmable events that react to queries and reside directly on the database server. Triggers can be executed before or after INSERT, UPDATE or DELETE statements.
WHY USE TRIGGERS?
There are several reasons to use triggers. Triggers can automate a lot of stuff that you may have been doing by hand before. The main reason I use triggers is to maintain the integrity of one table that might rely on another table. For example, if TableA references TableB and a value from TableB gets deleted, you could have a trigger setup to handle the data in TableA. Perhaps the data in TableA should be reset to a generic value or maybe the data should just be deleted completely.
BEFORE WE BEGIN
You may want to run the following CREATE TABLE statements on a test database, if you plan on following along with the examples. The examples use three tables: NewsCategories, News and NewsCount.
CREATING A TRIGGER
Triggers are relatively easy to create. Let's take a look at the basic syntax of the CREATE TRIGGER statement:
This trigger will occur after an insert takes place on the 'News' table. When a new item is added to the News table, an INSERT is triggered, adding the count of news items to the NewsCount table.
There's a couple of basics you need to understand about the syntax. First, the trigger can occur either before or after your query. If you create a trigger that handles INSERTs, you can set it up to run before the database has had the record added, or after. If you want to manipulate data based on a newly inserted AUTO_INCREMENT value, you would have to run it AFTER the INSERT. The NEW keyword acts like a virtual table of the values that are being manipulated on UPDATE and INSERT triggers. For example, NEW.Title corresponds to the Title field that is replacing (or has replaced) the old value. There is also an OLD keyword that is similar to the NEW keyword, only it uses the old value that had been, or is about to be, updated. The OLD keyword can only be applied to UPDATE and DELETE triggers. The following trigger will cause all news items to be deleted when the corresponding news category is deleted and it will demonstrate how to use the OLD keyword:
CHANGING A TRIGGER
To get a list of triggers that reside on your Database server, simply run SHOW TRIGGERS. This will return a list of triggers with all of their attributes, including the statement and timing.
It seems the DROP IF EXISTS statement doesn't work for triggers, like it does for stored procedures and tables. You also can't use the ALTER statement on triggers like you can in Microsoft SQL. Instead we have to drop it, unless it's the first time you're creating it (hopefully MySQL 5.1 will have this fixed).
So if we want to make a change to our trigger from Figure 1, we would have to do this:
Now the trigger will delete all records from the NewsCount table, before adding a new one. This will ensure that the NewsCount table always has only one record.
It might be a good idea to set up another trigger that works the same way, only it gets triggered on DELETE statements. If you don't do this, it will update the count only when new records are added, but if a record gets deleted the count will be wrong. Let's also rename newsCounter to newsCounterUpdate:
OTHER RESTRICTIONS
You can only have one trigger per event, per table. In other words, you can't have two triggers that occur AFTER INSERT ON NEWS, but you can have one BEFORE INSERT ON NEWS and another AFTER INSERT ON NEWS.
You also can't apply a single trigger to multiple events. For example, in Microsoft SQL you can create one trigger that will run after an INSERT or an UPDATE. Unfortunately in MySQL 5.0, you must create an individual trigger for both the INSERT and UPDATE. If you do plan on having the same code run on an INSERT and an UPDATE, it might be better to have your triggers call a stored procedure, so you can keep your code centralized.
REFERENCES
MySQL Triggers
Triggers are programmable events that react to queries and reside directly on the database server. Triggers can be executed before or after INSERT, UPDATE or DELETE statements.
WHY USE TRIGGERS?
There are several reasons to use triggers. Triggers can automate a lot of stuff that you may have been doing by hand before. The main reason I use triggers is to maintain the integrity of one table that might rely on another table. For example, if TableA references TableB and a value from TableB gets deleted, you could have a trigger setup to handle the data in TableA. Perhaps the data in TableA should be reset to a generic value or maybe the data should just be deleted completely.
BEFORE WE BEGIN
You may want to run the following CREATE TABLE statements on a test database, if you plan on following along with the examples. The examples use three tables: NewsCategories, News and NewsCount.
CREATE TABLE NewsCategories
( catID int not null auto_increment, catName varchar(32), primary key(catID));
CREATE TABLE News
( newsID int not null auto_increment, catID int not null, title
varchar(32) not null, txt blob, primary key(newsID));
CREATE TABLE NewsCount
( newsItemCount int );
CREATING A TRIGGER
Triggers are relatively easy to create. Let's take a look at the basic syntax of the CREATE TRIGGER statement:
DELIMITER $$
CREATE TRIGGER newsCounter
AFTER INSERT ON News
FOR EACH ROW BEGIN
INSERT INTO NewsCount (newsItemCount) (SELECT count(*) FROM News);
END;
$$
This trigger will occur after an insert takes place on the 'News' table. When a new item is added to the News table, an INSERT is triggered, adding the count of news items to the NewsCount table.
There's a couple of basics you need to understand about the syntax. First, the trigger can occur either before or after your query. If you create a trigger that handles INSERTs, you can set it up to run before the database has had the record added, or after. If you want to manipulate data based on a newly inserted AUTO_INCREMENT value, you would have to run it AFTER the INSERT. The NEW keyword acts like a virtual table of the values that are being manipulated on UPDATE and INSERT triggers. For example, NEW.Title corresponds to the Title field that is replacing (or has replaced) the old value. There is also an OLD keyword that is similar to the NEW keyword, only it uses the old value that had been, or is about to be, updated. The OLD keyword can only be applied to UPDATE and DELETE triggers. The following trigger will cause all news items to be deleted when the corresponding news category is deleted and it will demonstrate how to use the OLD keyword:
DELIMITER $$
CREATE TRIGGER newsCategoryHandler
AFTER DELETE ON NewsCategories
FOR EACH ROW BEGIN
DELETE FROM News WHERE catID=OLD.catID;
END;
$$
CHANGING A TRIGGER
To get a list of triggers that reside on your Database server, simply run SHOW TRIGGERS. This will return a list of triggers with all of their attributes, including the statement and timing.
SHOW TRIGGERS;
It seems the DROP IF EXISTS statement doesn't work for triggers, like it does for stored procedures and tables. You also can't use the ALTER statement on triggers like you can in Microsoft SQL. Instead we have to drop it, unless it's the first time you're creating it (hopefully MySQL 5.1 will have this fixed).
So if we want to make a change to our trigger from Figure 1, we would have to do this:
DROP TRIGGER newsCounter;
DELIMITER $$
CREATE TRIGGER newsCounter
AFTER INSERT ON News
FOR EACH ROW BEGIN
DELETE FROM NewsCount;
INSERT INTO NewsCount (newsItemCount) (SELECT count(*) FROM News);
END;
$$
Now the trigger will delete all records from the NewsCount table, before adding a new one. This will ensure that the NewsCount table always has only one record.
It might be a good idea to set up another trigger that works the same way, only it gets triggered on DELETE statements. If you don't do this, it will update the count only when new records are added, but if a record gets deleted the count will be wrong. Let's also rename newsCounter to newsCounterUpdate:
-- DROP OLD TRIGGER
DROP TRIGGER newsCounter;
DELIMITER $$
-- CREATE UPDATE TRIGGER
CREATE TRIGGER newsCounterUpdate
AFTER INSERT ON News
FOR EACH ROW BEGIN
DELETE FROM NewsCount;
INSERT INTO NewsCount (newsItemCount) (SELECT count(*) FROM News);
END;
$$
-- CREATE DELETE TRIGGER
CREATE TRIGGER newsCounterDelete
AFTER DELETE ON News
FOR EACH ROW BEGIN
DELETE FROM NewsCount;
INSERT INTO NewsCount (newsItemCount) (SELECT count(*) FROM News);
END;
$$
OTHER RESTRICTIONS
You can only have one trigger per event, per table. In other words, you can't have two triggers that occur AFTER INSERT ON NEWS, but you can have one BEFORE INSERT ON NEWS and another AFTER INSERT ON NEWS.
You also can't apply a single trigger to multiple events. For example, in Microsoft SQL you can create one trigger that will run after an INSERT or an UPDATE. Unfortunately in MySQL 5.0, you must create an individual trigger for both the INSERT and UPDATE. If you do plan on having the same code run on an INSERT and an UPDATE, it might be better to have your triggers call a stored procedure, so you can keep your code centralized.
REFERENCES
MySQL Triggers
Comments temporarily disabled
