| Trigger Problem [message #959] |
Fri, 15 March 2002 07:28  |
Vijay
Messages: 116
Registered: September 1999
|
Senior Member |
|
|
I have a table with a column named X of type numeric. I want to increment the value of this column everytime I update any field in that particular row. I tried writing a trigger but could not write a trigger which will increment the column when updating any field. I am able to do it for 1 field only.
Any help would be greatly appreciated.
Vijay
Take a look at this:
create or replace package cust as
type cid is table of customer.CUST_ID%type index by binary_integer;
customer_id cid;
num_entries binary_integer :=0;
end cust;
CREATE OR REPLACE TRIGGER customertriggerupdate
before update
on customer
for each row
begin
cust.num_entries := cust.num_entries + 1;
cust.customer_id(cust.num_entries) := :new.cust_id;
end;
CREATE OR REPLACE TRIGGER customertriggerupdatefollowup
after update of first_name on customer
begin
if SQL%rowcount <> 0 then
update customer set X = X + 1 where cust_id = cust.customer_id(cust.num_entries);
end if;
end;
|
|
|
|
| Re: Trigger Problem [message #963 is a reply to message #959] |
Fri, 15 March 2002 09:50  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
You cannot update a table which is being modified by a trigger. It would lead to mutation.
A statement level trigger is possible but not row level trigger.
May be you can update another similar table and then flush the changes
from that table to original table.
if u badly want it, try to use the standard workarounds
(issuing on statetment level and one row level triggers etc). But that may complicate ur app logic!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
