| problem solving mutating trigger [message #346180] |
Sun, 07 September 2008 01:20  |
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 #346200 is a reply to message #346180] |
Sun, 07 September 2008 04:29  |
Frank
Messages: 7901
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 #346211 is a reply to message #346206] |
Sun, 07 September 2008 06:26 |
Frank
Messages: 7901
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 |
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
