0

I have this trigger to update a single field in a journal table after each update, but I need to update ALL columns in related row in journal at once, not by naming each of them and separating by comma, how can I do that?

    DELIMITER //
    CREATE TRIGGER sfo_clone_update_subtotal_invoiced AFTER UPDATE ON sales_flat_order
    FOR EACH ROW BEGIN
        update sales_flat_order_journal set subtotal_invoiced=NEW.subtotal_invoiced where entity_id=new.entity_id;
    END;
DELIMITER ;
AVEbrahimi
  • 17,993
  • 23
  • 107
  • 210

1 Answers1

1

UPDATE syntax allows you to set multiple columns, separated by commas.

update sales_flat_order_journal 
set subtotal_invoiced = NEW.subtotal_invoiced,
    other_column1 = NEW.other_column,
    other_column2 = (/* constant expression, not based on NEW */),
    other_column3 = (SELECT ...scalar expression from some other tables... LIMIT 1),
    updated_at = NOW() 
where entity_id = new.entity_id;

You can access NEW.other_column to get the values of the same row that spawned the trigger.

You can DECLARE local variables in your trigger body to help calculate complex values.

You can use SELECT statements in your trigger body to query values from other rows or other tables, as long as you use a scalar SELECT that returns one row and one column.

If you need a more complex update that's too difficult to do in a trigger, I would do that in application code, not a trigger.

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks bill, is there any way to update all columns at once without naming all of them? – AVEbrahimi Feb 18 '19 at 04:15
  • You would have to type in some expressions for what you want to update them _to_, wouldn't you? And then you ought to be specific about which expression goes with which column, right? Are you just trying to avoid typing? – Bill Karwin Feb 18 '19 at 08:25