473,320 Members | 2,117 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

problem in trigger

hello,
i have a problem in trigger. My trigger is

CREATE OR REPLACE TRIGGER duplicate_deptno
BEFORE INSERT OR UPDATE OF deptno ON DEPT1
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
x NUMBER ;


BEGIN

SELECT COUNT(deptno)INTO x FROM DEPT1 WHERE deptno=:NEW.deptno;

IF INSERTING OR UPDATING THEN
IF x>0 THEN
RAISE_APPLICATION_ERROR(-20101,'duplicate deptno found');
END IF ;
END IF;
End;

My problem is
when i insert new deptno it work.if there is already that deptno then it send message.But when i update table error message come like

table....is mutating, trigger/function may not see it ..............................

i try to do by using autonomous transaction .i solve the mutation error but same deptno are inserted & updated.so how can i solve this problem in trigger.
Oct 1 '07 #1
3 1856
debasisdas
8,127 Expert 4TB
The mutating trigger error comes when the user tries to update the base table from which it is reading data.

Please find the details of triggers here and mutating triggers here.
Oct 1 '07 #2
QVeen72
1,445 Expert 1GB
hello,
i have a problem in trigger. My trigger is

CREATE OR REPLACE TRIGGER duplicate_deptno
BEFORE INSERT OR UPDATE OF deptno ON DEPT1
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
x NUMBER ;


BEGIN

SELECT COUNT(deptno)INTO x FROM DEPT1 WHERE deptno=:NEW.deptno;

IF INSERTING OR UPDATING THEN
IF x>0 THEN
RAISE_APPLICATION_ERROR(-20101,'duplicate deptno found');
END IF ;
END IF;
End;

My problem is
when i insert new deptno it work.if there is already that deptno then it send message.But when i update table error message come like

table....is mutating, trigger/function may not see it ..............................

i try to do by using autonomous transaction .i solve the mutation error but same deptno are inserted & updated.so how can i solve this problem in trigger.
Hi,

Just Intrested to know this.. Why Use a Trigger to avoid duplicate..?
Why not Declare Unique Contsraint on that Column ..?
It is meant to avoid duplicates..
Use objects, what they are best for..

Regards
Veena
Oct 1 '07 #3
Saii
145 Expert 100+
Yes, just create constraint and handle dup_val_on_index exception wherever needed.
Oct 1 '07 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Dunc | last post by:
I'm new to Postgres, and getting nowhere with a PL/Perl trigger that I'm trying to write - hopefully, someone can give me some insight into what I'm doing wrong. My trigger is designed to reformat...
4
by: Hank | last post by:
I have two SQL Server 2000 machines (server_A and server_B). I've used sp_addlinkedserver to link them both, the link seems to behave fine. I can execute remote queries and do all types of neat...
1
by: BUSHII | last post by:
I have little problem and I dont have any idea how to make my trigger. I have table MyTable where I have many column with almost same name and same type (Grp1,Grp2,Grp3,Grp4...Grp50 char(1))....
4
by: JesusFreak | last post by:
From: us_traveller@yahoo.com (JesusFreak) Newsgroups: microsoft.public.scripting.jscript Subject: toolbar script problem NNTP-Posting-Host: 192.92.126.136 Recently, I downloaded the following...
2
by: gustavo_randich | last post by:
Hi :-) I'm porting a project from Oracle to DB2 and now I'm trying to avoid error SQL0746N in a trigger which reads the same table in which the trigger is defined. Below is Oracle's...
4
by: SUKRU | last post by:
Hello everybody. Unfortunately I am pretty new to sql-server 2000 I need some help with a Trigger I created. I created a trigger witch takes the id of the affected row and does a update on a...
2
by: mob1012 via DBMonster.com | last post by:
Hi All, I wrote last week about a trigger problem I was having. I want a trigger to produce a unique id to be used as a primary key for my table. I used the advice I received, but the trigger is...
2
by: dean.cochrane | last post by:
I have inherited a large application. I have a table which contains a hierarchy, like this CREATE TABLE sample_table( sample_id int NOT NULL parent_sample_id int NOT NULL ....lots of other...
11
by: Ed Dror | last post by:
Hi there, I'm using ASP.NET 2.0 and SQL Server 2005 with VS 2005 Pro. I have a Price page (my website require login) with GridView with the following columns PriceID, Amount, Approved,...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.