I have the following trigger in SQL with MariaDB v10.1 as DBMS
CREATE TRIGGER tic_bd_compus_bu BEFORE UPDATE
ON tic_bd_compus
FOR EACH row
INSERT INTO tic_bd_info
(ant_centro,
ant_usuario,
nuevo_centro,
nuevo_usuario,
f_registro)
VALUES (old.centro_operacion,
old.usuario,
new.centro_operacion,
new.usuario,
NOW())
The triggerworks each time it is done UPDATEin the table tic_bd_compusand its data is saved in the table tic_bd_info.
The problem is that the table where the is done UPDATEhas several fields/columns, and regardless of whether the two fields of the trigger( centro_operacionand usuario) are modified or not, it will "fire". For example, if I change the field of ram memory, or operating system, the trigger will still be launched:
I then need to condition said statement triggerso that it is executed only if one of the two aforementioned fields is changed.
I tried (or invented) to do the following:
CREATE TRIGGER tic_bd_compus_bu before
UPDATE
ON tic_bd_compus FOR each row AS
IF
UPDATE (centro_operacion, usuario) BEGIN
INSERT INTO tic_bd_info
(
ant_centro,
ant_usuario,
nuevo_centro,
nuevo_usuario,
f_registro
)
VALUES
(
old.centro_operacion,
old.usuario,
new.centro_operacion,
new.usuario,
NOW()
)
END
But this clearly doesn't work. I don't know much about triggers, in the chat they tell me to compare if the fields for oldand neware the same by doing the following:
IF NEW.columna <> OLD.columna THEN
Thank you
Update:
I just created this structure, I think it's fine, although at the end it tells me a syntax error:
CREATE TRIGGER tic_bd_compus_bu BEFORE UPDATE
ON tic_bd_compus
FOR EACH row
BEGIN
IF new.centro_operacion <> old.centro_operacion || new.usuario <> old.usuario THEN BEGIN
INSERT INTO tic_bd_info
(ant_centro,
ant_usuario,
nuevo_centro,
nuevo_usuario,
f_registro)
VALUES (old.centro_operacion,
old.usuario,
new.centro_operacion,
new.usuario,
NOW());
END;
END IF;
END
I share SQL from my database for testing purposes. (Includes CREATE DATABASE statement): https://raw.githubusercontent.com/jheymejia/tests/master/tests1.sql
PS: I am working with PHP7, I am open to options with said language.
