Home » SQL & PL/SQL » SQL & PL/SQL » Urgent: Trigger updating the table itself on which trigger is written (Oracle 9i)
Urgent: Trigger updating the table itself on which trigger is written [message #383049] Tue, 27 January 2009 01:50 Go to next message
ide007
Messages: 5
Registered: October 2008
Junior Member
Hi,
I need to write an AFTER INSERT trigger on Table a
when a.number = 10.
And after insertion on this table,
I have to update the a.name like below:

a.name = (select name from b where b.id = a.id)

Please tell me how should be the trigger body for the same.


Thanks in Adv
Re: Urgent: Trigger updating the table itself on which trigger is written [message #383052 is a reply to message #383049] Tue, 27 January 2009 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nothing is urgent in forum but you to read read OraFAQ Forum Guide and provide usual and requested information (DDL and so on).
Don't forget to read "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), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post what you already tried.

Regards
Michel
Re: Urgent: Trigger updating the table itself on which trigger is written [message #383055 is a reply to message #383049] Tue, 27 January 2009 02:21 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Do you really need to write an AFTER trigger? Because, the BEFORE one does the job quite successfully.

Here's an example, based on data stored in Scott's schema. I'll insert DEPTNO into a test table, and trigger will populate DNAME as a reference to the DEPT table.
SQL> create table test (deptno number, dname varchar2(20));

Table created.

SQL> create or replace trigger trg_bi
  2    before insert on test
  3    for each row
  4  begin
  5    select dname into :new.dname
  6      from dept d
  7      where d.deptno = :new.deptno;
  8  end;
  9  /

Trigger created.

SQL> insert into test (deptno) values (20);

1 row created.

SQL> select * from test;

    DEPTNO DNAME
---------- --------------------
        20 RESEARCH

SQL>

Is this what you are looking for?
Re: Urgent: Trigger updating the table itself on which trigger is written [message #383249 is a reply to message #383055] Tue, 27 January 2009 23:43 Go to previous messageGo to next message
ide007
Messages: 5
Registered: October 2008
Junior Member
Hi,
thanks.

Please see the trigger code below. I need an after insert trigger only as I have to update the same table on which I am writing the trigger.

CODE:
------
create table ttab1
(
 tno number(4),
 tname varchar2(30),
 tcomm varchar2(30)
 );
 
create table ttab2
(
 tno1 number(4),
 tname1 varchar2(30),
 tcomm1 varchar2(30)
 );
 
CREATE OR REPLACE TRIGGER apps.ttab1_trig
   AFTER INSERT ON ttab1 FOR EACH ROW
   WHEN (new.tno = 10)
BEGIN
      UPDATE ttab1
           SET tcomm = (SELECT tcomm1
                          FROM ttab2
                         WHERE tname1 = :new.tname);
END ttab1_trig;

 insert into ttab2
 values(1, 'TEST', 'SHIP_M');

 commit;

 insert into ttab1
 values(10, 'TEST', '');


Now when I am trying to execute the above insert statement,
I am getting ORA-04091 9table is mutating; trigger/function may not see it).

Can you please help me in resolving it?

[EDITED by LF: applied [code] tags]

[Updated on: Thu, 29 January 2009 03:07] by Moderator

Report message to a moderator

Re: Urgent: Trigger updating the table itself on which trigger is written [message #383250 is a reply to message #383049] Tue, 27 January 2009 23:46 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Can you please help me in resolving it?
Do NOT do access table [no DDL, no DML, no SELECT] within trigger the table upon which the trigger exists.

[Updated on: Tue, 27 January 2009 23:47]

Report message to a moderator

Re: Urgent: Trigger updating the table itself on which trigger is written [message #383253 is a reply to message #383249] Wed, 28 January 2009 00:34 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
ide007
I have to update the same table on which I am writing the trigger.

But, my example DOES update the same ("test") table ... it is referenced by the :NEW identifier (":NEW.DNAME", ":NEW.DEPTNO").
Re: Urgent: Trigger updating the table itself on which trigger is written [message #383269 is a reply to message #383249] Wed, 28 January 2009 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am getting ORA-04091 9table is mutating; trigger/function may not see it).

Can you please help me in resolving it?

I remark that in one day:
1/ you didn't read the forum guide
2/ you didn't learn how to format your post
3/ you didn't learn how to search for this FAQ

Regards
Michel
Re: Urgent: Trigger updating the table itself on which trigger is written [message #383466 is a reply to message #383269] Thu, 29 January 2009 00:04 Go to previous messageGo to next message
ide007
Messages: 5
Registered: October 2008
Junior Member
Hi Michel,
thanks for your remarks. And I would like to let you know that I am very much aware how to search FAQ for this and not only FAQ there are many other places where I could search for this error. And I already had search results for the same error but my requirement was something different for which I posted it.
Anyway I could resolve this by my own. Regarding formatting,
I may not have formatted my post properly but what I can see is that most of these posts are like this.

Thanks
Re: Urgent: Trigger updating the table itself on which trigger is written [message #383492 is a reply to message #383466] Thu, 29 January 2009 00:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I may not have formatted my post properly but what I can see is that most of these posts are like this.

And you can see than in most posts I ask for formatting (but for Forms/Report forum I have almost no look).
If most people start to rape or kill, do you also do it?

Quote:
And I already had search results for the same error but my requirement was something different

The root of the error is the same each time, you have to understand it to know how to solve your problem.
And Littlefoot perfectly answered it.

Regards
Michel
Re: Urgent: Trigger updating the table itself on which trigger is written [message #383558 is a reply to message #383492] Thu, 29 January 2009 03:00 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:

And you can see than in most posts I ask for formatting


And how much do I wish you'd give it a rest?
You've been repetatively posting the same requests for over 6 months, and other than giving you a huge posts total, it's had no observable impact on the quality of posts here.
It just serves to reduce the Useful Posts : Noise ratio and I suspect, to discourage people from posting here by making the site appear less welcoming and more officious.

Yes - formatting does help, but given that the OP had posted a decent test case showing his problem, the fact that it was not formatted is virtually irrelevant.
The test case was there - either provide help, or stop complaining and don't post to the thread.

Quote:

If most people start to rape or kill, do you also do it?



Well, that's a non-combatative and well reasoned argument, if ever I saw one.
Previous Topic: I need help in this query (merged)
Next Topic: How to call JSP from Oracle
Goto Forum:
  


Current Time: Sun Dec 04 18:55:48 CST 2016

Total time taken to generate the page: 0.10681 seconds