Home » SQL & PL/SQL » SQL & PL/SQL » trigger to check existence of record and get errors (Oracle 9i 92010)
trigger to check existence of record and get errors [message #361768] Fri, 28 November 2008 00:29 Go to next message
chintan.patel
Messages: 140
Registered: July 2008
Location: Ahmedabad
Senior Member
hi friends

i am trying to create trigger to check existence of record in table before insert or update but i found different errors from sqlplus and application.

***Trigger
CREATE OR REPLACE TRIGGER RECORDCHECK
BEFORE insert or update on EMPRECORD
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
cnt number;
Begin
select nvl(count(empcode),0) into cnt from (select distinct empcode from emprecord WHERE empcode=:new.empcode);
if cnt>0 then
RAISE_APPLICATION_ERROR (-20011, 'Double Record Error.......');
end if;
End;

****Insert Query from sqlplus
insert into emprecord fields(empcode) values (3);

if record already exist in table trigger fired and it gives bellow messages.

SQL> insert into emprecord fields(empcode) values (3);
insert into emprecord fields(empcode) values (3)
*
ERROR at line 1:
ORA-20011: Double Record Error.......
ORA-06512: at "RECORDCHECK", line 6
ORA-04088: error during execution of trigger 'RECORDCHECK'

****Insert Query from Application
insert into emprecord select 3 from dual;

it gives error

SQL> insert into emprecord select 3 from dual;
insert into emprecord select 3 from dual
*
ERROR at line 1:
ORA-04091: table EMPRECORD is mutating, trigger/function may not see it
ORA-06512: at "RECORDCHECK", line 4
ORA-04088: error during execution of trigger 'RECORDCHECK'

Now any one guide me that what should i have to do to resolve this problem.

Thanks
Re: trigger to check existence of record and get errors [message #361769 is a reply to message #361768] Fri, 28 November 2008 00:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Mutating trigger question is one of the more asked one, please search BEFORE posting.

Regards
Michel
Re: trigger to check existence of record and get errors [message #361777 is a reply to message #361768] Fri, 28 November 2008 01:16 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Just wondering: why would you want to enforce such a rule by a trigger? Wouldn't you rather create UNIQUE INDEX and let Oracle handle duplicates?
Re: trigger to check existence of record and get errors [message #361779 is a reply to message #361777] Fri, 28 November 2008 01:24 Go to previous messageGo to next message
chintan.patel
Messages: 140
Registered: July 2008
Location: Ahmedabad
Senior Member
Actually this is the sample. in my actual table i can not apply unique index.

actual structure is
ccode dcode lcode fyr dtype vno eno
1 1 1 20082009 x 1 1
1 1 1 20082009 x 1 2

in above example within one vno multiple eno is not exeptable.
Re: trigger to check existence of record and get errors [message #361862 is a reply to message #361779] Fri, 28 November 2008 05:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The reason for the difference in behaviour is this:
If you perform a single insert (INSERT INTO table VALUES) then Oracle knows the extent of the changes to the table, and can avoid the mutating table problem.
If you insert multiple rows, then Oracle cannot guarantee from within the trigger which rows have been inserted and which haven't, and so cannot guarantee that queries performed within the trigger will return consistent results.
Re: trigger to check existence of record and get errors [message #361866 is a reply to message #361768] Fri, 28 November 2008 05:16 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
If eno must have the same value for every record with the same value of vno then that would suggest to me that eno shouldn't actually be on that table.
Re: trigger to check existence of record and get errors [message #361884 is a reply to message #361768] Fri, 28 November 2008 06:48 Go to previous messageGo to next message
chintan.patel
Messages: 140
Registered: July 2008
Location: Ahmedabad
Senior Member
hi friends

any one have the sample triggers to check existence of record in table. if u have please send me to see it.

Thanks
Re: trigger to check existence of record and get errors [message #361901 is a reply to message #361884] Fri, 28 November 2008 08:54 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
There are no sample triggers to check for existence in the same table, since they would all result in a mutating table error.

The way your trigger works now, a unique key on empcode would work.

Can you explain why this does not fit your requirement? Can you explain what your requirement exactly is?

An format your table output and code.

It's impossible to see which column is which in your post so far.

Previous Topic: tutorial on stored procedure
Next Topic: SYS_CONNECT_BY_PATH
Goto Forum:
  


Current Time: Sat Dec 10 22:22:54 CST 2016

Total time taken to generate the page: 0.19300 seconds