-
PL/SQL: ORA-00984: column not allowed here
guys,
trigger failing with Ora- below. Can't figure out why. see table desc
thanks...
SQL> CREATE OR REPLACE TRIGGER MONOWNER.TRG_TEST
2 before UPDATE OR DELETE
3 ON MONOWNER.TMON_TEST03
4 REFERENCING OLD AS OLD NEW AS NEW
5 FOR EACH ROW
6 DECLARE
7 ACT_ID_CUR number;
8 ORG_USR_NU_CUR VARCHAR2(8);
9 ORG_USR_ACV_TS_CUR DATE;
10 LTS_UPD_USR_NU_CUR VARCHAR2(8);
11 LTS_UPD_USR_TS_CUR DATE;
12 BEGIN
13 IF UPDATING then
14 select ACT_ID, ORG_USR_NU, ORG_USR_ACV_TS, LTS_UPD_USR_NU, LTS_UPD_USR_TS
15 into ACT_ID_CUR, ORG_USR_NU_CUR, ORG_USR_ACV_TS_CUR, LTS_UPD_USR_NU_CUR, LTS_UPD_USR_TS
_CUR
16 from TMON_TEST03 where act_id = :NEW.ACT_ID;
17 IF :NEW.ACT_ID = :OLD.ACT_ID THEN
18 insert into MONOWNER.TMON_TEST04 values
19 (ACT_ID, :NEW.ORG_USR_NU, :NEW.ORG_USR_ACV_TS,'LTS_UPD_USR_NU',LTS_UPD_USR_TS);
20 END IF;
21 END IF;
22 end;
23 /
Warning: Trigger created with compilation errors.
SQL> sho errors
Errors for TRIGGER MONOWNER.TRG_TEST:
LINE/COL ERROR
-------- -----------------------------------------------------------------
13/11 PL/SQL: SQL Statement ignored
14/73 PL/SQL: ORA-00984: column not allowed here
SQL> desc TMON_TEST03
Name Null? Type
----------------------- -------- ------------
ACT_ID NOT NULL NUMBER
ORG_USR_NU NOT NULL VARCHAR2(8)
ORG_USR_ACV_TS NOT NULL DATE
LTS_UPD_USR_NU NOT NULL VARCHAR2(8)
LTS_UPD_USR_TS NOT NULL DATE
Last edited by Tuma; 02-10-2009 at 06:19 PM.
Looking for the greatest evil in the world? Look in the mirror.
-
sridhar
hi,
There is a mistake in your insert statement.
Look at the code below,its working....
CREATE OR REPLACE TRIGGER MONOWNER.trg_test
BEFORE UPDATE OR DELETE
ON MONOWNER.tmon_test03
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
act_id_cur NUMBER;
org_usr_nu_cur VARCHAR2 (8);
org_usr_acv_ts_cur DATE;
lts_upd_usr_nu_cur VARCHAR2 (8);
lts_upd_usr_ts_cur DATE;
BEGIN
IF UPDATING
THEN
SELECT act_id, org_usr_nu, org_usr_acv_ts,
lts_upd_usr_nu, lts_upd_usr_ts
INTO act_id_cur, org_usr_nu_cur, org_usr_acv_ts_cur,
lts_upd_usr_nu_cur, lts_upd_usr_ts_cur
FROM MONOWNER.tmon_test03
WHERE act_id = :NEW.act_id;
IF :NEW.act_id = :OLD.act_id
THEN
INSERT INTO MONOWNER.tmon_test04
VALUES (act_id_cur, :NEW.org_usr_nu, :NEW.org_usr_acv_ts,
'LTS_UPD_USR_NU', lts_upd_usr_ts_CUR);
END IF;
END IF;
END;
-
sridhar,
Thanks a bundle. I really appreciate it.
Looking for the greatest evil in the world? Look in the mirror.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|