Re: Trigger problem

Oracle isn't Sybase. For that matter it isn'd DB2, SQL Server, or Informix. You are engaging in one of the most egregious and fatal errors anyone can make in Oracle. Assuming that Oracle is just another RDBMS with a different company name on the CD.

Buy Tom Kyte's book "expert one-onm-one Oracle" and read it. Especially the parts on multiversioning, transactions, and locking.

Then you will be ready to unlearn 50% of what you think you know and will be far better for it.

But in direct answer to your question ... the mutating trigger error is related to Oracle's architecture. You can get around it by creating your trigger as an autonomous transaction. Whether you should is another matter entirely. So I strongly suggest you read Tom's book before making that decision.

Daniel Morgan

Michael Zhao wrote:

> Hi,
>
> I have very simple table "t1" in Oracle 8.1.7 database, I wrote a trigger on
> "t1" and got an error when I update "t1".
>
> ORA-04091: table T1 is mutating, trigger/function may not see it
> ORA-06512: at "AU_T1", line 4
> ORA-04088: error during execution of trigger AU_T1'
>
> But the same trigger works fine in Sybase.
>
> How can I write this trigger so I can "select" or "update" other rows in the
> same table in my trigger?
>
> Thanks in advance.
>
> -Michael
>
> ====Table====
> create table t1 (
> tid number(10) primary key,
> name varchar2(40)
> );
>
> ====Trigger====
> create or replace trigger au_t1
> before insert or update
> on t1
> referencing old as old new as new
> for each row
> declare
> cc number(10);
> begin
> select count(tid) into cc from t1;
> end;
> /
>
> ====Update====
> update t1 set tid=2 where tid=1;
Received on Mon Mar 11 2002 - 16:12:33 CST