In a trigger we may refer to inserted/updated values use NEW alias as a table. But it is NOT a table.
I insert some data into some table, and I need to update some another table during this insert. There is a lof of fields in both tables which matches to each other absolutely (names, datatypes, sizes and so on). So the simplest trigger text (for example, for insert) would be
CREATE TRIGGER tr
AFTER INSERT
ON table1
FOR EACH ROW
UPDATE table2 NATURAL JOIN NEW
SET table2.field = SOMEFUNC(NEW.somefields);
However, NEW is not actually a table, and I expect to get an error message when the trigger is fired:
Table 'DB_name.new' doesn't exist
Question: is there a way or trick to use NEW as a table for to use NATURAL JOIN strongly (not ON, WHERE or USING)?
Assume that the goal is: if the list of matched fields altered (synchronously in both tables) I do not need to edit the trigger.
FOR EACH ROW
? The trigger scope is a single row, not a table or result set.