Home / Questions / 43589
Accepted
El Cóndor
Asked: 2020-01-12 13:45:04 +0800 CST 2020-01-12 13:45:04 +0800 CST

How to save the information of a row that is going to be deleted in another table with Trigger?

  • 772

I have the tablau:

Nombre  ApellidoP  ApellidoM
Juan    Ruiz       Robles
Pedro   Lopez      Lopez
Luis    Diaz       Ruiz

and the tablelog_users

id     fecha_delete  Nombre
null   null          null

Also I have the following Trigger:

       CREATE DEFINER=`root`@`localhost` TRIGGER PRUEBA_BD_TRIGGER

            BEFORE DELETE ON test.tablau 
            FOR EACH ROW 
            BEGIN 
            INSERT INTO test.log_users 
             (fecha_delete,Nombre) VALUES (now(),Nombre); 

        END

I remove records from the tablaulike this:

delete FROM test.tablau where Nombre = 'Luis';

The trigger does the following on the table log_users:

id     fecha_delete           Nombre
1      2017-01-11 14:49:13

As you can see, it does not save the field Nombreof the deleted row, how can I do that in the Trigger?

mysql

1 Answers

  1. Best Answer

    Use something like this:

    CREATE DEFINER=`root`@`localhost` TRIGGER PRUEBA_BD_TRIGGER  
    BEFORE DELETE ON test.tablau 
    FOR EACH ROW 
    BEGIN 
    INSERT INTO test.log_users (fecha_delete,Nombre) VALUES (now(),OLD.Nombre); 
END

    With OLDindicates the old value of the column. For more information the official documentation of Trigger in MySQL :

    http://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html

    • 3