msgbartop
A chronological documentation test project, nothing serious, really!
msgbarbottom

30 Apr 2007 mySQL and Stored Triggers

Posted by

This is an example on how you an use stored triggers in mySQL

/* test_workaround.sql */
USE test ;
DROP TABLE IF EXISTS `addresses`;
CREATE TABLE `addresses` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `created_on` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_on` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `mac_address` CHAR(17) NOT NULL DEFAULT '00:16:DD:xx:xx:xx', /* see
http://anonsvn.wireshark.org/wireshark/trunk/manuf */
  `serial_number` VARCHAR(60) NOT NULL DEFAULT '',
  `comment` TEXT NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=400 DEFAULT CHARSET=utf8 COMMENT='Assigned MAC
Addresses' ;
/* DROP TRIGGER compute_inserted_mac_address ; */
/* DROP TRIGGER computed_updated_mac_address ; */
DELIMITER //
CREATE TRIGGER compute_inserted_mac_address BEFORE INSERT ON addresses
FOR EACH ROW
    BEGIN
        DECLARE next_id INT(11) UNSIGNED ;
        DECLARE tmp CHAR(6);
        DECLARE right_now TIMESTAMP;
        SELECT AUTO_INCREMENT FROM information_schema.tables
            WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'addresses'
            INTO next_id ;
        SET tmp = RIGHT(CONCAT('000000', UPPER(CONV(12582912 + next_id, 10,
16))), 6) ; /* 0 => ... :C0:00:00 */
        SET right_now = NOW() ;
        SET NEW.mac_address = CONCAT('00:16:DD:', SUBSTRING(tmp, 1, 2), ':',
SUBSTRING(tmp, 3, 2), ':', SUBSTRING(tmp, 5, 2)) ;
        SET NEW.created_on = right_now ;
        SET NEW.updated_on = right_now ;
    END ;
//
DELIMITER ;
DELIMITER //
CREATE TRIGGER computed_updated_mac_address BEFORE UPDATE ON addresses
FOR EACH ROW
    BEGIN
        DECLARE tmp CHAR(6) ;
        SET tmp = RIGHT(CONCAT('000000', UPPER(CONV(12582912 + OLD.id, 10,
16))), 6) ; /* 0 => ... :C0:00:00 */
        SET NEW.mac_address = CONCAT('00:16:DD:', SUBSTRING(tmp, 1, 2), ':',
SUBSTRING(tmp, 3, 2), ':', SUBSTRING(tmp, 5, 2)) ;
        SET NEW.created_on = OLD.created_on ;
        SET NEW.updated_on = NOW();
    END ;
//
DELIMITER ;

Show triggers and insert new data to the database

SHOW TRIGGERS
INSERT INTO `addresses` (serial_number, comment) VALUES ('B0001BKAP8', '');
INSERT INTO `addresses` (serial_number, comment) VALUES ('B0000089JE', '');
SELECT * FROM addresses ;

Tags: , ,

Comments are closed.