Hi Tom,
I have a question about trigger on nested table:
I'd like to know if there is any way to fire a trigger on a nested_table column 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);
It's a legacy software and I cannot modify the procedure that insert/modify records so I want to use a trigger before insert.
Unfortunately I just find that triggers on parent_table don't work in that case because the procedure initializes the nested_table column with an empty collection and then insert a record using the cast operator TABLE.
So when the trigger fires the collection is empty and I obtain ORA-06502
Also trying to reference directly the nested_table using the name defined in the STORE clause fails with ORA-22812
I updated a livesql link to better explain the case.
Thanks in advance
To stop getting ORA-06502 when trying to iterate over an empty nested table, first check it has elements!
This gives a trigger body along the lines of:
begin
if :new.empfiles.count > 0 then
for i in :new.empfiles.first..:new.empfiles.last
loop
if :new.empfiles(i).filename like '%\_pdf.p7m' escape '\' then
:new.empfiles(i).filename:=replace(:new.empfiles(i).filename,'_pdf.p7m','.pdf.p7m');
end if;
end loop;
end if;
end; But this only solves part of the issue. When you have:
insert into table ( select nested_table...
You're not really inserting to the parent table, but the nested table. The triggers don't fire for this.
It's a legacy software and I cannot modify the procedure that insert/modify records so I want to use a trigger before insert.Why not? If you're able to add triggers to the table, surely you can change the procedure too?
Ideally you'd change the procedure to initialize the nested table first, then insert it, e.g.:
create or replace procedure addfile (
l_id number, l_empname varchar2, l_empsurname varchar2, l_myfilename varchar2
)
is
l_myfiles myfile;
begin
l_myfiles := myfile ( myfilet( sysdate, l_myfilename ) );
insert into myreg values ( l_id, l_empname, l_empsurname, l_myfiles );
end;
Though if you do this it's better to remove the trigger altogether and place the file renaming logic in the procedure.
If you're not allowed to change the procedure because reasons, your options are limited.
It's possible you could rename the table and create a view over it with the table's original name. Then create instead of triggers on the view to do this.
The other option is to create a post-insert update routine, that you call to change the nested table data.