Re: enabling triggers

From: Greg DeMent <dementg_at_usa.net>
Date: Thu, 30 Nov 2000 23:38:51 -0800
Message-ID: <3A27558B.3BBFBEB0@usa.net>

Thanks to everybody for clearing that up. It's working now, almost. I'm getting a warning message though, here's what I'm entering:

LOGIT table is defined as:

key                    char(20)

log_comment char(20)

member.ssn is a char(9)

SQL> create trigger tr_log
  2 after insert on member
  3 for each row
  4 insert into logit values(new.ssn, 'INSERTED');   5 /

Warning: Trigger created with compilation errors.

Does this mean that PL/SQL hasn't been properly set up, and I need to get the CATPROC.SQL script to run first? I get the same warning if I replace new.ssn with a literal string.

When I login as SYS and run CATPROC.SQL, the last line is marked with ORA-01756: quoted string not properly terminated. I'm sure that script should work, I guess I'm just demonstrating more ignorance. :) Thanks for any and all help.

Greg

Brian Tkatch wrote:

> On Wed, 29 Nov 2000 16:37:50 -0800, Greg DeMent <dementg_at_usa.net>
> wrote:
>
> >In SQL*Plus, 8i
> >
> >When I try to create a trigger, I get to the final semicolon and oracle
> >doesn't respond to it. e.g.
> >
> >CREATE TRIGGER TR_LOG
> > AFTER INSERT ON MEMBER
> > FOR EACH ROW
> > BEGIN
> > INSERT INTO ... ;
> > END;
> >;
> >;
> >(I can type semicolons all day, and it never accepts the trigger
> >statement)
> >Eventually I have to type <CTRL>-C to break back to the SQL prompt. I
> >have read that I'm supposed to run CATPROC.SQL while logged in as SYS to
> >enable triggers. When I do that, I get a "quoted string not properly
> >terminated" error from the script. I've also tried running
> >DBMSSTDX.SQL, as the documentation suggests, and it also throws an error
> >regarding an undeclared variable.
> >
> >How do I get this thing to accept triggers? As you can probably tell,
> >I'm new at this. Thanks.
> >
>
> Semicolons are the end of line character. Since SQL statements can
> only be one line, a semicolon tells the system that you are done
> writing the statement.
>
> A trigger, however, is a multi line entity, and a semicolon, while
> ending a line, does not necessarily mean that the trigger is finished.
> Therefore, you must use another character. PL/SQL uses the forward
> slash on its own line to denote the end of intput, or to run the code
> in the buffer.
>
> CREATE TRIGGER TR_LOG
> AFTER INSERT ON MEMBER
> FOR EACH ROW
> BEGIN
> INSERT INTO ... ;
> END;
> /
>
> HTH,
> Brian
Received on Fri Dec 01 2000 - 01:38:51 CST