| After Insert Trigger [message #36882] |
Mon, 31 December 2001 04:15  |
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  |
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))
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
