Home » SQL & PL/SQL » SQL & PL/SQL » Fire triggers on a nested_table
Fire triggers on a nested_table [message #684813] Fri, 27 August 2021 04:30 Go to next message
ryouga82
Messages: 1
Registered: August 2021
Junior Member
Hello to all.
This is my first post on this forum! Smile
I have a strange question about trigger on nested table:
I'd like to know if there is any way to fire a trigger on a nested_table when the procedure that insert/modify data uses unnesting queries like these:

INSERT INTO TABLE(SELECT nested_table FROM parent_table WHERE id = 1) VALUES
UPDATE TABLE(SELECT nested_table FROM parent_table WHERE id = 1) SET...
DELETE FROM TABLE(SELECT nested_table FROM parent_table WHERE id = 1);

I just find that triggers on parent_table don't work in that case.
Also trying to reference directly the nested_table fails with ORA-22812

Thanks in advance
Re: Fire triggers on a nested_table [message #684816 is a reply to message #684813] Fri, 27 August 2021 10:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68043
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

You can create triggers on nested table only with views as in this example.

Re: Fire triggers on a nested_table [message #684820 is a reply to message #684813] Sun, 29 August 2021 11:33 Go to previous message
Solomon Yakobson
Messages: 3104
Registered: January 2010
Location: Connecticut, USA
Senior Member
ryouga82 wrote on Fri, 27 August 2021 05:30
Hello to all.
I just find that triggers on parent_table don't work in that case.
Yes, and it is all documented - inserting/updating/deleting from nested table doesn't fire main table triggers. Nested table is stored as a separate table (segment to be precise) and main table rows simply have pointers to their respective nested tables. So when we issue insert/update/delete directly against nested table main table isn't modified - all we do is select pointer from main table so obviouly no trigger is fired.

ryouga82 wrote on Fri, 27 August 2021 05:30
Hello to all.
Also trying to reference directly the nested_table fails with ORA-22812
Correct. Nested table name can't be referenced directly in DML since technically it isn't top level object.

SY.

Previous Topic: Oracle Invalid hint
Next Topic: Duplicating Excel "RATE" function in Oracle
Goto Forum:
  


Current Time: Thu Dec 02 13:59:37 CST 2021