7

Is it possible to pass the NEW and the OLD tables from a trigger into a procedure in MySQL? I suspect no, since there is no such a datatype as table that a procedure accepts. Any workarounds possible?

Ideally it would look like this:

CREATE TRIGGER Product_log AFTER UPDATE ON Product
  FOR EACH ROW BEGIN
    call logChanges(OLD, NEW);
  END;
user19878
  • 311
  • 5
  • 8

2 Answers2

1

it's not possible because there is no NEW or OLD table. The entire trigger is related to the table - the "new" and "old" refer to the rows and the values they contained before and after the event that was triggered. In other words, your example would be:

call logChanges(OLD.customername, NEW.customername)

You could also save all the OLD data in a history table (which I expect logchanges does anyways), basically being a clone of the production table something like this:

BEGIN
    IF OLD.customer_name != NEW.customer_name
    THEN
            INSERT INTO myTable_chagne_history
                (
                    customer_id    ,
                    customer_name  ,
                    another_field  ,
                    edit_time
                )
                VALUES
                (
                    OLD.customer_id,
                    OLD.customer_name,
                    OLD.another_field  ,
                    NEW.time_edit_was_made
                );
    END IF;
END;
GDP
  • 8,109
  • 6
  • 45
  • 82
1

You can explicitly pass each field:

CALL logChanges(OLD.colA, OLD.colB, NEW.colA, NEW.colB);

Or if logChanges must be sufficiently generic that it can handle such calls from different tables, one could concatenate the field values into a single string using a suitable delimiter (e.g. the unit separator):

CALL logChanges(CONCAT_WS(CHAR(31), OLD.colA, old.colB),
                CONCAT_WS(CHAR(31), NEW.colA, NEW.colB));

Or if data types must be preserved, one could insert the records into a temporary from which logChanges reads.

eggyal
  • 122,705
  • 18
  • 212
  • 237