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` (
[INDENT]`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`)[/INDENT]
);

-- PostgreSQL
CREATE TABLE "tblAudit" (
[INDENT]"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")[/INDENT]
);

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
[INDENT]INSERT INTO `tblAudit` (`UserName`, `TableName`, `FieldName`, `OldValue`, `NewValue`)
VALUES (pUserName, pTableName, pFieldName, pOldValue, pNewValue);
RETURN LAST_INSERT_ID() AS `AuditId`;[/INDENT]
END;$$

-- PostgreSQL
CREATE OR REPLACE FUNCTION "add_tblAudit"("varchar", "varchar", "varchar", text, text) RETURNS int4 AS$BODY$
BEGIN
[INDENT]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"');[/INDENT]
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` (
[INDENT]`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`)[/INDENT]
);

-- PostgreSQL
CREATE TABLE `tblTest` (
[INDENT]`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`)[/INDENT]
);

Trigger Definition in MySQL

The syntax for declaring a trigger on a MySQL table is as follows:

Code:

CREATE TRIGGER [I]trigger_name[/I] [I]trigger_time[/I] [I]trigger_event[/I] ON [I]table_name[/I]
FOR EACH ROW [I]trigger_statement[/I]

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
[INDENT]BEGIN
		
IF (NEW.TestVarchar <> '') OR (NEW.TestVarchar IS NOT NULL) THEN
[INDENT]CALL add_tblAudit (USER(), "tblTest", "TestVarchar", "--new record--", NEW.TestVarchar);[/INDENT]
END IF;

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

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

END;$$

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

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

-- Create DELETE event for tblTest
CREATE OR REPLACE trigger_update_tblTest AFTER DELETE ON tblTest
FOR EACH ROW
[INDENT]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--);
[/INDENT]		
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 [I]trigger_name[/I] { BEFORE | AFTER } { [I]trigger_event[/I] [ OR ... ] }ON [I]table_name[/I] [ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE [I]procedure_name (arguments)[/I]

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
[INDENT]IF (TG_OP = 'INSERT') THEN
[INDENT]IF (NEW."TestVarchar" <> '') OR (NEW."TestVarchar" IS NOT NULL) THEN
[INDENT]PERFORM "add_tblAudit"(current_user, 'tblTest', 'TestVarchar', '--new record--', NEW."TestVarchar");[/INDENT]
END IF;[/INDENT]
[INDENT]IF (NEW."TestNumber" <> '0') THEN
[INDENT]PERFORM "add_tblAudit"(current_user, "tblTest", "TestNumber", "--new record--", NEW."TestNumber");[/INDENT]
END IF;[/INDENT]
[INDENT]IF (NEW."TestDate" <> '') OR (NEW."TestDate" IS NOT NULL) THEN
[INDENT]PERFORM "add_tblAudit"(current_user, "tblTest", "TestDate", "--new record--", NEW."TestDate");[/INDENT]
END IF;[/INDENT]
[INDENT]RETURN NEW;[/INDENT]
ELSIF (TG_OP = 'UPDATE') THEN
[INDENT]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
[INDENT]PERFORM "add_tblAudit"(current_user, "tblTest", "TestVarchar", OLD."TestVarchar", NEW."TestVarchar");[/INDENT]
END IF;[/INDENT]
[INDENT]IF (NEW."TestNumber" <> OLD."TestNumber") THEN
[INDENT]PERFORM "add_tblAudit"(current_user, "tblTest", "TestNumber", OLD."TestNumber", NEW."TestNumber");[/INDENT]
END IF;[/INDENT]
[INDENT]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
[INDENT]PERFORM "add_tblAudit"(current_user, "tblTest", "TestDate", OLD."TestDate", NEW."TestDate");[/INDENT]
END IF;[/INDENT]
[INDENT]RETURN NEW;[/INDENT]
ELSIF (TG_OP = 'DELETE') {
[INDENT]PERFORM "add_tblAudit"(current_user, "tblTest", "TestVarchar", OLD."TestVarchar", '--deleted record--');[/INDENT]
[INDENT]PERFORM "add_tblAudit"(current_user, "tblTest", "TestNumber", OLD."TestNumber", '--delete record--');[/INDENT]
[INDENT]PERFORM "add_tblAudit"(current_user, "tblTest", "TestDate", OLD."TestDate", '--deleted record--');[/INDENT]
[INDENT]RETURN OLD;[/INDENT]
END IF;
RETURN NULL;[/INDENT]
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.

 

Good one.

 

I like this one.. seems my PL/SQL knowledge would be still usable for MySQL language extension.

 

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.

 

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.

 

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

 

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.

 

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"?

 

Have each record within the table that is being audited have "user_name_created" and "user_name_modifed" fields. This information would then be passed through the trigger, which can then be recorded in the auditing table.

 

Hi

I try this code:
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;$$

And the line "RETURN LAST_INSERT_ID() AS `AuditId`;" gives an error 1064
Any help?

Alejandro

 

You don't need to name/alias the returning value [LAST_INSERT_ID()].

Also check the version of MySQL you are using - error 1064 is when a reserved word that was allowed in a previous version is now deprecated. Maybe using aliasing on a returned value is not allowed.

 

Hi,

Good knowledge of Mysql..............

 

Code:

CREATE  trigger_insert_tbltest AFTER INSERT ON tbltest
FOR EACH ROW

    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;

The above code is not working in mysql 5.4.1. Especially it doesn't support Begin and END statements. and how can write if conditions.
My intension is to find the audit details of delete, insert or modify records in mysql.
I have successfully done with only updation, but my intension is to track the audit details when delete, insert and updation is done.

The below code for tracking the updation records.

Code:

  CREATE  TRIGGER before_employee_update 
 BEFORE UPDATE ON employees
 FOR EACH ROW 
 INSERT INTO employees_audit
 SET action = 'update OR insert OR delete OR select',
 employeeNumber = OLD.employeeNumber,
 lastname_old = OLD.lastname,
 lastname_new = NEW.lastname,
 user = CURRENT_USER(),
 changedon = NOW(); 

please provide me of tracking of insertion, deletion and updation, related with above code.

Thanks in Advance,

Praveen

 

Firstly, MySQL 5.4.1 does support BEGIN and END within stored procedures - it has to because if you use mutliple SQL statements MySQL has to differentiate between the stored procedure and the SQL statement itself. So, the first chunk of code you have provided should work fine when you put back in the BEGIN and END keywords. If you're only using a single statement, which is what you have done in your second chunk of code, you don't need the BEGIN and END keywords - they are required for multiple statements.

You're also missing the keyword TRIGGER from your CREATE statement.

You will need to use a different delimiter when entering a stored procedure containing multiple statements. When I use phpMyAdmin I use the semi-colon for separating the multiple statements and use the pipe symbol ( | ) at the end of the stored procedure to tell MySQL that this is the end of the whole statement.

You haven't included the actual trigger code in your question - if no-one can review your SQL statement it does make it quite difficult for anyone to provide any help. Bear in mind, however, that the trigger events have to be declared seaprately - you cannot combine INSERT, UPDATE and DELETE events into a single trigger in MySQL.

I think the first port of call would be to go back to the MySQL Manual and look at the syntax for creating triggers (http://dev.mysql.com/doc/refman/5.4/en/create-trigger.html). Then, if you're still stuck, come back and post your actual trigger statements on the forum.

 

Thanks for giving reply, so the if end if; keywords also not working. In which version of MYSQL supports stored procedures. I have a doubt regarding triggers that, I am created seperate triggers for insert, update, delete, but what is the difference between after and before for all above statements (insert/update/delete). and also after i modify two values in one table, shows seperate modifications in audit log table (with different rows).

Thanks in Advance,

Praveen.

 

The IF ... END IF statements do work in MySQL stored prodecures, since the start of version 5 (probably before then). IF ... THEN ... ELSE statements are a fundamental part of controlling the process flow within any programming language - SQL, in all of its different guises, is no different.

You have to create individual triggers for INSERT, UPDATE and DELETE, because MySQL does not allow the grouping of these actions together; unlike PostgreSQL, which does. I don't know why - it is just the way MySQL performs. Is it a good or bad thing? ... open up a debate and see what other developer's viewpoint is.

The BEFORE and AFTER clauses within the trigger tell it when to run - BEFORE or AFTER the action. This was mentioned in the MySQL Manual (http://dev.mysql.com/doc/refman/5.4/en/create-trigger.html) I put in my previous post - look at the "trigger_time" definition.

The whole purpose of the auditing table was to show the inserts, updates and deletes of information relating to a specific database table. It shows traceability on a specific field on a row, as opposed to recording the whole row. You can, if you wanted to, write the whole row of information on creation/update within an audit table, showing the maturity/version control of a row. The only downside to this method would be that you would need a separate audit table for each database table in your database; unless you serialised the information before writing it to the audit table ... but this is probably going slgihtly off-piste!!

The article shows "one" method of auditing transactions using triggers - there are plenty out there. It is up to the database developer to review all possibilities and implement a solution that meets the requirements of the project.

Oh, and just one more comment - you are aware that MySQL version 5.4.1 is still classed as a "development" version. For stability purposes (certainly if you're placing this database into a production environment) I would recommend downgrading to 5.1.45. However, 5.4.1 still supports stored procedures (with or without BEGIN and END), IF-THEN-ELSE statements and triggers.

 

I'm currently writing this up, I haven't gotten to the testing it yet but I believe their are two errors in your script for the mysql section.

The first is in the created procedure
Quote:

Code:

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

Since in the parameter you use in add_tblAudit is 'pUserId' should the value in the insert statement also be pUserid and not pUserName? (Although I see you are using User() as the passed variable perhaps this is why?)

Secondly in your trigger:

Code:

-- Create INSERT event for tblTest
CREATE OR REPLACE trigger_insert_tblTest AFTER INSERT ON tblTest
FOR EACH ROW
BEGIN

Code:

-- Create UPDATE event for tblTest
CREATE OR REPLACE trigger_update_tblTest AFTER UPDATE ON tblTest
FOR EACH ROW
BEGIN

Code:

-- Create DELETE event for tblTest
CREATE OR REPLACE[COLOR="Red"] trigger_update_tblTest[/COLOR] AFTER DELETE ON tblTest
FOR EACH ROW
BEGIN

I believe you are deleting your update trigger with the delete trigger in your example.

 

Yes, you're right - but the important thing here is the structure of the SQL, and not the specifics ...

... nobody's perfect !!