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

pete_bisby 14Mar2009 16:49

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

alfsys 14Mar2009 18:00

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

pete_bisby 15Mar2009 17:08

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

itsys 21May2009 10:29

Hi,
 
Good knowledge of Mysql..............

praveen222 22Mar2010 18:20

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

pete_bisby 23Mar2010 13:30

Re: Database Transaction Auditing With MySQL and PostgreSQL Using Triggers
 
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/...e-trigger.html). Then, if you're still stuck, come back and post your actual trigger statements on the forum.

praveen222 23Mar2010 16:30

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

pete_bisby 24Mar2010 13:17

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

chengui 15Jun2011 05:47

Re: Database Transaction Auditing With MySQL and PostgreSQL Using 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 trigger_update_tblTest AFTER DELETE ON tblTest
FOR EACH ROW
BEGIN

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

pete_bisby 15Jun2011 13:03

Re: Database Transaction Auditing With MySQL and PostgreSQL Using Triggers
 
Yes, you're right - but the important thing here is the structure of the SQL, and not the specifics ...

... nobody's perfect !!

chengui 16Jun2011 00:17

Re: Database Transaction Auditing With MySQL and PostgreSQL Using Triggers
 
Yes and a wonderful structure it is!! I highly appreciate having it available. :crazy: I'm happy to have access to it and alter it to suit my needs :)
So happy in fact I am compelled to share a little helper script that I created for my own goals to help automatically create the script needed, if it helps anyone.
This script will look at all the tables in your database and also go through all the column names and generate the script needed to make the update, delete, and insert triggers. The only catch is that you have to establish your own connection and create your own query result fetchers from your mysql database, I use the function called get_result to do this it gets both an associative and numeric array. You'll need to replace any calls to the database with your own.

Code:

<?php
        include_once('mysql_connection_doc.php'); //You'll have to make your own, I used Doctrine2 dbal but you can just as much use regular mysql.

        $tables = get_result('show tables'); //the first query is 'show tables' and then $tables becomes an array with the results


$final = "delimiter $$\n";

$final .= "
CREATE TABLE IF NOT EXISTS `Audit` (
`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`)
);
/n";


$final .= "
DROP PROCEDURE IF EXISTS `add_Audit`;
CREATE PROCEDURE `add_Audit`(
                                                                                IN pUserName VARCHAR(50),
                                                                                IN pTableName VARCHAR(50),
                                                                                IN pFieldName VARCHAR(50),
                                                                                IN pOldValue LONGTEXT,
                                                                                IN pNewValue LONGTEXT
                                                                                )
BEGIN
INSERT INTO `Audit` (`UserName`, `TableName`, `FieldName`, `OldValue`, `NewValue`)
VALUES (pUserName, pTableName, pFieldName, pOldValue, pNewValue);

END$$

" ;


       
        echo "<strong>Tables Applied to: </strong><br>";
        foreach ($tables['result'] AS $tbl_val){            //loops through result array established at get_result
        $sqql = "Show columns from " . $tbl_val[0];  //SECOND SQL statement, put the columns in another associative array
        $columns = get_result($sqql);       
        if (                                                            //Tables you do not wish to Audit
                $tbl_val[0] != 'Audit'
                AND
                $tbl_val[0] != 'tbl_1'
                AND
                $tbl_val[0] != 'tbl_2'
                AND
                $tbl_val[0] != 'tbl_3'
        ){
        echo "- {$tbl_val[0]}<br>"; //This is the table name of
       
       
                $final .= "-- {$tbl_val[0]}\n"; // Table name
               
                $final .= "-- Create INSERT event for {$tbl_val[0]}\n";
                $final .= "DROP TRIGGER IF EXISTS trigger_insert_{$tbl_val[0]};\n";
                $final .= "CREATE TRIGGER trigger_insert_{$tbl_val[0]} AFTER INSERT ON {$tbl_val[0]} \n";
                $final .= "FOR EACH ROW\n";
                $final .= "BEGIN\n";
                $final .= "\n";
                foreach ($columns['result'] as $c_key => $c_val){
                        $final .= "IF(NEW.{$c_val['Field']} <> '') OR (NEW.{$c_val['Field']} IS NOT NULL) THEN\n";
                        $final .= "CALL add_Audit (user(), \"{$tbl_val[0]}\", \"{$c_val['Field']}\", \"--new record--\", NEW.{$c_val['Field']});\n";
                        $final .= "END IF;\n";
                        $final .= "\n";
                }               
                $final .= "END;$$\n";
                $final .= "\n";
       
       
                $final .= "-- Create UPDATE event for {$tbl_val[0]}\n";
                $final .= "DROP TRIGGER IF EXISTS trigger_update_{$tbl_val[0]};\n";
                $final .= "CREATE TRIGGER trigger_update_{$tbl_val[0]} AFTER UPDATE ON {$tbl_val[0]} \n";
                $final .= "FOR EACH ROW\n";
                $final .= "BEGIN\n";
                $final .= "\n";
                foreach ($columns['result'] as $c_key => $c_val){
                        $final .= "IF (NEW.{$c_val['Field']} <> OLD.{$c_val['Field']}) OR (NEW.{$c_val['Field']} IS NOT NULL AND OLD.{$c_val['Field']} IS NULL) OR (NEW.{$c_val['Field']} IS NULL AND OLD.{$c_val['Field']} IS NOT NULL) THEN\n";
                        $final .= "CALL add_Audit (user(), \"{$tbl_val[0]}\", \"{$c_val['Field']}\", OLD.{$c_val['Field']}, NEW.{$c_val['Field']});\n";
                        $final .= "END IF;\n";
                        $final .= "\n";
                }               
                $final .= "END;$$\n";
                $final .= "\n";
       
       
                $final .= "-- Create DELETE event for {$tbl_val[0]}\n";
                $final .= "DROP TRIGGER IF EXISTS trigger_delete_{$tbl_val[0]};\n";               
                $final .= "CREATE TRIGGER trigger_delete_{$tbl_val[0]} AFTER DELETE ON {$tbl_val[0]} \n";
                $final .= "FOR EACH ROW\n";
                $final .= "BEGIN\n";
                $final .= "\n";
                foreach ($columns['result'] as $c_key => $c_val){
                        $final .= "CALL add_Audit (user(), \"{$tbl_val[0]}\", \"{$c_val['Field']}\", OLD.{$c_val['Field']}, \"--deleted record--\");\n";
                }               
                $final .= "END;$$\n";
                $final .= "\n";
                $final .= "-- {$tbl_val[0]} END\n";
                $final .= "\n\n";

       
       
       
       

               
               
               
               
        }
        }
        $final .= "delimiter ;\n";
?>
<?php echo "<hr><pre> $final </pre>";

To get an example of how the query results need to look you can comment out the parts that say get_result and replace it with the following to get an idea of what the result set should look like. (Also consider altering the foreach loop if you need to as I go a little crazy with arrays)
Code:

$tables = array(
        'result' => array(
                                0 => array(0=>'Table_One'),
                                1 => array(0=>'Table_Two'),
                                2 => array(0=>'Table_Three'),
                                3 => array(0=>'Table_Four'),
                                4 => array(0=>'Table_Five'),
                                5 => array(0=>'Table_Six'),
                                5 => array(0=>'Audit'),
                                5 => array(0=>'tbl_1'),
                                5 => array(0=>'tbl_2'),
                                5 => array(0=>'tbl_3')

                                )
        );
$columns = array(
                'result'=> array(
                        0 => array('Field'=>'column1'),
                        1 => array('Field'=>'column2'),
                        2 => array('Field'=>'column3'),
                        3 => array('Field'=>'column4'),
                        4 => array('Field'=>'column5'),
                        5 => array('Field'=>'column6'),
                        6 => array('Field'=>'column7'),
                        7 => array('Field'=>'column8')
                       
                )
                       
               
        );

If you decide to use this script you should note that I changed my Audit table name from tbl_Audit to just Audit so you'll have to match it with the original script. Anyways this is just a skeleton feel free to modify to your heart's content.

It's fun how pete's code was written so long ago yet is still valid today.

pete_bisby 16Jun2011 12:36

Re: Database Transaction Auditing With MySQL and PostgreSQL Using Triggers
 
Instead of manually entering the table names, you could use SHOW TABLES IN [database_name] to get the table names from your database. Once you have the tables stored in an array you can then use SHOW COLUMNS IN [table_name] to retrieve the column names. Once the column names have been stored in another array, you should be able to automate the whole process. This is for MySQL only - for PostgreSQL you would need to query pg_class, pg_attribute and pg_type to get the correct tables, columns and data types.

P.S. the index for your $tables array is slightly wrong - you've used index 5 too many times ...

... like I said, nobody's perfect !! :-)


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