Go4Expert - View Single Post - Database Transaction Auditing With MySQL and PostgreSQL Using Triggers

praveen222

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