MySQL Forums
Forum List  »  Triggers

Add record to table with trigger
Posted by: Mike Bosschaert
Date: September 29, 2006 09:26AM

Hi,
I want to implement a sort of audit trails in my application (MySQL 5.0.18).
Therefor I want to use a separate table where all modified data are stored (table audittrail). Now I want this table to also audit itself. I made the following trigger:

CREATE TRIGGER audittrail_audit
AFTER UPDATE ON audittrail
FOR EACH ROW
BEGIN
INSERT DELAYED INTO audittrail SET idAuditField=OLD.id, tableName='audittrail', id_user=0,datemodification=NOW(), fieldname='oldValue', oldValue=OLD.oldValue,newValue=NEW.oldValue;
END;//

The trigger is fired when I modify the field 'oldValue', but I get the error:
ERROR 1165 (HY000): INSERT DELAYED can't be used with table 'audittrail' because it is locked with LOCK TABLES.

Since the trigger should fire after the update, I guessed this should work.
Is there a way to solve this?
Thanks
Mike

Options: ReplyQuote


Subject
Views
Written By
Posted
Add record to table with trigger
1939
September 29, 2006 09:26AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.