Is it possible to pass NEW and OLD tables from a trigger to a procedure in MySQL?

Question

Is it possible to pass NEW and OLD tables from a trigger to a procedure in MySQL? I suspect not, because there is no such type of data as the table that the procedure accepts. Possible workarounds?

Ideally, it would look like this:

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

Answer 1

this is not possible because there is no new or old table. The entire trigger is associated with the table - “new” and “old” refer to the rows and values ​​that they contained before and after the triggered event. In other words, your example:

 call logChanges(OLD.customername, NEW.customername) 

You can also save all OLD data in the history table (which I expect to make changes), basically being a clone of the production table 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; 

Answer 2

You can explicitly pass each field:

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

Or, if logChanges needs to be generic enough to handle such calls from different tables, you can combine the field values ​​into one row using a suitable separator (for example, a block 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 stored, you can insert records into the temporary one from which logChanges reads.