Re: Help with trigger compilation errors

If you drop the column DATE from PDMLOG everyting will be O.K. Do not use 'Oracle words' for the names of your objects, columns,... It can cause problems.

tim.mcconechy_at_runtime.dk wrote in article <7ed3ij$ted$1_at_nnrp1.dejanews.com>...
> Hello,
>
> This code usually works fine: CREATE OR REPLACE TRIGGER
pdmlog_audit_info
> BEFORE INSERT OR UPDATE ON "PDMLOG" FOR EACH ROW BEGIN IF INSERTING
THEN
> :new.user_name := user; :new.lastedit := sysdate; ELSIF UPDATING THEN
> :new.user_name := user; :new.lastedit := sysdate; END IF; END; / ERROR
at
> line 2: ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553:
> PLS-320: the declaration of the type of this expression is incomplete or
> malformed
>
> I run this only changing pdmlog to the table name....
>
> For some reason the trigger won't work on two tables out of 130.
> PDMLOG is one of them...
>
> Here is the code to duplicate the problem.
>
> DROP TABLE "PDMLOG"
> /
>
> CREATE TABLE "PDMLOG" (
> "LOGNUM" number(6) NOT NULL,
> "USER" varchar2(5) NOT NULL,
> "PDMNAME" varchar2(5) NOT NULL,
> "DATABASE" varchar2(10) NOT NULL,
> "TYPE" varchar2(30) NULL,
> "DATE" date NOT NULL,
> "TIME" varchar2(8) NOT NULL,
> "MAINKEY" varchar2(125) NULL,
> "OLINEKEY" varchar2(180) NULL,
> "NLINEKEY" varchar2(180) NULL,
> "DATA" varchar2(2000) NULL,
> "STATUS1" varchar2(40) NULL,
> "STATUS2" varchar2(40) NULL,
> "ADMSTATUS" varchar2(40) NULL,
> "ADMSTATUS2" varchar2(40) NULL,
> "ADMCODE" varchar2(8) NULL,
> "CHANGED" NUMBER(10) NULL,
> "RECNO" number(20) NULL,
> "USER_NAME" number(2) NULL,
> "LASTEDIT" varchar2(20) NULL
> )
> PCTFREE 20
> PCTUSED 40
> /
>
> CREATE INDEX pdmlogmainkeyidx ON "PDMLOG" (mainkey)
>
> GRANT SELECT , INSERT , DELETE , UPDATE ON "PDMLOG" TO public
> /
>
> DROP SEQUENCE pdmlogseq
> /
>
> CREATE SEQUENCE pdmlogseq INCREMENT BY 1
> /
>
> CREATE OR REPLACE TRIGGER pdmlog_audit_info BEFORE INSERT OR UPDATE ON
> "PDMLOG" FOR EACH ROW BEGIN IF INSERTING THEN :new.user_name := user;
> :new.lastedit := sysdate; ELSIF UPDATING THEN :new.user_name := user;
> :new.lastedit := sysdate; END IF; END; /
>
> ERROR at line 2: ORA-06552: PL/SQL: Compilation unit analysis terminated
> ORA-06553: PLS-320: the declaration of the type of this expression is
> incomplete or malformed
>
>
> However , If I take another table....
> DROP TABLE "PICTURE"
> /
>
> CREATE TABLE "PICTURE" (
> "MAINKEY" varchar2(125) NOT NULL,
> "PICTPICT01" varchar2(100) NOT NULL,
> "FPICTCOM1" varchar2(40) NOT NULL,
> "CPICTCOM1" varchar2(40) NOT NULL,
> "PICTPICT02" varchar2(100) NOT NULL,
> "FPICTCOM2" varchar2(40) NOT NULL,
> "CPICTCOM2" varchar2(40) NOT NULL,
> "PICTPICT03" varchar2(100) NOT NULL,
> "FPICTCOM3" varchar2(40) NOT NULL,
> "CPICTCOM3" varchar2(40) NOT NULL,
> "PICTPICT04" varchar2(100) NOT NULL,
> "FPICTCOM4" varchar2(40) NOT NULL,
> "CPICTCOM4" varchar2(40) NOT NULL,
> "PICTPICT05" varchar2(100) NOT NULL,
> "FPICTCOM5" varchar2(40) NOT NULL,
> "CPICTCOM5" varchar2(40) NOT NULL,
> "PICTPICT06" varchar2(100) NOT NULL,
> "FPICTCOM6" varchar2(40) NOT NULL,
> "CPICTCOM6" varchar2(40) NOT NULL,
> "PICTPICT07" varchar2(100) NOT NULL,
> "FPICTCOM7" varchar2(40) NOT NULL,
> "CPICTCOM7" varchar2(40) NOT NULL,
> "PICTPICT08" varchar2(100) NOT NULL,
> "FPICTCOM8" varchar2(40) NOT NULL,
> "CPICTCOM8" varchar2(40) NOT NULL,
> "PICTPICT09" varchar2(100) NOT NULL,
> "FPICTCOM9" varchar2(40) NOT NULL,
> "CPICTCOM9" varchar2(40) NOT NULL,
> "PICTPICT10" varchar2(100) NOT NULL,
> "FPICTCOM10" varchar2(40) NOT NULL,
> "CPICTCOM10" varchar2(40) NOT NULL,
> "PICTPICT11" varchar2(100) NOT NULL,
> "FPICTCOM11" varchar2(40) NOT NULL,
> "CPICTCOM11" varchar2(40) NOT NULL,
> "PICTPICT12" varchar2(100) NOT NULL,
> "FPICTCOM12" varchar2(40) NOT NULL,
> "CPICTCOM12" varchar2(40) NOT NULL,
> "LASTEDIT1" varchar2(15) NULL,
> "LASTEDIT2" varchar2(15) NULL,
> "CHANGED" NUMBER(10) NULL,
> "RECNO" number(20) NULL,
> "USER_NAME" number(2) NULL,
> "LASTEDIT" varchar2(20) NULL
> )
> PCTFREE 20
> PCTUSED 40
> /
>
> DROP TABLE pictureimages
> /
>
> CREATE TABLE pictureimages ( MAINKEY varchar2(125) NOT NULL, CODE
number(2)
> NOT NULL,PICGPICT LONG RAW NOT NULL) /
>
> CREATE INDEX pictureimagesidx ON pictureimages (MAINKEY)
> /
>
> CREATE INDEX picturemainkeyidx ON "PICTURE" (mainkey)
>
> GRANT SELECT , INSERT , DELETE , UPDATE ON "PICTURE" TO public
> /
>
> DROP SEQUENCE pictureseq
> /
>
> CREATE SEQUENCE pictureseq INCREMENT BY 1
> /
>
> CREATE OR REPLACE TRIGGER picture_audit_info BEFORE INSERT OR UPDATE ON
> "PICTURE" FOR EACH ROW BEGIN IF INSERTING THEN :new.user_name := user;
> :new.lastedit := sysdate; ELSIF UPDATING THEN :new.user_name := user;
> :new.lastedit := sysdate; END IF; END; /
>
> It works fine.....
>
> Can anyone help me????
> I trieed pdmlog instead of "PDMLOG"
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
 

>
Received on Tue Apr 06 1999 - 21:52:32 CDT