ztQJ4vru

1START TRANSACTION;
2
3	/*---- clean-up ----------------------------*/
4	
5	DROP TABLE IF EXISTS `tbl_bla_time`;
6	DROP TABLE IF EXISTS `tbl_bla`;
7
8	/*---- tables ------------------------------*/
9
10	CREATE TABLE `tbl_bla` (
11        `bla_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
12        `bla_name` char(50) NOT NULL,
13        `bla_createtime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
14        `bla_changetime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
15        PRIMARY KEY (`bla_id`)
16    ) ENGINE=InnoDB;
17    
18    /* timestamps for tbl_bla */
19	CREATE TABLE `tbl_bla_time` (
20        `bt_bla_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
21        `bt_createtime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
22        `bt_changetime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
23        PRIMARY KEY (`bt_bla_id`)
24    ) ENGINE=InnoDB;
25    
26    ALTER TABLE `tbl_bla_time`
27		ADD CONSTRAINT `fk_bla_time_bla`
28			FOREIGN KEY (`bt_bla_id`)
29				REFERENCES `tbl_bla`(`bla_id`)
30			ON UPDATE CASCADE
31			ON DELETE CASCADE
32	;
33        
34    /*---- triggers ----------------------------*/    
35    
36    DROP TRIGGER IF EXISTS `tr_update_bla`;
37    DELIMITER //
38    CREATE TRIGGER `tr_update_bla`
39        AFTER UPDATE 
40        ON `tbl_bla`
41        FOR EACH ROW
42        BEGIN
43        	UPDATE `tbl_bla_time` 
44				SET `bt_changetime` = CURRENT_TIMESTAMP 
45				WHERE `bt_bla_id` = OLD.`bla_id`
46			;
47        END;
48        //
49	DELIMITER ;
50	
51	/*---- data manipualtion --------------------*/ 
52		
53	INSERT INTO `tbl_bla` (`bla_name`) 
54		VALUES ("Mr. X")
55	;
56	
57	INSERT INTO `tbl_bla_time` (`bt_bla_id`)
58		VALUES (1)
59	;
60		
61	UPDATE `tbl_bla` 
62		SET `bla_name` = "Jürgen" 
63		WHERE `bla_name` <> "Jürgen"
64	;
65    
66COMMIT;