0

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.

7
  • Notice how it says FOR EACH ROW? The trigger scope is a single row, not a table or result set.
    – mustaccio
    Dec 27, 2019 at 18:37
  • @mustaccio I know this and I understand this, including the reason. This is not a point of my question - I search does some "backdoor" exists on the SQL level (strongly, not more deep)...
    – Akina
    Dec 27, 2019 at 19:34
  • Your best shot is probably to generate the trigger via script. The script can inspect the catalog, and construct the trigger
    – Lennart
    Dec 27, 2019 at 23:41
  • @Lennart Yes, creating the procedure which garbages the structures and constructs proper trigger script (or a template which feeds in manual inserting of SET clause) is an option.
    – Akina
    Dec 28, 2019 at 17:12
  • this is after insert trigger, what if you would use natural join with table1 where table1.PKcol = NEW.PKcol? Mar 5, 2020 at 12:43

0

Your Answer

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

Browse other questions tagged or ask your own question.