Home » SQL & PL/SQL » SQL & PL/SQL » Database Trigger Error (10g)
Database Trigger Error [message #411559] Sun, 05 July 2009 06:51 Go to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear All

I have following trigger on table but it is creating error during insertion in table.
CREATE TABLE A
(
  IDE  NUMBER
);
Insert Into A Values(1);
Commit;

CREATE TABLE ORA_FAQ_ONE
(
  IDE   NUMBER,
  NAME  VARCHAR2(250 BYTE)
);
ALTER TABLE ORA_FAQ_ONE ADD (
  CONSTRAINT ORA_FAQ_ONE_PK PRIMARY KEY (IDE)

I have following trigger on ORA_FAQ_ONE Table
CREATE OR REPLACE TRIGGER TRG_ORA_FAQ_ONE BEFORE INSERT ON ORA_FAQ_ONE
 REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
DECLARE
v_id NUMBER;
BEGIN
SELECT NVL(MAX(v_id),0)+1 INTO v_id FROM a;
:NEW.Ide:=v_id;
UPDATE a
SET ide=v_id;
END;


After this I am running following query to insert data

INSERT INTO ora_faq_one(name)
SELECT 'abc' FROM dual
UNION
SELECT 'def' FROM dual


After this following error is occuring
Ora-00001. Unique Contraint Voilated.

I am unable to understand why this is occuring while "Before insert for each row" trigger fire for each row before inserting and i am assinging new value to "Id" for each row.

Thanks In Advance

Regards
Asif.
Re: Database Trigger Error [message #411560 is a reply to message #411559] Sun, 05 July 2009 07:03 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
SELECT NVL(MAX(v_id),0)+1 INTO v_id FROM a;
Is your intention to write
SELECT NVL(MAX(IDE),0)+1 INTO v_id FROM a;
Anyway why can't you use sequence to fill the ORA_FAQ_ONE,IDE?

EDIT:
SELECT IDE + 1 INTO v_id FROM a;
I think this is also sufficient.

By
Vamsi

[Updated on: Sun, 05 July 2009 07:17]

Report message to a moderator

Re: Database Trigger Error [message #411563 is a reply to message #411559] Sun, 05 July 2009 07:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
UPDATE a
SET ide=v_id;

What does this do?

Regards
Michel
Re: Database Trigger Error [message #411566 is a reply to message #411563] Sun, 05 July 2009 07:40 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear Michle Uncle.
Update A
Set Ide=V_Id


Above statement updates table "A" and genrates new id for next row. We are avoiding to use Sequence Because we want to reset ID on year Bases. e.g. 0901,1001.(Here 09 and 10 is year)

Regards.
Asif

Re: Database Trigger Error [message #411575 is a reply to message #411566] Sun, 05 July 2009 09:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read the statement. It update ALL rows of the table with the last value you calculated.
What did you put it in the trigger?

Quote:
:NEW.Ide:=v_id;

What does this do?

Regards
Michel
Re: Database Trigger Error [message #411578 is a reply to message #411575] Sun, 05 July 2009 09:41 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Michel,
I don't see any issues with his code, except the v_id calculation.
There is only one row in table A and Asif is using that to calculate the ide of ORA_FAQ_ONE.
While inserting into the table ORA_FAQ_ONE, he is not inserting the ide column and he want to insert that using his trigger.

This is what my understanding. Let me know, if I'm wrong.

By
Vamsi
Re: Database Trigger Error [message #411579 is a reply to message #411578] Sun, 05 July 2009 10:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
There is only one row in table A and Asif is using that to calculate the ide of ORA_FAQ_ONE.

Nothing in OP's post mentioned this.
And "SELECT NVL(MAX(v_id),0)+1 INTO v_id FROM a;" seems to imply this is not the case otherwise why doing a MAX?

Of course the correct way is to use a sequence.

Regards
Michel
Re: Database Trigger Error [message #411580 is a reply to message #411566] Sun, 05 July 2009 10:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
We are avoiding to use Sequence Because we want to reset ID on year Bases.

And what prevent you from resetting the sequence to the value you want each year?

Regards
Michel
Re: Database Trigger Error [message #411584 is a reply to message #411559] Sun, 05 July 2009 11:22 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear Michle Uncle.

"Vamsi" is saying correct. you are also correct, i have used "Max" wrongly, it should not be used because there is only one value in table "A".

And i want to assing value in colunn ORA_Faq_One.IDE by using "BEFORE INSERT" trigger on table ORA_FAQ_ONE.

Secondly how can i reset my sequence when new year starts. The format of new number shuold be like 2009000001. Here 2009 is year. When 2009 ends and 2010 starts then first number would be 2010000001.

How can i reset my sequence? And why follwoing my posted code is not working?

Regards.
Asif.
Re: Database Trigger Error [message #411585 is a reply to message #411584] Sun, 05 July 2009 11:33 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
You can alter the sequence to change the "increment by" and select nextval.
Then you can alter increment by back to 1.
But when will you do it?
Who else can insert the data to your table?
Can you assure no one will insert data to that table until you change the sequence?
Of course you will be in the same situation, if you use your trigger also.

By
Vamsi
Re: Database Trigger Error [message #411586 is a reply to message #411559] Sun, 05 July 2009 11:57 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear

I have to reset sequenct on 31-dec of every year. How can we do it without job?

we can no ensure that noone will insert data until we reset sequence.

Regards.
Asif.
Re: Database Trigger Error [message #411587 is a reply to message #411586] Sun, 05 July 2009 12:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
we can no ensure that noone will insert data until we reset sequence.

Lock the table, reset the sequence, unlock the table.

Regards
Michel
Re: Database Trigger Error [message #411597 is a reply to message #411559] Sun, 05 July 2009 23:02 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear
We Want to reset Sequence automatically instead of manually.

Secondly sir, when i send data by cursor then before trigger is working property but when i send using select statement it generates error unique key violated. I am unable to understand.

if i send data using follwoing code then above written trigger is doing well
Declare
Cursor C Is 
SELECT 'abc' Str FROM dual
UNION
SELECT 'def' FROM dual;
Begin
For V In C Loop
Insert Into Ora_Faq_One(Name) Values(V.Str);
End Loop;
End;

But when i send data using following then error occure that primary key voilated
Insert Into Ora_Faq_One(Name)
Select 'Abc' From Dual
Union
Select 'Def' From Dual;


and i am not understand why this is happening while before trigger is for each row. While in above trigger for each row trigger is picking ide from table "A" and updating at same time to generate Unique Number for next Row.

Please clear my concept that why before trigger is not working properly at the time of inserting data by select statment while same trigger is working property when same data is inserted by cursor.

Thanks in advance for your time and help.

Regards.
Asif.

Re: Database Trigger Error [message #411598 is a reply to message #411597] Sun, 05 July 2009 23:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
We Want to reset Sequence automatically instead of manually.

Which can be done by a job as already mentioned.
By the way, your trigger does not handle this case.

Quote:
and i am not understand why this is happening while before trigger is for each ro

Put a dbms_output in your trigger to see what happens.

Regards
Michel
Re: Database Trigger Error [message #411599 is a reply to message #411559] Sun, 05 July 2009 23:23 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear Michle Uncle
Quote:
By the way, your trigger does not handle this case.



sorry sir, how can i do?

Secondly Sir,

I have written dbms_output.put_Line to see what is actually happening. I have set serveroutput on. but that statement is not executing. I have written this at the beginning of trigger to see what happen, but no message is displaying as i run sql stament message appear unique constraint voilated.

but when i insert only single row then message by dbms_output.put_line is displayed.but when more than one row is inserted by using select statment then no message of dbms_output.put_line is showed but unique key error is displayed.

Regards
Asif.
Re: Database Trigger Error [message #411600 is a reply to message #411599] Sun, 05 July 2009 23:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
have written dbms_output.put_Line to see what is actually happening. I have set serveroutput on. but that statement is not executing.

If you don't post what you did, how could we what is wrong?
Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: Database Trigger Error [message #411605 is a reply to message #411559] Sun, 05 July 2009 23:54 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

DEAR MICHLE UNCLE

Please see follwoing trigger on table Ora_Faq_One.
CREATE OR REPLACE TRIGGER trg_ora_faq_one   BEFORE INSERT
   ON ora_faq_one
   REFERENCING NEW AS NEW OLD AS OLD   FOR EACH ROW
DECLARE
   v_id   NUMBER;
BEGIN
   SELECT NVL (v_id, 0) + 1
     INTO v_id
     FROM a;

   DBMS_OUTPUT.put_line ('IDE WILL BE ===>' || v_id);
   :NEW.ide := v_id;
   
   UPDATE a
      SET ide = v_id;
END;


please see attachment which has statment that i run

thanks
Re: Database Trigger Error [message #411607 is a reply to message #411605] Mon, 06 July 2009 00:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I can't download doc file.
Quote:
Use SQL*Plus and copy and paste your session.

Regards
Michel

Re: Database Trigger Error [message #411609 is a reply to message #411597] Mon, 06 July 2009 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
if i send data using follwoing code then above written trigger is doing well
Declare
Cursor C Is 
SELECT 'abc' Str FROM dual
UNION
SELECT 'def' FROM dual;
Begin
For V In C Loop
Insert Into Ora_Faq_One(Name) Values(V.Str);
End Loop;
End;


Not my experience:
SQL> Declare
  2  Cursor C Is 
  3  SELECT 'abc' Str FROM dual
  4  UNION
  5  SELECT 'def' FROM dual;
  6  Begin
  7  For V In C Loop
  8  Insert Into Ora_Faq_One(Name) Values(V.Str);
  9  End Loop;
 10  End;
 11  /
Declare
*
ERROR at line 1:
ORA-00001: unique constraint (MICHEL.ORA_FAQ_ONE_PK) violated
ORA-06512: at line 8

Regards
Michel

Re: Database Trigger Error [message #411610 is a reply to message #411605] Mon, 06 July 2009 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table t (id number, nam varchar2(50), ts timestamp default systimestamp);

Table created.

SQL> create or replace procedure p (id in number, nam in varchar2)
  2  is
  3     pragma autonomous_transaction;
  4  begin
  5    insert into t (id, nam) values (id, nam);
  6    commit;
  7  end;
  8  /

Procedure created.

SQL> CREATE OR REPLACE TRIGGER trg_ora_faq_one   BEFORE INSERT
  2     ON ora_faq_one
  3     REFERENCING NEW AS NEW OLD AS OLD   FOR EACH ROW
  4  DECLARE
  5     v_id   NUMBER;
  6  BEGIN
  7     SELECT NVL (v_id, 0) + 1
  8       INTO v_id
  9       FROM a;
 10  
 11     p(v_id, :new.name);
 12     :NEW.ide := v_id;
 13     
 14     UPDATE a
 15        SET ide = v_id;
 16  END;
 17  /

Trigger created.

SQL> Insert Into Ora_Faq_One(Name)
  2  Select 'Abc' From Dual
  3  Union
  4  Select 'Def' From Dual;
Insert Into Ora_Faq_One(Name)
*
ERROR at line 1:
ORA-00001: unique constraint (MICHEL.ORA_FAQ_ONE_PK) violated

SQL> select * from t;

        ID NAM        TS
---------- ---------- ------------------------------
         1 Abc        06-JUL-09 07.52.05.090000 AM
         1 Def        06-JUL-09 07.52.05.090000 AM

Regards
Michel
Re: Database Trigger Error [message #411612 is a reply to message #411559] Mon, 06 July 2009 01:04 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear Michle

please check follwoing trigger
CREATE OR REPLACE TRIGGER trg_ora_faq_one
   BEFORE INSERT
   ON ora_faq_one
   REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW
DECLARE
   v_id   NUMBER;
BEGIN
   SELECT NVL (ide, 0) + 1
     INTO v_id
     FROM a;

   DBMS_OUTPUT.put_line ('IDE WILL BE ===>' || v_id);
   :NEW.ide := v_id;

   UPDATE a
      SET ide = v_id;
END;


please note following with old posted trigger
Quote:
SELECT NVL (ide, 0) + 1
INTO v_id
FROM a;


I saw it carefully and found out problem

very very thanks for your precious time.

and please tell me when we insert data in table using select statment is data inserted in table by one by one row or all data is inserted at once. I am confused.

Regards.
Asif.
Re: Database Trigger Error [message #411614 is a reply to message #411559] Mon, 06 July 2009 01:18 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear Michle Uncle

Please not follwoing trigger on Ora_Faq_One
CREATE OR REPLACE TRIGGER trg_ora_faq_one BEFORE INSERT ON ora_faq_one
 REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
DECLARE
v_id NUMBER;
BEGIN
SELECT NVL(MAX(ide),0)+1 INTO v_id FROM Ora_Faq_One;
Dbms_Output.Put_Line('IDE WILL BE ===>'||v_id);
:NEW.Ide:=v_id;
END;


Please note follwoing
SQL> insert into ora_faq_one(name) values('asif');

1 row created.

SQL> insert into ora_faq_one(name)
  2  select 'Michle' From dual
  3  Union
  4  Select 'Asif' From Dual;
insert into ora_faq_one(name)
            *
ERROR at line 1:
ORA-04091: table MAMALIK.ORA_FAQ_ONE is mutating, trigger/function may not see it
ORA-06512: at "MAMALIK.TRG_ORA_FAQ_ONE", line 4
ORA-04088: error during execution of trigger 'MAMALIK.TRG_ORA_FAQ_ONE'


Please note when one row is inserted then no mutating error when when more than one row inserted mutating error why?

Select please tell me when data is inserted using select statement is it inserted at once or inserted one by one row.


Regards.
Asif.
Re: Database Trigger Error [message #411630 is a reply to message #411614] Mon, 06 July 2009 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I saw it carefully and found out problem

Please when you find a solution, post it for others.

In this case, you selected the variable you want to set instead of the column:
SELECT NVL (v_id, 0) + 1 INTO v_id FROM a;
SELECT NVL (ide, 0) + 1 INTO v_id FROM a;

Anyway, forget this solution and go to sequence.

Quote:
Select please tell me when data is inserted using select statement is it inserted at once or inserted one by one row.

one by one at once (in a single statement).

Regards
Michel

Re: Database Trigger Error [message #411649 is a reply to message #411612] Mon, 06 July 2009 03:53 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
mamalik wrote on Mon, 06 July 2009 11:34


please note following with old posted trigger
Quote:
SELECT NVL (ide, 0) + 1
INTO v_id
FROM a;


I saw it carefully and found out problem

very very thanks for your precious time.
It is nothing but my first post in this topic.

By
Vamsi
Re: Database Trigger Error [message #411683 is a reply to message #411559] Mon, 06 July 2009 05:52 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear Michle Uncle

Please not follwoing trigger on Ora_Faq_One

CREATE OR REPLACE TRIGGER trg_ora_faq_one BEFORE INSERT ON ora_faq_one
 REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
DECLARE
v_id NUMBER;
BEGIN
SELECT NVL(MAX(ide),0)+1 INTO v_id FROM Ora_Faq_One;
Dbms_Output.Put_Line('IDE WILL BE ===>'||v_id);
:NEW.Ide:=v_id;
END;

Please note follwoing

SQL> insert into ora_faq_one(name) values('asif');

1 row created.

SQL> insert into ora_faq_one(name)
  2  select 'Michle' From dual
  3  Union
  4  Select 'Asif' From Dual;
insert into ora_faq_one(name)
            *
ERROR at line 1:
ORA-04091: table MAMALIK.ORA_FAQ_ONE is mutating, trigger/function may not see it
ORA-06512: at "MAMALIK.TRG_ORA_FAQ_ONE", line 4
ORA-04088: error during execution of trigger 'MAMALIK.TRG_ORA_FAQ_ONE'

Please note when one row is inserted then no mutating error when when more than one row inserted mutating error why?

Select please tell me when data is inserted using select statement is it inserted at once or inserted one by one row.


Regards.
Asif.
Re: Database Trigger Error [message #411685 is a reply to message #411683] Mon, 06 July 2009 05:55 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
http://asktom.oracle.com/tkyte/Mutate/
Re: Database Trigger Error [message #411700 is a reply to message #411683] Mon, 06 July 2009 06:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Select please tell me when data is inserted using select statement is it inserted at once or inserted one by one row.

Michel Cadot wrote on Mon, 06 July 2009 09:35
...
Quote:
Select please tell me when data is inserted using select statement is it inserted at once or inserted one by one row.

one by one at once (in a single statement).

Regards
Michel

Re: Database Trigger Error [message #411705 is a reply to message #411700] Mon, 06 July 2009 06:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or, to paraphrase Michel, an INSERT ... SELECT will insert a set of rows at once, in a single transaction (ie the rows will all succeed or fail together).
A row level trigger will fire for each individual row in this set.
Re: Database Trigger Error [message #411707 is a reply to message #411705] Mon, 06 July 2009 06:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd have to suggest that if you don't even know things like this, then you're probably not ready to be doing tasks like Creating Triggers.

Re: Database Trigger Error [message #413839 is a reply to message #411559] Fri, 17 July 2009 15:32 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Your whole approach is wrong. If two people are inserting at the same time, they will get the same key generated. This is what sequences are for. Start the sequence at 1 and let it increment forever. Have an indexed time stamp column. The time stamp column will show you every row that is inserted during the year. Putting intelligence into the sequence is wasteful, unusable and silly. I have been programming for 30 years and can say that your design is bad. if you want to know all the rows and the order that they were entered for January of 2009 simply

select col
from my_table
where entry_date BETWEEN TO_DATE('01-JAN-2009','DD-MON-YYYY') AND TO_DATE('31-JAN-2009 24:59.59','DD-MON-YYYY HH24:MI.SS')
ORDER BY MY_SEQUENCE;

[Updated on: Fri, 17 July 2009 15:34]

Report message to a moderator

Previous Topic: Compliation errors and Please Help with Nested Table merged (many)
Next Topic: Devided by zero in query
Goto Forum:
  


Current Time: Wed Dec 07 13:02:13 CST 2016

Total time taken to generate the page: 0.13167 seconds