Trigger problem

[puneet]

Hi

Create or replace trigger order_hist
before insert or update of ORPSTMP,ORUSTMP,ORSTAT on orders
for each statement
begin
insert into RF_ORD_HIST
select orrfnbr,orstat,ORUSTMP from orders;
commit;
end;
/


I am getting a table mutatiion here. Let me know what to do . Will declaring teh variables help me here

Let me know

[marist89]

What are you trying to do?

[rsuri]

is there any trigger on RF_ORD_HIST ?

- Rajeev

[jmodic]

[QUOTE][i]Originally posted by puneet [/i]
[B]Hi

Create or replace trigger order_hist
before insert or update of ORPSTMP,ORUSTMP,ORSTAT on orders
for each statement
begin
insert into RF_ORD_HIST
select orrfnbr,orstat,ORUSTMP from orders;
commit;
end;
/


I am getting a table mutatiion here. Let me know what to do . Will declaring teh variables help me here

Let me know [/B][/QUOTE]

1. If you wouldn't get table-mutating error, you would get some other ORA message telling you that you can't use COMMIT or ROLLBACK insde a trigger (unles you would implement it as autonomus transaction).

2. There is something wrong with the logic of your trigger. Before any insert statement you are trying to save some column values from *all* of the rows of your triggering table (you have no WHERE clause!) - is this realy what you want to do?

[puneet]

Hi Back

Nope I just want the following values in ord_hist table whenever the orstat in orders table is updated or a new row is inserted in it .

Create or replace trigger order_hist
before insert or update of ORSTAT on orders
for each row
begin
insert into RF_ORD_HIST
select orrfnbr,orstat,sysdate from orders;
end;

[akkerend]

If you want to insert sysdate and new values of orrfnbr and orstat, in ord_hist table on every update of orstat and insert on orders, use :new qualifier for new values.

CREATE OR REPLACE TRIGGER order_hist
BEFORE INSERT OR UPDATE OF orstat ON orders
FOR EACH ROW
BEGIN
INSERT INTO rf_ord_hist
VALUES(:new.orrfnbr,:new.orstat,sysdate);
END;
/