Home » SQL & PL/SQL » SQL & PL/SQL » After Insert Trigger
After Insert Trigger [message #36882] Mon, 31 December 2001 04:15 Go to next message
Fred
Messages: 34
Registered: August 1999
Member
I have a problem with this tg:

TRIGGER MY_TG_INSDEL
AFTER INSERT OR DELETE ON MY_TABLE_1
FOR EACH ROW
DECLARE
wNAME VARCHAR2(50);
wGROUPID NUMBER;
wCAT NUMBER;
BEGIN
IF (INSERTING) THEN
SELECT FIELD_1 || ' (' || FIELD_2 || ')', TO_NUMBER(ASCII(SUBSTR(FIELD_2,0,1))||SUBSTR(FIELD_2,2,7))
INTO wNAME, wGROUPID
FROM MY_TABLE_1
WHERE FIELD_2 = :NEW.FIELD_2;
IF (:NEW.FIELD_2 LIKE 'E%') THEN
wCAT := 2;
END IF;
IF (:NEW.FIELD_2 LIKE 'P%') THEN
wCAT := 4;
END IF;
IF (:NEW.FIELD_2 LIKE 'S%') THEN
wCAT := 4;
END IF;
IF (:NEW.FIELD_2 LIKE 'H%') THEN
wCAT := 4;
END IF;
IF (:NEW.FIELD_2 LIKE 'F%') THEN
wCAT := 5;
END IF;
SPM4GRUPOSINS(wGROUPID, wNAME, wCAT);
ELSE
SPM4GRUPOSDEL(:OLD.FIELD_2);
END IF;
END;

Everytime I try yo make an insert on MY_TABLE_1 I keep getting this error msg:

[[INTERSOLV]][[ODBC Oracle driver]][[Oracle]]
ORA-04091: table MY_TABLE_1 is mutating, trigger/function may not see it
ORA-06512: at "MY_TG_INSDEL", line 9
ORA-04088: error during execution of trigger 'MY_TG_INSDEL'

I just canīt see where Iīm making a mistake.
TIA

Fred

----------------------------------------------------------------------
Re: After Insert Trigger [message #36883 is a reply to message #36882] Mon, 31 December 2001 06:51 Go to previous message
vk
Messages: 13
Registered: December 2001
Junior Member
You can't issue a select statement on the table on which trigger fires.
modify your code to get values into wNAME, wGROUPID like this.
wNAME :=:new.FIELD_1 || ' (' || :new.FIELD_2 || ')'
wGROUPID :=TO_NUMBER(ASCII(SUBSTR(:new.FIELD_2,0,1))||SUBSTR(:new.FIELD_2,2,7))

----------------------------------------------------------------------
Previous Topic: calling procedure giving problems
Next Topic: Problems with procedures- SQL statement.
Goto Forum:
  


Current Time: Thu Mar 28 15:05:33 CDT 2024