Insert Trigger Help

[mortonsa]

Hello,

Here is what I am trying to do: I have a table called tCase. Anytime a user tries to update specific fields in this table I want the information currently on the record to be written to a history table before the update is allowed.

I keep getting an error that my trigger is invalid and I can not seem to debug it. Here is my code:

CREATE OR REPLACE TRIGGER ECC.UPD_CASEEMPLOYMENT
BEFORE UPDATE OF EMPLOYERIDY,PERSONEMPLOYMENTIDY
ON ECC.TCASE
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
Insert into tCaseHistory (CASEHISTORYIDY,CASEIDY,PERSONEMPLOYMENTIDY,EMPLOYERIDY,USERNAME,RECORDDATE)
(select ld.CASEHISTORYIDY,ld.CASEIDY,ld.PERSONEMPLOYMENTIDY,ld.EMPLOYERIDY,ld.USERNAME,ld.RECORDDATE
from tcase
where caseidy = ld.caseidy);
END;

Can anyone help or point me in the right direction?

Thanks!

 

[carp]

Take the ld off of your select list:

(select CASEHISTORYIDY,CASEIDY,PERSONEMPLOYMENTIDY,EMPLOYERIDY,USERNAME,RECORDDATE
from tcase
where caseidy = ld.caseidy);

You might even be able to get away with NO selects:

Insert into tCaseHistory (CASEHISTORYIDY,CASEIDY,PERSONEMPLOYMENTIDY,EMPLOYERIDY,USERNAME,RECORDDATE)
VALUES old.CASEHISTORYIDY,ld.CASEIDY,ld.PERSONEMPLOYMENTIDY,ld.EMPLOYERIDY,ld.USERNAME,ld.RECORDDATE);

 

[jee]

Carp is correct in his second statement, the select is not needed (and would probably generate a mutating table error). Both the ld and :new values for the row just updated are automatically available. So simply insert the ld values into the history table.

 

[mortonsa]

Carp,

I tried both of your suggestions and I am still getting the following error message:

ORA-04098: trigger 'UPD_CASEEMPLOYMENT' is invalid and failed re-validation.

Right now the code looks like:

CREATE OR REPLACE TRIGGER ECC.UPD_CASEEMPLOYMENT
BEFORE UPDATE OF EMPLOYERIDY,PERSONEMPLOYMENTIDY
ON ECC.TCASE
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
Insert into tCaseHistory (CASEHISTORYIDY,CASEIDY,PERSONEMPLOYMENTIDY,EMPLOYERIDY,USERNAME,RECORDDATE)
Values old.CASEHISTORYIDY,ld.CASEIDY,ld.PERSONEMPLOYMENTIDY,ld.EMPLOYERIDY,ld.USERNAME,ld.RECORDDATE);
END;

Do you see anything that I am doing wrong?

 

[sem]

You may omit "REFERENCING OLD AS OLD NEW AS NEW
" line. Check also whether column names are correct and EXIST in TCASE (not tCaseHistory) table.

 

[mortonsa]

Sem,

You picked up on something I overlooked, I do not have a CASEHISTORYIDY field in the tCase table. I rewrote my trigger and now it is no longer showing up as invalid but I am still getting an error when I attempt to update the tCase table. The error I get now is:

ORA-04091: table ECC.TCASE is mutating, trigger/function may not see it
ORA-06512: at "ECC.UPD_CASEEMPLOYMENT", line 5
ORA-04088: error during execution of trigger 'ECC.UPD_CASEEMPLOYMENT'

Here is my code at present:

CREATE OR REPLACE TRIGGER ECC.UPD_CASEEMPLOYMENT
BEFORE UPDATE OF EMPLOYERIDY,PERSONEMPLOYMENTIDY
ON ECC.TCASE
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
declare
new_serial number(9);
BEGIN
Select seq_tcasehistory.nextval into new_serial from dual;
Insert into tCaseHistory (CASEHISTORYIDY,CASEIDY,PERSONEMPLOYMENTIDY,EMPLOYERIDY,USERNAME,RECORDDATE)
Values (new_serial,ld.CASEIDY,ld.PERSONEMPLOYMENTIDY,ld.EMPLOYERIDY,ld.USERNAME,ld.RECORDDATE);
END;

Do you see what I'm doing wrong?

 

[sem]

Do you have a foreign key constraint on tCaseHistory?
If yes, try to drop it (you'll have to drop it anyway if you try to move the case from TCASE table as it may become too large). If you do need it, the technique is a bit harder. It was discussed earlier, try to look for it.

 

[mortonsa]

Hi Sem,

Yes, I do have a foreign key constraint on the tCase table. I disabled it and the trigger works as it is supposed to. Unfortunately, I do need that constraint. I have been looking through the forum and other books and I am a little confused about whether I am going to have to create a temp table, package, statement level trigger or all of the above! I have been playing around a little bit but I don't really understand what I'm doing all that well. Could you possibly point me in the right direction so I know what avenue I should be going down?

Thanks

 

[sem]

You may create temporary table but IMHO the better way is to create package with pl/sql table to store changed rows there for "deffered" processing. This "array" is filled from row-level trigger and then finally processed in after statement-level trigger. The idea is that during row-level processing the table is "mutating" but during statement-level processing the table is "stable" i.e. you know the rows it contains so you also know that your constraints are not violated. So the answer is yes, you should create package, temp table (packaged variable), row-level trigger to fill this variable and statement-level triggers: before statement to set some flag to avoid recursions and after-statement - to loop through your array and issue single-row commands or check some conditions. You may also clear the array there.

 

[oracleraj]

hi
The solution for handling mutating trigger is given in
oracle press's pl/sql book fo r7.x/8/8i (of any version).
try it out mortonsa
all the best

 

[blom0344]

I know this is a very old thread, but I have almost the same requirements as Mortonsa, with the added requirement to write a record to a history table AFTER an update of a status field , but only if the status field changes from null to a certain value. Is this possible to achieve with a trigger?

The second issue is that the SQL that is called in the trigger runs over two tables and requires about a minute to finish when run from TOAD. I suspect this to be far too long with possible risk of flooding the database with subsequent SQL-calls. Anyway is calling such an SQL a viable solution?

The trigger should be like:

CREATE OR REPLACE TRIGGER COM.UPD_STATUS
AFTER UPDATE OF MOV_REASON (to .....? missing syntax)
ON COM.STOCK_MOVEMENT
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
Insert into xxxxxx(.......,...........,.........,.......)
(SELECT A.STK_ID,A.STK_LOC_ID,A.STK_PRO_CODE,A.STK_QTY FROM STOCK A,STOCK_MOVEMENT B
WHERE A.STK_LOC_ID = B.MOV_TO_LOC_ID AND
A.STK_ZON_ID = 'BPI' AND STK_STAGE <> '90' AND
A.STK_LOC_ID = '05-AH-07-09-2' AND B.MRA_TYPE LIKE 'P%');
END;







T. Blom
Information analyst
tbl@shimano-eu.com