Home » SQL & PL/SQL » SQL & PL/SQL » problem solving mutating trigger (10.0 win xp)
problem solving mutating trigger [message #346180] Sun, 07 September 2008 01:20 Go to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Hi,

I am trying to avoid mutating trigger by the steps given in this forum but unsuccessful, please let me know where am I going wrong.

SQL> select * from t;

NAME           SALARY
---------- ----------
Akash            1000
Menon            3000
Salim            1500
Vilpesh          5500


I want to update the salaries of all the employees based on what happened to the salary of Akash. If it was decreased or increased then the other salaries should follow suit with the same amount increase or decrease. Here is the logic I tried to use
Quote:

The "correct" solution for mutating trigger is:

- a package with a variable of a collection type (varray, pl/sql table or the like)
- a before statement trigger that initializes that variable
- a before row trigger that adds an identifier for the current row (e.g. rowid) to that variable
- an after statement trigger that loops through the entries in the collection and executes the desired code for each.



SQL> create or replace package pg1 as type ty1 is table of number; end;
  2  /
Package created.


SQL> create or replace trigger tr1 before update on t declare tab1 pg1.ty1; begin null; end;
  2  /
Trigger created.

SQL> create or replace trigger tr2 before update on t for each row declare begin select salary into tab1 from t where name='Akash'; end;
  2  /

Warning: Trigger created with compilation errors.

SQL> show error
Errors for TRIGGER TR2:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/28     PL/SQL: SQL Statement ignored
1/47     PLS-00905: object DAS.TAB1 is invalid
1/52     PL/SQL: ORA-00904: : invalid identifier


The trigger does not recognize the object created in the earlier trigger as it has not fired yet so how to code this logic? any clues..
Re: problem solving mutating trigger [message #346190 is a reply to message #346180] Sun, 07 September 2008 03:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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.
Also always post your Oracle version (4 decimals); 10.0 does not exist.

Regards
Michel
Re: problem solving mutating trigger [message #346200 is a reply to message #346180] Sun, 07 September 2008 04:29 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You did not follow the methods described in my solution:
- your before statement trigger does NOT initialize the packaged variable.
- your row trigger does NOT add an identifier to the packaged variable
- you do not have an after statement trigger.

Re: problem solving mutating trigger [message #346206 is a reply to message #346200] Sun, 07 September 2008 05:36 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
I tried again after initializing and a little change but I am not able to get this part.

Quote:
- a before row trigger that adds an identifier for the current row (e.g. rowid) to that variable


I had not created the after statement trigger as I wanted to get this step done first..can you help me with this step..

SQL> create or replace package pg1 as type ty1 is table of rowid; end;
  2  /


Quote:
Package created.


SQL> create or replace trigger tr1 before update on t declare tab1 pg1.ty1 := pg1.ty1(); begin null; end;
  2  /


Quote:
Trigger created.


SQL> create or replace trigger tr1 before update on t for each row declare begin
 if updating then select rowid into pg1.ty1 from t; end if; end;
  2  /


Quote:
Warning: Trigger created with compilation errors.

SQL> show error
Errors for TRIGGER TR1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/32 PL/SQL: SQL Statement ignored
1/50 PLS-00321: expression 'TY1' is inappropriate as the left hand
side of an assignment statement

1/58 PL/SQL: ORA-00904: : invalid identifier
Sad
Re: problem solving mutating trigger [message #346208 is a reply to message #346206] Sun, 07 September 2008 06:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is it so hard to use a formatter?

Regards
Michel
Re: problem solving mutating trigger [message #346211 is a reply to message #346206] Sun, 07 September 2008 06:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
durgadas.menon wrote on Sun, 07 September 2008 12:36

SQL> create or replace package pg1 as type ty1 is table of rowid; end;
  2  /


Does this piece of code declare "a variable of a collection type (varray, pl/sql table or the like)"? NO!
Quote:

SQL> create or replace trigger tr1 before update on t declare tab1 pg1.ty1 := pg1.ty1(); begin null; end;
  2  /


Does this initialize the package variable, or does this trigger create a private variable with the same type as the packaged variable and initialize THAT?

It is very very clear that you don't understand the least bit of what the framework is supposed to do. I would like to suggest you first start to read up on pl/sql and on mutating table problems before you proceed.

[Updated on: Sun, 07 September 2008 06:26]

Report message to a moderator

Re: problem solving mutating trigger [message #346213 is a reply to message #346208] Sun, 07 September 2008 06:28 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
oopsss I did it again.. will take care from now on Embarassed
Re: problem solving mutating trigger [message #346218 is a reply to message #346213] Sun, 07 September 2008 08:54 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Tom Kyte gives explanation with complete examples here:

http://asktom.oracle.com/tkyte/Mutate/index.html
Re: problem solving mutating trigger [message #346250 is a reply to message #346218] Sun, 07 September 2008 23:02 Go to previous message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
as usual ...thanks for the help Barbara..
Previous Topic: Creating Search function
Next Topic: Delete Cascade, Triggers and Mutating errors
Goto Forum:
  


Current Time: Fri Dec 09 09:44:12 CST 2016

Total time taken to generate the page: 0.17361 seconds