how to write an audit trigger in MYSQL to log changes to a table
you could do it like this
CREATE TABLE logevents (
id int(11) NOT NULL AUTO_INCREMENT,
event text NOT NULL,
timeevent timestamp DEFAULT CURRENT_TIMESTAMP,
id_user int(11) NOT NULL DEFAULT 0,
ip varchar(255) NOT NULL DEFAULT 'not indef',
old_value varchar(255) DEFAULT NULL,
new_value int(11) DEFAULT NULL,
typeeventint(11) DEFAULT NULL,
PRIMARY KEY (id)
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci
COMMENT = 'event log';
trigger for update
CREATE
DEFINER = 'root'@'127.0.0.1'
TRIGGER audit_change
AFTER UPDATE
ON tible_for_audit
FOR EACH ROW
BEGIN
IF (@DISABLE_TRIGGERS IS NULL) THEN
IF (OLD.field1 <> NEW.field1) THEN
INSERT INTO log_events
set event='field1',
old_value=OLD.field1,
new_value= NEW.field1;
END IF;
IF (OLD.field2<> NEW.field2) THEN
INSERT INTO log_events
set event='field2' ,
old_value=OLD.field2,
new_value= NEW.field2;
END IF;
IF (OLD.field3<> NEW.field3) THEN
INSERT INTO log_events
set event='field1',
old_value=OLD.field2,
new_value= NEW.field2; END IF;
END IF;
and a trigger for delete, after
CREATE
DEFINER = 'root'@'127.0.0.1'
TRIGGER audit_del
AFTER DELETE
ON table
FOR EACH ROW
IF (@DISABLE_TRIGGERS IS NULL) THEN
INSERT INTO logevents
set event='field2',
old_value=OLD.field2,
idrole= OLD.id,
typeevent=3;
INSERT INTO logevents
set event='field3' ,
old_value=OLD.field3,
idrole= OLD.id,
typeevent=3;
END IF
Comments