kGMkdBMr

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