The 29 March quiz focused on the fact that a mutating table error can be raised not only when a trigger is executed, but also when a function is called from within an SQL statement.
A player noticed that when you run the verification code for this quiz from a SYSDBA account, no error is raised.
I lack the time to do more experimentation; perhaps a player can see if this change in behavior extends to mutating table errors in triggers - and also uncover an explanation for this behavior.
The PL/SQL Challenge (www.plsqlchallenge.com) offers a daily quiz on the PL/SQL language, through which thousands of Oracle technologists demonstrate and deepen their knowledge of PL/SQL. This blog contains posts by the PL/SQL Challenge founder, Steven Feuerstein, as well as comments from players.
I tested this briefly and it appears that when the trigger is owned by the SYSDBA user (SYS in my case) then mutating triggers/function rules do not apply?
ReplyDeleteInterestingly, in my testing, I also noticed another tidbit that I wasn't aware of: triggers cannot be created on objects owned by SYS (ORA-0489). So, for my test, I created a table in the SCOTT schema, but created my trigger as SYS. With this setup, I get no mutation errors whether my insert/update is done by SYS or by SCOTT. If I drop the SYS trigger though and add the same trigger as SCOTT then I get the mutation errors.
Interesting observation, but ... SYS should nver, ever be used for such purposes.
ReplyDeleteAs Tom Kyte like to put it "SYS is different".
A trigger owned by SYS appears resistant to the mutating table error. If I had to guess, I'd say that it is because SYS needs some leeway to cater for recursive SQL (eg adding partitions when inserting rows)
ReplyDelete