ACDC wroteWe are using SQL Triggers to record a history of attribute changes in business objects. It's working very well and quickly
I have been wanting to insert an audit trail object into all my objects, but have stayed away from doing it because of the perceived overhead. Your solution sounds like an excellent idea, are you able to share how and the steps I would need to follow to get this working in my setup.
Thanks in advance
Here are the basics. We've ended up extending this to included LOTS of attributes. What I show below only records changes to 3 attributes but you simply have to replicate the lines pertaining to individual attributes (SQL Columns).
Good news is lots of attributes does not slow operation of the trigger. Note the 3 comments "EXAMPLES" .show how we have handle Plain Text (Surname), Reference (ps_Workplace) and Yes/No (Financial) attributes differently.
For this example we are recording changes that occur to object called "Person". These changes are records in an object called "History". Example SQL statements are for mySQL.
STEP 1 : Create History Table
This is not a table controlled by AwareIM but later we link it AwareIM so you can view its contents through AwareIM.
So we create this in SQL not Aware IM.
CREATE TABLE `history` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`AttributeName` varchar(100) DEFAULT NULL,
`ValueOld` varchar(500) DEFAULT NULL,
`ValueNew` varchar(500) DEFAULT NULL,
`TimeStampChanged` datetime DEFAULT NULL,
`ObjectID` int(11) DEFAULT NULL,
`ObjectName` varchar(100) DEFAULT NULL
PRIMARY KEY (`ID`)
)
STEP 2 : Create Trigger for Person table
CREATE DEFINER=`root`@`localhost` TRIGGER person_update_trigger AFTER UPDATE ON Person
FOR EACH ROW BEGIN
/****** PLAIN TEXT EXAMPLE *********/
IF (OLD.Surname <> NEW.Surname) THEN
INSERT INTO history
(ObjectName,AttributeName,ValueOld,ValueNew,ObjectID,Officer_FullName,TimeStampChanged,Year_TimeStampChanged)
VALUES("Person","Surname",OLD.Surname, NEW.Surname,OLD.ID,NOW()));
END IF;
/****** REFERENCE EXAMPLE *********/
IF (OLD.ps_Workplace_RID <> NEW.ps_Workplace_RID) THEN
INSERT INTO history
(ObjectName,AttributeName,ValueOld,ValueNew,ObjectID,TimeStampChanged)
SELECT "Person","Workplace",`wOld`.`Name`,`wNew`.`Name`,`p`.`ID`,NOW())
FROM
((`Person` `p`
LEFT JOIN `Workplace` `wOld` ON ((OLD.ps_Workplace_RID = `wOld`.`ID`)))
LEFT JOIN `Workplace` `wNew` ON ((NEW.ps_Workplace_RID = `wNew`.`ID`)))
WHERE `p`.`ID` = OLD.ID;
END IF;
/******** YES/NO EXAMPLE ***********/
IF (OLD.Financial <> NEW.Financial) THEN
INSERT INTO history
(ObjectName,AttributeName,ValueOld,ValueNew,ObjectID,TimeStampChanged)
VALUES("Person","Financial",
(CASE OLD.Financial
WHEN '1' THEN 'Yes'
WHEN '0' THEN 'No'
END),
(CASE NEW.Financial
WHEN '1' THEN 'Yes'
WHEN '0' THEN 'No'
END),
ID,NOW());
END IF;
END
STEP 3 : Make History available to AwareIM Queries
In the Configuration Tool create an object (any name will do).
Change Persistence to "Database : existing external" and provide settings that point to your database and the "History" table.
STEP 4 : Extend to Record User that made changes
Steps above do not include data identifying user that made the changes.....we tried to assign LoggedInRegularUser to the Person object but got inconsistent results (only changes made by rules were being picked up). We ended up extending this SQL to find the active user from another source. Our final trigger (with more attributes recorded in History including the 'User') is below. You would have to extend the columns in History and have a separate object for UserActivity to support this trigger .
CREATE DEFINER=`root`@`localhost` TRIGGER person_update_trigger AFTER UPDATE ON Person
FOR EACH ROW BEGIN
/****** Surname *********/
IF (OLD.Surname <> NEW.Surname) THEN
INSERT INTO history
(ObjectName,AttributeName,ValueOld,ValueNew,ObjectID,Officer_FullName,TimeStampChanged,Year_TimeStampChanged)
VALUES("Person","Surname",OLD.Surname, NEW.Surname,OLD.ID,@LIRU,NOW(),YEAR(Now()));
END IF;
/********** ps_Workplace ********/
IF (OLD.ps_Workplace_RID <> NEW.ps_Workplace_RID) THEN
INSERT INTO history
(ObjectName,AttributeName,ValueOld,ValueNew,ObjectID,Officer_FullName,TimeStampChanged,Year_TimeStampChanged)
SELECT "Person","Workplace",`wOld`.`Name`,`wNew`.`Name`,`p`.`ID`,@LIRU,NOW(),
YEAR(Now())
FROM
((`Person` `p`
LEFT JOIN `Workplace` `wOld` ON ((OLD.ps_Workplace_RID = `wOld`.`ID`)))
LEFT JOIN `Workplace` `wNew` ON ((NEW.ps_Workplace_RID = `wNew`.`ID`)))
WHERE `p`.`ID` = OLD.ID;
END IF;
/******** Financial ***********/
IF (OLD.Financial <> NEW.Financial) THEN
INSERT INTO history
(ObjectName,AttributeName,ValueOld,ValueNew,ObjectID,Officer_FullName,TimeStampChanged,Year_TimeStampChanged)
VALUES("Person","Financial",
(CASE OLD.Financial
WHEN '1' THEN 'Yes'
WHEN '0' THEN 'No'
END),
(CASE NEW.Financial
WHEN '1' THEN 'Yes'
WHEN '0' THEN 'No'
END),
ID,@LIRU,NOW(),YEAR(Now()));
END IF;
END
Note also the above only records changes to existing objects. So no record is created when an object is first created. You can add an extra INSERT trigger to do this.
