Re: Help with trigger compilation errors

    Tim,

  The first table has a column named USER, and the second does not. You should probably change your trigger for the tables that have this column. Find a name that does not correspond to any column in any table (you can check all columns' names at DBA_TABLE_COLUMNS - I guess this is the table name).

    HTH,             Eduardo Pimenta.

  tim.mcconechy_at_runtime.dk wrote:
> 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
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Apr 09 1999 - 07:53:11 CDT