Mysql – How to write a trigger to add some information before update

MySQLtrigger

I want this trigger to insert some information a table named into orders_log (which has six columns orderNumber, orderdate, customerNumber, status (before update), status (after update), action = ‘update’) every time, after an UPDATE happens into a table called orders.

I have attempted the trigger below, please let me know if this correct:

DELIMITER $$
CREATE trigger after_orders_table_update
after update
on orders
FOR EACH ROW
 BEGIN                
INSERT INTO orders_log table
SET
orderNumber = old.orderNumber,
orderdate = old.orderdate,
customerNumber = new.customerNumber,
status(before update)=old.status,
status(after update)=new.status,
action = 'update';    
END$$

Best Answer

The fieldnames, which contains spaces and parenthesis, must be enclosed by backticks.

In current state they are treated as UDFs, and you must obtain the "undefined function" error (or syntax error).

The trigger contains only one statement, so you do not need in BEGIN-END block and DELIMITER change.

CREATE TRIGGER after_orders_table_update
AFTER UPDATE
ON orders
FOR EACH ROW
INSERT INTO orders_log table
SET orderNumber             = old.orderNumber,
    orderdate               = old.orderdate,
    customerNumber          = new.customerNumber,
    `status(before update)` = old.status,
    `status(after update)`  = new.status,
    action                  = 'update';
Related Question