Home / Questions / 419445
Accepted
Jheyman Mejia
Asked: 2022-01-09 11:26:25 +0800 CST 2022-01-09 11:26:25 +0800 CST

Trigger that runs only when updating certain columns or fields

  • 772

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:

table example

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.

php

1 Answers

  1. Best Answer

    With this proposed solution, the trigger is only fired if the operation_centre or user fields were updated by the UPDATE statement

    DROP TRIGGER IF EXISTS `tic_bd_compus_bu`;
DELIMITER $$
CREATE TRIGGER `tic_bd_compus_butic_bd_compus_bu` BEFORE UPDATE ON `tic_bd_compus` FOR EACH ROW

if old.centro_operacion != new.centro_operacion OR old.usuario != new.usuario THEN
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 IF;
$$
DELIMITER ;

    The only difference is to validate if there are changes in the desired fields, and if there are, perform the INSERT .

    I tried it on 10.5.13-MariaDB-0ubuntu0.21.10.1 and it works.

    • 2