2VAzuAXE

1> -- ----------------------------
2--  Table structure for `UUSE`
3-- ----------------------------
4DROP TABLE IF EXISTS `UUSE`;
5CREATE TABLE `UUSE` (
6  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
7  `METERPOINT_ID` int(10) unsigned DEFAULT NULL,
8  `METER_ID` int(10) unsigned DEFAULT NULL,
9  `SNUMBER` varchar(15) NOT NULL DEFAULT '',
10  `GNAME` varchar(15) NOT NULL DEFAULT '',
11  `UTILITY` varchar(64) NOT NULL DEFAULT '',
12  `PORTION` double DEFAULT '1',
13  PRIMARY KEY (`ID`) USING BTREE,
14  UNIQUE KEY `Index_1` (`GNAME`,`UTILITY`,`SNUMBER`) USING BTREE,
15  UNIQUE KEY `Index_2` (`SNUMBER`,`METERPOINT_ID`) USING BTREE,
16  KEY `METERPOINT_ID` (`METERPOINT_ID`),
17  CONSTRAINT `fk_uuse_groups_id` FOREIGN KEY (`METERPOINT_ID`) REFERENCES `GROUPS` (`ID`) ON UPDATE CASCADE,
18  CONSTRAINT `fk_uuse_meter` FOREIGN KEY (`SNUMBER`) REFERENCES `METER` (`SNUMBER`) ON UPDATE CASCADE
19) ENGINE=InnoDB AUTO_INCREMENT=107354 DEFAULT CHARSET=latin1;
20
21-- ----------------------------
22--  Triggers structure for table UUSE
23-- ----------------------------
24DROP TRIGGER IF EXISTS `UUSE__ai`;
25delimiter ;;
26CREATE TRIGGER `UUSE__ai` AFTER INSERT ON `UUSE` FOR EACH ROW INSERT INTO UUSE_JOURNAL SELECT 'insert', User(), NULL, NULL, NULL, NULL, NOW(), d.* 
27    FROM UUSE AS d WHERE d.ID = NEW.ID
28 ;;
29delimiter ;
30DROP TRIGGER IF EXISTS `UUSE__au`;
31delimiter ;;
32CREATE TRIGGER `UUSE__au` AFTER UPDATE ON `UUSE` FOR EACH ROW BEGIN
33
34    IF NEW.METERPOINT_ID <> OLD.METERPOINT_ID THEN
35        INSERT INTO UUSE_JOURNAL SELECT 'update', User(), 'METERPOINT_ID', OLD.METERPOINT_ID, NEW.METERPOINT_ID, NULL, NOW(), d.*
36        FROM UUSE AS d WHERE d.ID = NEW.ID;
37    END IF;
38
39    IF NEW.METER_ID <> OLD.METER_ID THEN
40        INSERT INTO UUSE_JOURNAL SELECT 'update', User(), 'METER_ID', OLD.METER_ID, NEW.METER_ID, NULL, NOW(), d.*
41        FROM UUSE AS d WHERE d.ID = NEW.ID;
42    END IF;
43
44    IF NEW.SNUMBER <> OLD.SNUMBER THEN
45        INSERT INTO UUSE_JOURNAL SELECT 'update', User(), 'SNUMBER', OLD.SNUMBER, NEW.SNUMBER, NULL, NOW(), d.*
46        FROM UUSE AS d WHERE d.ID = NEW.ID;
47    END IF;
48
49    IF NEW.GNAME <> OLD.GNAME THEN
50        INSERT INTO UUSE_JOURNAL SELECT 'update', User(), 'GNAME', OLD.GNAME, NEW.GNAME, NULL, NOW(), d.*
51        FROM UUSE AS d WHERE d.ID = NEW.ID;
52    END IF;
53
54    IF NEW.UTILITY <> OLD.UTILITY THEN
55        INSERT INTO UUSE_JOURNAL SELECT 'update', User(), 'UTILITY', OLD.UTILITY, NEW.UTILITY, NULL, NOW(), d.*
56        FROM UUSE AS d WHERE d.ID = NEW.ID;
57    END IF;
58
59    IF NEW.PORTION <> OLD.PORTION THEN
60        INSERT INTO UUSE_JOURNAL SELECT 'update', User(), 'PORTION', OLD.PORTION, NEW.PORTION, NULL, NOW(), d.*
61        FROM UUSE AS d WHERE d.ID = NEW.ID;
62    END IF;
63END
64 ;;
65delimiter ;
66DROP TRIGGER IF EXISTS `UUSE__bd`;
67delimiter ;;
68CREATE TRIGGER `UUSE__bd` BEFORE DELETE ON `UUSE` FOR EACH ROW INSERT INTO UUSE_JOURNAL SELECT 'delete', User(), NULL, NULL, NULL, NULL, NOW(), d.* 
69    FROM UUSE AS d WHERE d.ID = OLD.ID
70 ;;