Re: enabling triggers

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 Thu Nov 30 2000 - 07:46:29 CST