asked on

Oracle Statement Level Trigger - How to reference the "inserted" table?

I am mainly a SQL Server guy but I am working on a project with Oracle backend. In a statement level trigger how do you reference the rows inserted? I searched extensively it seems this has been shunned. Docs just say :new.Field and it is only for row level triggers. Does that mean in stmt level triggers the inserted rows are just inaccessible? Does Oracle have an equivalent to SQL Server's Inserted table to contain multiple rows I can reference in the trigger?

The actual project is like this: we have a product that I don't access to source code but do have sys and schema owner access to the backend. There are two tables like: parent table Orders (OrderID, OField1, OField2...). Child table OrderDetails (OrderID, ODField1, ODField2...). Each order has anywhere between 20 to 100 rows in OD table.

I want to send an email when a new order is created. The email will include the info in both Orders and OrderDetails tables. If I put a row level after insert trigger on Orders table, I cannot get the info in OrderDetails table because the app creates these rows after the row in Orders is created. If I put a statement level trigger on OD table, how do I find the inserted rows' OrderID so I can find the corresponding row in Orders table?

Alex [***Alex140181***]

Without having a certain kind of status on your order table, which marks the order as "finished" or "readonly" (in terms of being unable to change the order details), there will be no proper way to handle your issue with the help of any kind of trigger and I strongly suggest you do NOT go this way! What if someone adds or deletes a row of order details?! Stuff like this should be dealt with at least layer above triggers (business/application layer).

Qlemo

AFAIK there is indeed no way to directly access changed rows only, because the trigger does not record them.
Workaround: use a row level trigger for Orders, storing IDs in a "queue" table you can read in the OrderDetails statement trigger.

Alex [***Alex140181***]

Workaround: use a row level trigger for Orders, storing IDs in a "queue" table you can read in the OrderDetails statement trigger.

Please: DON'T DO THAT!

I want to send an email when a new order is created.

Technically speaking: when/how is an order being created?!

Andrei Fomitchev

Inserted = :new, Deleted = :old 

Trace change:
if (:old.columnName != :new.columnName) then 
... 
end if;

Open in new window

Trigger should do what you need:

CREATE [ OR REPLACE ] TRIGGER trigger_name
AFTER INSERT
   ON table_name
   [ FOR EACH ROW ]

DECLARE
   -- variable declarations

BEGIN
   -- trigger code

EXCEPTION
   WHEN ...
   -- exception handling

END;

Open in new window

shutterhacker

ASKER

Alex, it is not possible to have a solution at application or business layer that's why it is being done by trigger.

Qlemo, thanks for confirming Oracle doesn't have a way to access inserted rows in statement triggers. At least I don't have to search any more.

Alex [***Alex140181***]

Alex, it is not possible to have a solution at application or business layer that's why it is being done by trigger.

Trust me: it is! Maybe, you got me all wrong... There must be some kind of application (code), which does at some point "insert into...." -> sending emails and such should rather be done at that level (or even "higer")! One major problem with sending emails from triggers is, that it is completely out of transaction! You're going to break this by doing so! It's similar to writing files with utl_file from within triggers! Try to imagine what "happens" when the whole transaction throws an exception at any point (after the trigger, which you might not know about) and is being rolled back?? The email has been sent or the file has been written, but your data doesn't reflect that!

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Oracle Statement Level Trigger - How to reference the &quot;inserted&quot; table?

Oracle Statement Level Trigger - How to reference the "inserted" table?