create trigger mysql

Question

create trigger mysql

Navigation

#1 by (1 votes)

0

I have two tables: the table requests and the table logs. Users can interact with the requests table, via a php script that runs a query update and updates the status column. I need to create a trigger that, when running this query update, save the change in the log table.

I used this trigger, but now it does not update either the requested table or the log table:

        DELIMITER $$
        CREATE TRIGGER salva_status 
        BEFORE UPDATE ON pedidos
        FOR EACH ROW BEGIN
        INSERT INTO logs
        SET acao = 'update',
        nPedido = OLD.nPedido,
        status = OLD.status,
        modificadoem = NOW(); END$$
        DELIMITER ;

These are the query for creating the table:

This table is the one that stores the order infos

         CREATE TABLE 'localhost'.'pedidos' ( 'id' INT NOT NULL AUTO_INCREMENT , 'emissaoPed' INT NOT NULL , 
        'nPed' INT NOT NULL , 
        'NrPedido' VARCHAR(20) NOT NULL ,
         'nomeAbrev' VARCHAR(50) NOT NULL , 
        'vlr' FLOAT NOT NULL , 
        'status' VARCHAR(20) NOT NULL , 
        UNIQUE ('id')) ENGINE = InnoDB;

And this is the table that will store the logs:

        CREATE TABLE 'root'.'logs' ( 
        'id' INT NOT NULL AUTO_INCREMENT , 
        'NrPedido' VARCHAR(20) NOT NULL , 
        'antigoStatus' VARCHAR(20) NOT NULL , 
        'novoStatus' VARCHAR(20) NOT NULL ,
         'modificadoem' DATE NOT NULL , 
        'vlr' FLOAT NOT NULL , 
        'status' VARCHAR(20) NOT NULL , 
        UNIQUE ('id')) ENGINE = InnoDB;

mysql trigger

asked by anonymous 22.05.2017 / 16:47

1 answer

I am having difficulty comparing two numbers in php Find ip inside the network [closed]

score 1 · Accepted Answer

Try changing the INSERT:

INSERT INTO logs
        SET acao = 'update',
        nPedido = OLD.nPedido,
        status = OLD.status,
        modificadoem = NOW();

for

 INSERT INTO logs
          (acao, nPedido, status, modificagem)
        VALUES
          ('update', OLD.nPedido, OLD.status, NOW());