bRSZwgHF

1CREATE TABLE IF NOT EXISTS `tickets` (
2  `TicketNum` int(11) unsigned NOT NULL,
3  `SubmittedFromIP` tinyblob,
4  `SubmittedFromDevice` varchar(255) DEFAULT NULL,
5  `EntryDate` datetime DEFAULT NULL,
6  `ClosedDate` datetime DEFAULT NULL,
7  `LastName` varchar(50) DEFAULT NULL,
8  `FirstName` varchar(50) DEFAULT NULL,
9  `Email` varchar(50) DEFAULT NULL,
10  `Location` varchar(4) DEFAULT NULL,
11  `InventoryNumber` varchar(50) DEFAULT NULL,
12  `DeviceName` varchar(50) DEFAULT NULL,
13  `Description` text,
14  `Notes` text,
15  `Agent_ID` smallint(5) unsigned NOT NULL DEFAULT '1',
16  `TotalHoursSpent` float NOT NULL DEFAULT '0',
17  `Status` smallint(5) unsigned NOT NULL DEFAULT '1',
18  `Priority` tinyint(4) NOT NULL DEFAULT '0',
19  `LastUpdatedByAgent_ID` smallint(5) unsigned DEFAULT NULL,
20  PRIMARY KEY (`TicketNum`),
21  KEY `ClosedDate` (`ClosedDate`)
22) ENGINE=InnoDB DEFAULT CHARSET=utf8;
23
24CREATE TABLE IF NOT EXISTS `ticketsolutions` (
25  `Entry_ID` int(10) unsigned NOT NULL,
26  `TicketNum` mediumint(8) unsigned DEFAULT NULL,
27  `EntryDateTime` datetime DEFAULT NULL,
28  `HoursSpent` float DEFAULT NULL,
29  `Agent_ID` smallint(5) unsigned DEFAULT NULL,
30  `EntryText` text,
31  `LastUpdatedByAgent_ID` smallint(5) unsigned DEFAULT NULL,
32  PRIMARY KEY (`Entry_ID`),
33  KEY `TicketNum` (`TicketNum`),
34  KEY `EntryDateTime` (`EntryDateTime`),
35  KEY `HoursSpent` (`HoursSpent`),
36  KEY `Rating` (`Rating`)
37) ENGINE=InnoDB DEFAULT CHARSET=utf8;
38
39CREATE TABLE IF NOT EXISTS `tickettagsmap` (
40  `TicketNum` int(11) unsigned NOT NULL,
41  `Tag_ID` int(10) unsigned NOT NULL,
42  `AddedByAgent_ID` smallint(5) unsigned NOT NULL,
43  `DateTimeAdded` datetime NOT NULL,
44  PRIMARY KEY (`TicketNum`,`Tag_ID`),
45  KEY `Tag_ID` (`Tag_ID`),
46  KEY `fk_AgentID` (`AddedByAgent_ID`)
47) ENGINE=InnoDB DEFAULT CHARSET=utf8;
48	
49ModifiedDateTime
50Action
51	
52update_history_main(id int not null auto_increment primary key,
53modify_date datetime not null,
54table_involved varchar(50) not null);
55
56update_history_details (id int not null auto_increment primary key,
57update_history_main_id int not null,
58field_name varchar(100),
59old_value varchar(100),
60new_value varchar(100),
61FOREIGN KEY (update_history_main_id) REFERENCES update_history_main(id)
62ON UPDATE CASCADE ON DELETE CASCADE);
63	
64DELIMITER $$$
65 CREATE TRIGGER afterTicketUpdate AFTER UPDATE ON tickets
66 FOR EACH ROW
67 BEGIN
68     DECLARE main_id int;
69     INSERT INTO update_history_main(modify_date, table_involved) 
70      VALUES(NOW(),'tickets';
71     SELECT LAST_INSERT_ID() INTO main_id;
72
73     IF (new.SubmittedFromDevice != old.SubmittedFromDevice) THEN
74      INSERT INTO update_history_details(update_history_main_id, field_name, 
75      old_value,new_value) 
76     VALUES (main_id, 'SubmittedFromDevice',old.SubmittedFromDevice,
77        new.SubmittedFromDevice);
78     END IF; // ... check all other fields. 
79 END
80$$$
81	
82TicketNum          
83ModifiedDateTime   
84Action
85	
86SET GLOBAL log_output = 'TABLE';
87SET GLOBAL general_log = 'ON';
88	
89select argument from mysql.general_log where argument REGEXP '*INSERT*';