Go4Expert

Go4Expert (http://www.go4expert.com/)
-   PostgreSQL (http://www.go4expert.com/articles/postgresql/)
-   -   Database Transaction Auditing With MySQL and PostgreSQL Using Triggers (http://www.go4expert.com/articles/database-transaction-auditing-mysql-t7252/)

pete_bisby 11Nov2007 19:52

Database Transaction Auditing With MySQL and PostgreSQL Using Triggers
 
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.

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")
);

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.

Code:

-- MySQL
CREATE PROCEDURE `add_tblAudit`(IN pUserId VARCHAR(50), IN pTableName VARCHAR(50), IN pFieldName VARCHAR(50), IN pOldValue LONGTEXT, IN pNewValue LONGTEXT)
BEGIN
INSERT 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$
BEGIN
INSERT 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;

For the purpose of this article a simple table will be constructed, which will allow us to define triggers for the table events.

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

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.

Code:

-- Create INSERT event for tblTest
CREATE OR REPLACE trigger_insert_tblTest AFTER INSERT ON tblTest
FOR EACH ROW
BEGIN
               
IF (NEW.TestVarchar <> '') OR (NEW.TestVarchar IS NOT NULL) THEN
CALL add_tblAudit (USER(), "tblTest", "TestVarchar", "--new record--", NEW.TestVarchar);
END IF;

IF (NEW.TestNumber <> 0) THEN
CALL add_tblAudit (USER(), "tblTest", "TestNumber", "--new record--", NEW.TestNumber);
END IF;

IF (NEW.TestDate <> '') OR (NEW.TestDate IS NOT NULL) THEN
CALL add_tblAudit (USER(), "tblTest", "TestDate", "--new record--", NEW.TestDate);
END IF;
END;$$

-- Create UPDATE event for tblTest
CREATE OR REPLACE trigger_update_tblTest AFTER UPDATE ON tblTest
FOR EACH ROW
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) THEN
CALL add_tblAudit (USER(), "tblTest", "TestVarchar", OLD.TestVarchar, NEW.TestVarchar);
END IF;

IF (NEW.TestNumber <> OLD.TestNumber) THEN
CALL 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) THEN
CALL add_tblAudit (USER(), "tblTest", "TestDate", OLD.TestDate, NEW.TestDate);
END IF;
END;$$

-- Create DELETE event for tblTest
CREATE OR REPLACE trigger_update_tblTest AFTER DELETE ON tblTest
FOR EACH ROW
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)

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:

Code:

CREATE TRIGGER trigger_tblTest AFTER INSERT OR UPDATE OR DELETE ON "tblTest"
FOR EACH ROW EXECUTE PROCEDURE "sp_audit_tblTest";

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.

Code:

CREATE OR REPLACE FUNCTION "sp_audit_tblTest"() RETURNS "trigger" AS$BODY$[INDENT]BEGIN
IF (TG_OP = 'INSERT') THEN
IF (NEW."TestVarchar" <> '') OR (NEW."TestVarchar" IS NOT NULL) THEN
PERFORM "add_tblAudit"(current_user, 'tblTest', 'TestVarchar', '--new record--', NEW."TestVarchar");
END IF;
IF (NEW."TestNumber" <> '0') THEN
PERFORM "add_tblAudit"(current_user, "tblTest", "TestNumber", "--new record--", NEW."TestNumber");
END IF;
IF (NEW."TestDate" <> '') OR (NEW."TestDate" IS NOT NULL) THEN
PERFORM "add_tblAudit"(current_user, "tblTest", "TestDate", "--new record--", NEW."TestDate");
END IF;
RETURN NEW;
ELSIF (TG_OP = 'UPDATE') THEN
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) THEN
PERFORM "add_tblAudit"(current_user, "tblTest", "TestVarchar", OLD."TestVarchar", NEW."TestVarchar");
END IF;
IF (NEW."TestNumber" <> OLD."TestNumber") THEN
PERFORM "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) THEN
PERFORM "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;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;

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.

coderzone 12Nov2007 11:30

Re: Database Transaction Auditing With MySQL and PostgreSQL Using Triggers
 
Good one.

shabbir 1Dec2007 17:58

Re: Database Transaction Auditing With MySQL and PostgreSQL Using Triggers
 
I have reported the article for Nominate your favorite article of the month for November 2007. Add your nominations as well.

shabbir 17Dec2007 10:04

Re: Database Transaction Auditing With MySQL and PostgreSQL Using Triggers
 
Vote for the article for Article of the month for November 2007

ash0279 24Jan2008 08:40

Re: Database Transaction Auditing With MySQL and PostgreSQL Using Triggers
 
I like this one.. seems my PL/SQL knowledge would be still usable for MySQL language extension.

bjdahlstrom 15Nov2008 02:03

Re: Database Transaction Auditing With MySQL and PostgreSQL Using Triggers
 
Pete, you have piqued my curiosity. I need this audit process on my current PostgreSQL application. It really looks like what I need. Is the coding you show above compatible with unix (specifically solaris)? Or is it only for PC platforms only? I really need help on this my expertise on PostgreSQL is extremely limited. Thanks in advance for your assistance. Bernie D.

pete_bisby 16Nov2008 23:49

Re: Database Transaction Auditing With MySQL and PostgreSQL Using Triggers
 
Everything is done within PostgreSQL and MySQL - the platform/operating system is independent. So if you are using Unix/Solaris with PostgreSQL installed, these triggers/functions will work fine.

bjdahlstrom 17Nov2008 22:50

Re: Database Transaction Auditing With MySQL and PostgreSQL Using Triggers
 
Mr. Bisby, thank you for the rapid response. I have one additional question if I can further intrude upon your time.

There is much written on the web concerning storing functions in PostgreSQL, but from my perspective these articles are far too esoteric for my experience level.

I simply want to know how I can see the details of an existing/already stored function in PostgreSQL.

Again, I am sorry for the intrusion and I thank you in advance. BernieD

pete_bisby 18Nov2008 13:23

Re: Database Transaction Auditing With MySQL and PostgreSQL Using Triggers
 
Functions are stored within the pg_proc table - you will need to use the following query:

SELECT * FROM pg_proc WHERE proname='your_function_name';

Alternatively, use either pgAdmin III (application/desktop) or phpPgAdmin (web browser based) - both open source management tools for PostgreSQL and both excellent for viewing functions and stored procedures.

mugnebot 13Mar2009 17:45

Re: Database Transaction Auditing With MySQL and PostgreSQL Using Triggers
 
A solution like this does will not work with statement-based replication where the call to user() returns an empty string when the trigger runs on the slave. Any suggestions for how to work around that "feature"?


All times are GMT +5.5. The time now is 09:24.