SQgvHCzq

1START TRANSACTION;
2
3	/* tables------------------------------ */
4
5	DROP TABLE IF EXISTS `tbl_bla`;
6	CREATE TABLE `tbl_bla` (
7        `bla_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
8        `bla_name` char(50) NOT NULL,
9        `bla_createtime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
10        `bla_changetime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
11        PRIMARY KEY (`bla_id`)
12    ) ENGINE=InnoDB;
13        
14    /* triggers --------------------------- */    
15    
16    DROP TRIGGER IF EXISTS `tr_update_bla`;
17    DELIMITER //
18    CREATE TRIGGER `tr_update_bla`
19        AFTER UPDATE 
20        ON `tbl_bla`
21        FOR EACH ROW
22        BEGIN
23        	/* DO NOTHING */
24        END;
25        //
26	DELIMITER ;
27	
28	DROP TRIGGER IF EXISTS `tr_update_bla_fol`;
29    DELIMITER //
30    CREATE TRIGGER `tr_update_bla_fol`
31        AFTER UPDATE 
32        ON `tbl_bla`
33        FOR EACH ROW FOLLOWS `tr_update_bla`
34        BEGIN
35			/* eek, triggers tr_update_bla again :<  */
36        	UPDATE `tbl_bla` 
37				SET `bla_changetime` = CURRENT_TIMESTAMP 
38				WHERE `bla_id` = OLD.`bla_id`
39			;
40        END;
41        //
42	DELIMITER ;
43		
44	INSERT INTO `tbl_bla` (`bla_name`) 
45		VALUES ("Mr. X");
46		
47	UPDATE `tbl_bla` 
48		SET `bla_name` = "Jürgen" 
49		WHERE `bla_name` <> "Jürgen";
50    
51COMMIT;