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.1 person found this helpful
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.
Usually when updating you don't check whether or not the key exists (it might have been deleted meanwhile).1 person found this helpful
You just update and check sql%rowcount to see if anything was updated.
What is the point of the code:
as neither is used?
localcontext varchar2(32000) := '; Context : '; sqlstmt varchar2 (2000);
It seems to me very unlikely that this is the real code ... so please post the real code and database version number.
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.
Thanks Justin For the detail Explanation. I sincerely appreciate your help. I was under the impression that only Update would cause the mutation or create any issue. I was not aware that the table is in invalid state when it's in the middle of a SQL statement.
Thanks Again Everyone.
Actually the row level trigger can perform the select ... if the trigger is defined with PRAGMA AUTONOMOUS_TRANSACTION but, having said that, I would not advise that you do it.
The best thing to do is just let it happen and trap any resulting error if one is generated.