carters-site.net

MySQL 5.0 Triggers example usage setting fields conditionally.

MySQL 5.0 added Triggers as a feature.  Though the documentation is there and usage is similar to triggers in other database systems, I am putting up my example for those who may be looking to do simple item tracking using a start and end time. In my simple example I want to update some timestamps on my data and deal with handling an item in my databases status.

CREATE TABLE test (
    'id' INT(10) NOT NULL AUTO_INCREMENT,
    'name' VARCHAR(100) DEFAULT NULL,
    'created' DATETIME DEFAULT NULL,
    'updated' DATETIME DEFAULT NULL,
    'start' DATE DEFAULT NULL,
    'end' DATE DEFAULT NULL,
    'status' varchar(10) DEFAULT 'active',
    PRIMARY KEY('id')
);

In this sample table you could add more fields with the data you need to store. For my purposes this will serve just fine for illustrating the triggers I intend to implement. In the table above start and end represent the date item is in an active state.

Triggers:

delimiter //
 
/* Insert Trigger for new items */
CREATE TRIGGER 't_insert_item' BEFORE INSERT ON 'test'
  FOR EACH ROW BEGIN
    SET NEW.updated = NOW();
    SET NEW.created = NOW();
    SET NEW.status = 'active';
 
    IF NEW.start > DATE_FORMAT(NOW(),'%Y-%m-%d) THEN
       SET NEW.status = 'future';
    END IF;
    
    IF NEW.end < DATE_FORMAT(NOW(),'%Y-%m-%d) THEN
       SET NEW.status = 'past';
    END IF;    
END
//
 
/* Update trigger for existing */
CREATE TRIGGER 't_update_item' BEFORE UPDATE ON 'test'
  FOR EACH ROW BEGIN
    SET NEW.updated = NOW();
    SET NEW.status = 'active';
 
    IF NEW.start > DATE_FORMAT(NOW(),'%Y-%m-%d) THEN
       SET NEW.status = 'future';
    END IF;
 
    IF NEW.end < DATE_FORMAT(NOW(),'%Y-%m-%d) THEN
       SET NEW.status = 'past';
    END IF;
END
//
delimiter ; 

With these triggers when I am inserting new items or existing items are being rescheduled I no longer have to worry about timestamping UPDATE statements or adjusting the item’s status in the system. I can also do faster simpler searches on items. It could allow for a one query command to update all item’s status in the system.

UPDATE test SET updated = DATE_FORMAT('%Y-%m-%d') WHERE status <> 'past';

This updates all the active ads and future ads with a new timestamp and updates their status automatically.

I hope this information is of use to someone and if you have questions about triggers in general feel free to comment. I hope to get back to working on combining my 2 previous posts PHP Flickr Parser and ExtJS Image Effects into a combined example.

Tags: code example, MySql, programming, SQL, Triggers

No comments yet.

Leave a comment