Trigger doesnt work ORA-04091

[Bilberry]

Hi all,

How to write the trigger correctly, i want to fire this trigger when the column reviewer_id is updated on the same table. Within SQL server it looks like:

CREATE TRIGGER abracadra ON dbo.acc
FOR UPDATE as
if update (reviewerid)
update a set a.reviewed = NULL, a.reviewerid = NULL, a.revieweddate = NULL,a.t38 =
CASE when
(a.t38 is null) then a.reviewerid
else
a.reviewerid + ' / ' + a.t38
end
from acc a inner join inserted I on a.docid=I.docid and a.sort = i.sort

CREATE OR REPLACE TRIGGER abracadabra
BEFORE UPDATE
ON acc
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
begin

update acc a set a.reviewed = NULL, a.reviewerid = NULL, a.revieweddate = NULL, a.t38 =
CASE
when
(a.t38 is null) then a.reviewerid
else
a.reviewerid || ' / ' || a.t38
end
where a.docid= :NEW.docid and a.sort = :NEW.sort
;
END;

Any Idea?

 

[Dagon]

You can't perform any operations on the table for which the trigger is created. You can't select, update, insert or delete, as this will give a mutating table error.

If you want to change the value of a field, you must set it in the trigger using:

:new.field = 'XXXX';

rather than trying to do an update.

 

[ChrisHunt]

Also, it's not going to like this:

a.reviewerid + ' / ' + a.t38

when you mean this:

a.reviewerid || ' / ' || a.t38

(you have it right in the second trigger)

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[sem]

You can't perform any operations on the table for which the ROW LEVEL trigger is created except when using autonomous transactions. Though the last may be quite dangerous in some cases.

BTW do you really need to update a set of rows, not just to change fields in one being updated? Actually you may just assign values like

:new.reviewed = NULL;
:new.reviewerid = NULL;
:new.t38 := 'foo';

Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

Bilberry,

When you ask for, and receive, high-quality help, it is appropriate, at least, to respond to the posters who share their time and resources to solve your need.

Our colleagues, Dagon, ChrisHunt, and Sem kindly invested time and their knowledge to assist you. They deserve from you[ul][li]a response to their contributions,[li][/li]your thanks,[li][/li]and (where appropriate) a

[/li][/ul]


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”