Triggers in MySQL?

Question

Triggers in MySQL?

Navigation

#1 by (2 votes)

3

Greetings,

I have a table called audit that contains the fields:

- codigo_auditoria INT 
 - fechahora_auditoria DATETIME  
 - tabla_auditoria VARCHAR  
 - operacion_auditoria VARCHAR
 - usuario_auditado VARCHAR
 - campo_actualizado VARCHAR
 - valor_antiguo VARCHAR
 - valor_nuevo VARCHAR

and I want to audit different tables in that same table by means of triggers, but I do not know how to do in the fields "campo_actualizado, valor_antiguo y valor_nuevo" the bd autodetecte which field change and what is its old and new value.

NOTE: I do not want to do it in a fixed way because that way I would have to make more tables (one for each table to be audited)

Thank you in advance

mysql trigger

asked by Joshua Mejía 25.03.2017 в 22:44

source

1 answer

Cmbiar og: image with Javascript Postback in ASP net MVC 5 does not work after a JsonResult

score 2 · Accepted Answer

Try something like this:

DELIMITER //

CREATE TRIGGER "nombreX"  AFTER UPDATE ON "tabla a auditar"

FOR EACH ROW 

BEGIN 

    IF NEW."campo" <> OLD."campo" THEN
    INSERT INTO codigo_auditoria SET
        campo_actualizado = "campo",
        valor_antiguo = OLD."campo",
        valor_nuevo = NEW."campo";
    END IF;

END;//

DELIMITER ;