0

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$$ 
  • Nothing looks wrong; give it a try. At worst, you might get stuff in the log that is not what you expect. – Rick James May 8 at 0:36
0

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';  

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.