This content has been marked as final. Show 7 replies
A row-level trigger on TableAR cannot, in general, query TableAR or call anything that queries TableAR.
In your case, it would appear that you could just reference :new.itemId rather than calling the function. It's unclear to me, though, how it makes sense to assign an itemId to a status column-- it seems unlikely that itemId is a varchar2(1) so your function (or at least the function call) doesn't seem to make sense. That makes me suspect that there is more to the problem than you are providing here. The fact that you have a local variable sqlStmt in a trigger, implying that you're doing dynamic SQL in a trigger, has me even more concerned.
Thank you All for your input. I appreciate that.
Sorry to be little vague.
So, Let me add more details. The Function Funcion1 has many select statement from number of tables and those values are used to decide whether the return value for this function is 'Y' or 'N'.
I have cleaned up the code a bit to remove irrelevant.
DROP TRIGGER TableAR_BU; CREATE OR REPLACE TRIGGER TableAR_BU BEFORE UPDATE ON TableAR_BU REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW declare Status varchar2(1); Status := Function1(:new.Id); if Status = 'Y' then :new.ItemFlag := 11; end if; End; / function Function1(al_Id in TableAR.Id%type) RETURN varchar2 is lsItemId TableAR.itemId%type; lsValid varchar2(1); Begin lsValid :='N' Select itemId into lsItemId from TableAR where id = al_Id; Select key, name into lsKey, lsName from tableB where id = al_id; .................. ................ If lsItemId = "SomeValue" and lsKey = 22 and ..... IsValid :='Y' End if; return IsValid; Exception When others then return IsValid; End Function1;
Edited by: Eclipse01 on May 1, 2012 11:58 AM
The problem remains that a row-level trigger on TableAR cannot query TableAR or call anything that does query TableAR. In the middle of a SQL statement, the table is potentially in an invalid intermediate state so Oracle can't guarantee that queries against the table return consistent or reasonable results so it disallows such queries.
In 99+% of the cases, this is a good thing because it means that you are trying to use triggers for something they are not designed for (in addition to creating applications that are terribly difficult to maintain because one action like updating a row ends up creating a huge cascade of side effects). Most of the time, you're much better off reconsidering the architecture and moving the function call out of the trigger and into some sort of API that wraps the UPDATE statement (i.e. a stored procedure that does the UPDATE and calls the procedure).
In the <1% of the cases where you really want to do this in a trigger, you'd need a combination of objects (or a compound trigger with multiple sections in 11g). You would need
- A package with a collection of TableAR primary keys
- A before statement trigger that clears out this collection
- A row-level trigger that inserts the :new.Id into the collection
- An after statement trigger that iterates over the collection and calls the function.
That, however, is a lot of moving parts to deal with so it radically increases complexity. So you'd only want to consider it if you are really confident that you need to use triggers in the first place.