Hi,

I have a table et_trial which has column trial_id, date_last_updated etc.
trial_id is the primary key and date_last_updated is a date field. I need to update date_last_updated whenever there is any update on this table. I wrote two types of triggers, but I still get table mutating error, please help me to trouble shoot this problem. The first type is:

CREATE OR REPLACE package et_trial_new_pkg
as
type array is table of et_trial%rowtype index by binary_integer;
newvals array;
empty array;
end;
/

CREATE OR REPLACE package et_trial_old_pkg
as
type array is table of et_trial%rowtype index by binary_integer;
oldvals array;
empty array;
end;
/

CREATE OR REPLACE TRIGGER et_trial_new_bu
BEFORE UPDATE of private_name ON et_trial
begin
et_trial_new_pkg.newvals := et_trial_new_pkg.empty;
end;

CREATE OR REPLACE TRIGGER et_trial_new_bufer
BEFORE UPDATE of private_name ON et_trial
for each row
declare
i number default et_trial_new_pkg.newvals.count+1;
begin
et_trial_new_pkg.newvals(i).private_name := :new.private_name ;
end;

CREATE OR REPLACE TRIGGER et_trial_old_bu
BEFORE UPDATE of private_name ON et_trial
begin
et_trial_old_pkg.oldvals := et_trial_old_pkg.empty;
end;

CREATE OR REPLACE TRIGGER et_trial_old_bufer
BEFORE UPDATE of private_name ON et_trial
for each row
declare
i number default et_trial_old_pkg.oldvals.count+1;
begin
et_trial_old_pkg.oldvals(i).private_name := ld.private_name;
end;

CREATE OR REPLACE TRIGGER trial_date_last_updated_t
AFTER UPDATE of private_name ON et_trial
FOR EACH ROW
begin
for i in 1 .. et_trial_new_pkg.newvals.count loop
IF et_trial_old_pkg.oldvals(i).private_name <> et_trial_new_pkg.newvals(i).private_name THEN
update et_trial set DATE_LAST_UPDATED = SYSDATE
where trial_id = et_trial_old_pkg.oldvals(i).trial_id;
END IF;
end loop;
end;

Then I wrote another type of triggers to solve the problem, but it's still not fixed:

create or replace package et_trial_pkg
as
type ridArray is table of rowid index by binary_integer;

newRows ridArray;
empty ridArray;
g_trigger_updating BOOLEAN := FALSE;
end;
/


create or replace trigger et_trial_bu
before update on et_trial
begin
if(g_trigger_updating = TRUE) then
et_trial_pkg.newRows := et_trial_pkg.empty;
end if;
end;
/


create or replace trigger et_trial_aufer
after update on et_trial for each row
begin
if(g_trigger_updating = TRUE) then
et_trial_pkg.newRows( et_trial_pkg.newRows.count+1 ) := :new.rowid;
end if;
end;
/


create or replace trigger et_trial_au
after update on et_trial
begin
et_trial_pkg.g_trigger_updating := TRUE;
for i in 1 .. et_trial_pkg.newRows.count loop
Update ET_Trial
set DATE_LAST_UPDATED = SYSDATE
where rowid = et_trial_pkg.newRows(i);
end loop;
et_trial_pkg.g_trigger_updating := FALSE;
end;
/

Any suggestions are welcome.

im a new developer who had this problem too. here is the code that worked. it is based on p 301 of scott urman's advanced pl/sql book

CREATE OR REPLACE PACKAGE SITEDATA AS

TYPE t_site_id IS TABLE OF site.site_id%TYPE
INDEX BY BINARY_INTEGER;
TYPE t_status IS TABLE OF site.status%TYPE
INDEX BY BINARY_INTEGER;
TYPE t_site_type IS TABLE OF site.site_type%TYPE
INDEX BY BINARY_INTEGER;




v_site_id t_site_id;
v_status t_status;
v_site_type t_site_type;
v_numentries BINARY_INTEGER := 0;

END SITEDATA;
/


CREATE OR REPLACE TRIGGER BSITE_ID
BEFORE INSERT OR UPDATE ON site
FOR EACH ROW
BEGIN

SITEDATA.v_numentries := SITEDATA.v_numentries + 1;
SITEDATA.v_site_id(SITEDATA.v_numentries) := :new.site_id;
SITEDATA.v_status(SITEDATA.v_numentries) := :new.status;
SITEDATA.v_site_type(SITEDATA.v_numentries) := :new.site_type;



END BSITE_ID;


CREATE OR REPLACE TRIGGER PREV_ACCT_REQUEST -- email 01 'Preview Account Request'
AFTER INSERT ON SITE
-- Tested on September 29th, 2003, 10:16 am, works fine.
DECLARE
v_address1 coordinator.address1%TYPE;
v_address2 coordinator.address2%TYPE;
v_city coordinator.city%TYPE;
v_country country.c_country_dscr%TYPE;
v_email coordinator.email%TYPE;
v_fax coordinator.fax%TYPE;
v_first_name coordinator.first_name%TYPE;
v_last_name coordinator.last_name%TYPE;
v_phone coordinator.phone%TYPE;
v_phone_ext coordinator.phone_ext%TYPE;
v_postal coordinator.postal%TYPE;
v_site_name site.site_name%TYPE;
v_site_id site.site_id%TYPE;
v_site_type site.site_type%TYPE;
v_state coordinator.state%TYPE;
v_status site.status%TYPE;
v_title coordinator.title%TYPE;
v_zip coordinator.zip%TYPE;
f_address2 varchar(75); -- all variables preceded with a f are for formatting
f_comma varchar(5) := ', ';
f_ext varchar(1) := 'x';
f_title varchar2(15) := 'Job title:';
f_crlf char(2) := chr(13)||chr(10); -- line breaks for the message body

BEGIN

--avoid mutating tables, page 301 of Advanced PL/SQL book, author Joel Urman
FOR v_LoopIndex IN 1..SITEDATA.v_numentries LOOP
v_site_id := SITEDATA.v_site_id(v_LoopIndex);
v_site_type := SITEDATA.v_site_type(v_LoopIndex);
v_status := SITEDATA.v_status(v_LOOPIndex);


IF v_status = 'PP' AND v_site_type = 1
THEN
SELECT c.first_name, c.last_name, c.title, s.site_name, c.address1, c.address2,
c.city, c.state, NVL(c.phone, 'N/A'), c.phone_ext, NVL(c.fax,'N/A'),
c.email, c.postal, c.zip, co.c_country_dscr
INTO v_first_name, v_last_name, v_title, v_site_name, v_address1, v_address2,
v_city, v_state, v_phone, v_phone_ext, v_fax, v_email, v_postal, v_zip, v_country
FROM site s, coordinator c, country co
WHERE s.site_id = v_site_id
AND c.site_id = v_site_id
AND c.country = co.c_country_cd;
........... --- i took out a large chunk of code not related to the question presented in the forum





END LOOP;

SITEDATA.v_numentries := 0;
END;

Hi,

I saw your code for the trigger "et_trial_au" on the table "et_trial".
-----------------------------------------------------------------------------------
create or replace trigger et_trial_au
after update on et_trial
begin
et_trial_pkg.g_trigger_updating := TRUE;
for i in 1 .. et_trial_pkg.newRows.count loop
Update ET_Trial
set DATE_LAST_UPDATED = SYSDATE
where rowid = et_trial_pkg.newRows(i);
end loop;
et_trial_pkg.g_trigger_updating := FALSE;
end;
-----------------------------------------------------------------------------------

If I am not wrong, what you want to do is, you want to update the DATE_LAST_UPDATE column to the current date and time. Right? And hence you are using the UPDATE command.

OK, Lets understand whats happening here.
First things first, Oracle says you can not refer to the table, from a trigger or a courser or a package, on which you have written the trigger.
That means if you are wiritting a trigger "X" on the table "A" then you can not reffer to this table "A" in any way from your trigger "X".

Second, Your trigger is a "after update" one, so it does not make much sense to write a update inside a update trigger. IF IT WAS ALLOWED then this trigger will keep updating itself for ever and ever till your hardware would give up and crash! JUST JOKING!
Imagin this, you update a column of your table in the "after update" trigger. Once the update from inside the trigger gets fired the "After Update" trigger again gets fired because you issued a update command and so loooooop!

Suggession: Why don't you try to just set the value of the column to something like this
:new.DATE_LAST_UPDATE = sysdate;

You will just have to check what the new column will become ":new" or ":.old", Its been a long time I wrote a trigger so I forgot it! Hope this will help. All the best!