I would like to keep track on the INSERT/UPDATE/DELETE action that are performed on some simple tables. There is an example that works fine:
In the example the handling of the delete is done with a flagged field, but I would like that the record is removed from the blog table once deleted.
I modified and removed the delete condition on the INSERT and UPDATE cases and added a new trigger for the delete:
TRIGGER `blog_after_delete` AFTER UPDATE
FOR EACH ROW BEGIN
SET @changetype = 'DELETE';
INSERT INTO audit (blog_id, changetype) VALUES (OLD.id, @changetype);
In the INSERT I changed the value name "NEW.id" into "OLD.id" but the trigger will not work.
Even by changing into a BEFORE trigger it is still not working.
Any idea on how to solve this simple trigger?