0
pete_bisby's Avatar, Join Date: Nov 2007
Go4Expert Member
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.
0
alfsys's Avatar, Join Date: Mar 2009
Newbie Member
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
0
pete_bisby's Avatar, Join Date: Nov 2007
Go4Expert Member
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.
0
itsys's Avatar
Banned
Good knowledge of Mysql..............
0
praveen222's Avatar, Join Date: Mar 2010
Newbie Member
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

Last edited by shabbir; 23Mar2010 at 11:40.. Reason: Code blocks
0
pete_bisby's Avatar, Join Date: Nov 2007
Go4Expert Member
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.
0
praveen222's Avatar, Join Date: Mar 2010
Newbie Member
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.
0
pete_bisby's Avatar, Join Date: Nov 2007
Go4Expert Member
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.
0
chengui's Avatar, Join Date: Jun 2011
Newbie Member
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.
0
pete_bisby's Avatar, Join Date: Nov 2007
Go4Expert Member
Yes, you're right - but the important thing here is the structure of the SQL, and not the specifics ...

... nobody's perfect !!