Logging database transactions is relatively easy within PostgeSQL and MySQL. Options can be set within the configuration files (postgresql.conf for PostgreSQL and my.ini for MySQL) to enable and record database INSERTs, UPDATEs and DELETEs. But if a client requires to view this information on a regular basis for auditing purposes, without the need for a database administrator, a different method is needed to record individual field changes. In this article we will look at the use of triggers for recording these transactions.
To store the auditing transaction a table will be defined, which hold information relating to the table and field that triggered the event, the old value stored in the field before the triggered event, the new value that will be stored in the field after the triggered event, the user that made the change, and the date/time of the event. An example of the audit table definition follows. Note the differences with the syntax regarding auto-incrementing values for the primary key and declaring the primary key constraint itself.
A function/stored procedure will be used to populate the audit table, which will be referenced within the trigger statements. Again, note the differences how the two procedures reference the values being passed. The two dollar signs at the end of the MySQL procedure is a text delimeter, because semi-colons are used within the procedure itself. This applies to the $BODY$ delimeters used within the PostgreSQL function.
For the purpose of this article a simple table will be constructed, which will allow us to define triggers for the table events.
The syntax for declaring a trigger on a MySQL table is as follows:
Where trigger_name is the name of the trigger, trigger_time is either BEFORE or AFTER the event, trigger_event is either INSERT, UPDATE or DELETE, table_name is the name of the table the trigger is going to be placed on, and trigger_statement is any valid SQL statement(s).
With MySQL a trigger will need to be defined for each of the INSERT, UPDATE and DELETE events on the table. MySQL will distinguish the status of the record before and after the event by using the OLD and NEW keywords within the trigger statements. We will place "--new record--" in the OldValue field for INSERT events, and "--deleted record--" in the NewValue field for DELETE events. You will have to be aware that for the UPDATE trigger event we had to place additional checks in for comparing the OLD and NEW data, especially for fields that already contained a NULL value. If not, an error will appear and nothing will be written to the Audit table.
Now with PostgreSQL it is more of a two-stage process. The trigger event is created against the table you wish to audit, which will call/execute a procedure for comparing the values of the record before and after the event. The trigger definition for PostgreSQL can combine all of the INSERT, UPDATE and DELETE events, whereas in MySQL the definitions for the events had to be declared separately. The syntax for declaring a trigger in PostgreSQL is as follows:
Where trigger_name is the name of the trigger, trigger_event is either INSERT, UPDATE or DELETE, which can be separated by including an OR between each trigger event, table_name is the name of the table the trigger is going to be placed on, and procedure_name (arguments) is the stored procedure that will be executed when an event is triggered.
Because PostgreSQL can allow us to define the three events within the same trigger, the definition can be as follows:
Now that PostgreSQL allows us to define all the events together within the same trigger, it needs to be able to distinguish between each of the events when they transpire. PostgreSQL has a special variable that will allow us to do this - TG_OP - and combine all of the events within one single procedure.
With the triggers in place, on as many tables as you wish, any information being added, updated or deleted, can be recorded into a single table. From here the client will be able to query and view all data changes, and be able to search on which user performed the event, at what specific date/time, and the old and new value of the field that was changed.
To store the auditing transaction a table will be defined, which hold information relating to the table and field that triggered the event, the old value stored in the field before the triggered event, the new value that will be stored in the field after the triggered event, the user that made the change, and the date/time of the event. An example of the audit table definition follows. Note the differences with the syntax regarding auto-incrementing values for the primary key and declaring the primary key constraint itself.
Code:
-- MySQL CREATE TABLE `tblAudit` (`AuditId` int(8) NOT NULL auto_increment, `UserName` varchar(50) NOT NULL, `TableName` varchar(50) NOT NULL, `FieldName` varchar(50) NOT NULL, `OldValue` longtext, `NewValue` longtext, `DTAdded` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`AuditId`)); -- PostgreSQL CREATE TABLE "tblAudit" ("AuditId" int4 NOT NULL DEFAULT nextval('"tblAudit_AuditId_seq"'::regclass), "UserName" varchar(50) NOT NULL, "TableName" varchar(50) NOT NULL, "FieldName" varchar(50) NOT NULL, "OldValue" text, "NewValue" text, "DTAdded" timestamp NOT NULL DEFAULT current_timestamp(), CONSTRAINT "tblAudit_pkey" PRIMARY KEY ("AuditId"));
Code:
-- MySQL CREATE PROCEDURE `add_tblAudit`(IN pUserId VARCHAR(50), IN pTableName VARCHAR(50), IN pFieldName VARCHAR(50), IN pOldValue LONGTEXT, IN pNewValue LONGTEXT) BEGININSERT INTO `tblAudit` (`UserName`, `TableName`, `FieldName`, `OldValue`, `NewValue`) VALUES (pUserName, pTableName, pFieldName, pOldValue, pNewValue); RETURN LAST_INSERT_ID() AS `AuditId`;END;$$ -- PostgreSQL CREATE OR REPLACE FUNCTION "add_tblAudit"("varchar", "varchar", "varchar", text, text) RETURNS int4 AS$BODY$ BEGININSERT INTO "tblAudit" ("AuditId", "UserName", "TableName", "FieldName", "OldValue", "NewValue") VALUES (nextval('"tblAudit_AuditId_seq"'), $1, $2, $3, $4, $5, $6); RETURN currval('"tblAudit_AuditId_seq"');END$BODY$ LANGUAGE 'plpgsql' VOLATILE;
Code:
-- MySQL CREATE TABLE `tblTest` (`TestId` int(8) NOT NULL DEFAULT auto_increment, `TestVarchar` varchar(50), `TestNumber` int(4) NOT NULL default '0', `TestDate` DATE NOT NULL default CURRENT_DATE, PRIMARY KEY (`TestId`)); -- PostgreSQL CREATE TABLE `tblTest` (`TestId` int4 NOT NULL DEFAULT nextval('"tblAudit_AuditId_seq"'::regclass), `TestVarchar` varchar(50), `TestNumber` int4 NOT NULL default '0', `TestDate` DATE NOT NULL default current_date, CONSTRAINT "tblTest_pkey" PRIMARY KEY (`TestId`));
Trigger Definition in MySQL
The syntax for declaring a trigger on a MySQL table is as follows:
Code:
CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW trigger_statement
With MySQL a trigger will need to be defined for each of the INSERT, UPDATE and DELETE events on the table. MySQL will distinguish the status of the record before and after the event by using the OLD and NEW keywords within the trigger statements. We will place "--new record--" in the OldValue field for INSERT events, and "--deleted record--" in the NewValue field for DELETE events. You will have to be aware that for the UPDATE trigger event we had to place additional checks in for comparing the OLD and NEW data, especially for fields that already contained a NULL value. If not, an error will appear and nothing will be written to the Audit table.
Code:
-- Create INSERT event for tblTest CREATE OR REPLACE trigger_insert_tblTest AFTER INSERT ON tblTest FOR EACH ROWBEGIN IF (NEW.TestVarchar <> '') OR (NEW.TestVarchar IS NOT NULL) THENEND;$$ -- Create UPDATE event for tblTest CREATE OR REPLACE trigger_update_tblTest AFTER UPDATE ON tblTest FOR EACH ROWCALL add_tblAudit (USER(), "tblTest", "TestVarchar", "--new record--", NEW.TestVarchar);END IF; IF (NEW.TestNumber <> 0) THENCALL add_tblAudit (USER(), "tblTest", "TestNumber", "--new record--", NEW.TestNumber);END IF; IF (NEW.TestDate <> '') OR (NEW.TestDate IS NOT NULL) THENCALL add_tblAudit (USER(), "tblTest", "TestDate", "--new record--", NEW.TestDate);END IF;BEGIN IF (NEW.TestVarchar <> OLD.TextVarchar) OR (NEW.TestVarchar IS NOT NULL AND OLD.TextVarchar IS NULL) OR (NEW.TestVarchar IS NULL AND OLD.TextVarchar IS NOT NULL) THENEND;$$ -- Create DELETE event for tblTest CREATE OR REPLACE trigger_update_tblTest AFTER DELETE ON tblTest FOR EACH ROWCALL add_tblAudit (USER(), "tblTest", "TestVarchar", OLD.TestVarchar, NEW.TestVarchar);END IF; IF (NEW.TestNumber <> OLD.TestNumber) THENCALL add_tblAudit (USER(), "tblTest", "TestNumber", OLD.TestNumber, NEW.TestNumber);END IF; IF (NEW.TestDate <> OLD.TestDate) OR (NEW.TestDate IS NOT NULL AND OLD.TestDate IS NULL) OR (NEW.TestDate IS NULL AND OLD.TestDate IS NOT NULL) THENCALL add_tblAudit (USER(), "tblTest", "TestDate", OLD.TestDate, NEW.TestDate);END IF;BEGIN CALL add_tblAudit (USER(), "tblTest", "TestVarchar", OLD.TestVarchar, "--deleted record--"); CALL add_tblAudit (USER(), "tblTest", "TestNumber", OLD.TestNumber, "--deleted record--"); CALL add_tblAudit (USER(), "tblTest", "TestDate", OLD.TestDate, "--deleted record--);END;$$
Trigger Definition in PostgreSQL
Now with PostgreSQL it is more of a two-stage process. The trigger event is created against the table you wish to audit, which will call/execute a procedure for comparing the values of the record before and after the event. The trigger definition for PostgreSQL can combine all of the INSERT, UPDATE and DELETE events, whereas in MySQL the definitions for the events had to be declared separately. The syntax for declaring a trigger in PostgreSQL is as follows:
Code:
CREATE TRIGGER trigger_name { BEFORE | AFTER } { trigger_event [ OR ... ] }ON table_name [ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE procedure_name (arguments)
Because PostgreSQL can allow us to define the three events within the same trigger, the definition can be as follows:
Code:
CREATE TRIGGER trigger_tblTest AFTER INSERT OR UPDATE OR DELETE ON "tblTest" FOR EACH ROW EXECUTE PROCEDURE "sp_audit_tblTest";
Code:
CREATE OR REPLACE FUNCTION "sp_audit_tblTest"() RETURNS "trigger" AS$BODY$[INDENT]BEGINIF (TG_OP = 'INSERT') THENEND;$BODY$ LANGUAGE 'plpgsql' VOLATILE;IF (NEW."TestVarchar" <> '') OR (NEW."TestVarchar" IS NOT NULL) THENPERFORM "add_tblAudit"(current_user, 'tblTest', 'TestVarchar', '--new record--', NEW."TestVarchar");END IF;IF (NEW."TestNumber" <> '0') THENPERFORM "add_tblAudit"(current_user, "tblTest", "TestNumber", "--new record--", NEW."TestNumber");END IF;IF (NEW."TestDate" <> '') OR (NEW."TestDate" IS NOT NULL) THENPERFORM "add_tblAudit"(current_user, "tblTest", "TestDate", "--new record--", NEW."TestDate");END IF;RETURN NEW;ELSIF (TG_OP = 'UPDATE') THENIF (NEW."TestVarchar" <> OLD."TextVarchar") OR (NEW."TestVarchar" IS NOT NULL AND OLD."TextVarchar" IS NULL) OR (NEW."TestVarchar" IS NULL AND OLD."TextVarchar" IS NOT NULL) THENPERFORM "add_tblAudit"(current_user, "tblTest", "TestVarchar", OLD."TestVarchar", NEW."TestVarchar");END IF;IF (NEW."TestNumber" <> OLD."TestNumber") THENPERFORM "add_tblAudit"(current_user, "tblTest", "TestNumber", OLD."TestNumber", NEW."TestNumber");END IF;IF (NEW."TestDate" <> OLD."TestDate") OR (NEW."TestDate" IS NOT NULL AND OLD."TestDate" IS NULL) OR (NEW."TestDate" IS NULL AND OLD."TestDate" IS NOT NULL) THENPERFORM "add_tblAudit"(current_user, "tblTest", "TestDate", OLD."TestDate", NEW."TestDate");END IF;RETURN NEW;ELSIF (TG_OP = 'DELETE') {PERFORM "add_tblAudit"(current_user, "tblTest", "TestVarchar", OLD."TestVarchar", '--deleted record--');PERFORM "add_tblAudit"(current_user, "tblTest", "TestNumber", OLD."TestNumber", '--delete record--');PERFORM "add_tblAudit"(current_user, "tblTest", "TestDate", OLD."TestDate", '--deleted record--');RETURN OLD;END IF; RETURN NULL;
shabbir
likes this
