Re: Can't create triggers

From: Jac. _at_ xs4all <_at_>
Date: Sat, 10 Oct 1998 13:27:45 +0200
Message-ID: <6vngbc$m93$1@news2.xs4all.nl>

Hi Ken,

sequences can only be used in SQL, not in PL/SQL. So you have to use a SELECT statement to retrieve the next value. Your trigger will compile it you change it to:

CREATE OR REPLACE TRIGGER COMMS_KEY_BEF_INS_ROW BEFORE INSERT ON COMMS_KEY
FOR EACH ROW
BEGIN
SELECT SEQ_COMMS_KEY.NEXTVAL
INTO :NEW.COMM_ID
FROM DUAL;

       :NEW.ENTRY_DATE := SYSDATE;
       :NEW.ENTRY_BY := USER;

END;
/

Good luck

Jac.

Ken Rachynski wrote in message <361e8e2c.0_at_news.cadvision.com>...
>Good day,
>
>I have been trying without luck to figure out why my triggers won't compile
>at all. I am trying the following trigger:
>
>CREATE OR REPLACE TRIGGER COMMS_KEY_BEF_INS_ROW
>BEFORE INSERT ON COMMS_KEY
>FOR EACH ROW
>BEGIN
> :NEW.COMM_ID := SEQ_COMMS_KEY.NEXTVAL;
> :NEW.ENTRY_DATE := SYSDATE;
> :NEW.ENTRY_BY := USER;
>END;
>
>This is a fairly new installation of Oracle 8.0.4.0. Looking at my trigger,
>it is formatted properly for access, although I'm not sure about the
>sequence. I did check the documentation on the error I was receiving
>(trigger created with compilation errors) and ran the script that
apparently
>I'm supposed to know about. Other ideas on this?
>
>--
>Ken Rachynski
>Database Analyst/Developer
>krachyn_at_cadvision.com
><http://www.cadvision.com/krachyn>
>ICQ: 3113514
>
>
>
Received on Sat Oct 10 1998 - 06:27:45 CDT