Re: need help for triggers

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 23 Feb 1999 13:36:28 GMT
Message-ID: <36d7ae40.87632188@192.86.155.100>

A copy of this was sent to GreMa_at_t-online.de (Matthias Gresz) (if that email address didn't require changing) On Tue, 23 Feb 1999 11:51:56 +0100, you wrote:

>
>
>Venkat Mundrathi schrieb:
>>
>> I am new to oracle(triggers). I am using personal oracle 8.0,
>>
>> 1)The purpose of trigger is when ever I insert a new row in the emp
>> table(hope you know the description) with the same empno or ename it has to
>> fire and give an error message to user that the row with empno or ename is
>> already exist.
>
>create or replace trigger tbi_emp before insert on emp for each row
>declare l_counter number;
>begin
> select
> count(empno)
> into
> l_counter
> from
> emp
> where
> empno=:new.empno
> and
> rowid!=:new.rowid;
> if l_counter!=0 then
> RAISE_APPLICATION_ERROR(-20000,'Got empno yet!');
> end if;
>end;
>
>

Nope,that won't do it. Consider this:

SQL> create or replace trigger tbi_emp before insert on emp for each row   2 declare l_counter number;
  3 begin

  4        select 
  5               count(empno)
  6             into
  7               l_counter
  8             from
  9               emp
 10             where
 11               empno=:new.empno
 12             and
 13               rowid!=:new.rowid;
 14             if l_counter!=0 then
 15                RAISE_APPLICATION_ERROR(-20000,'Got empno yet!');
 16             end if;         

 17 end;
 18 /

Trigger created.

SQL> insert into emp ( empno ) values ( 1 );

1 row created.

SQL> commit;

Commit complete.

SQL> select empno, count(*) from emp where empno = 1 group by empno;

     EMPNO COUNT(*)
---------- ----------

         1 2

how did count get to be 2? Easy, in another session, after the above insert but BEFORE the commit, i did the same insert. Non-Blocking reads and multiversioning make it impossible to prevent dups in triggers (without putting a FULL table lock on the table in question allowing only one session at a time to insert or update).

you must use a unique constraint or primary key.

>> 2) I created a new table U_OBJECTS( name varchar(40),type
>> varchar(15),created date). I inserted the values from user_objects table.
>> Now i tried to create a trigger, it should fire such that when ever a new
>> row is inserted in user_objects the table U_OBJECTS has to be updated( does
>> this sounds ridiculous). when i was compiling thetrigger it is give me the
>> following error
>>
>> ORA 25001- CANNOT CREATE THIS TRIGGER TYPE ON VIEWS
>
>user_objects is a view not a table!!
>
>>
>> can you please comment on this.
>>
>> thanks in advance
>
>HTH
>Matthias
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Feb 23 1999 - 07:36:28 CST