Path: news.easynews.com!easynews!newshub2.home.com!news.home.com!dispose.news.demon.net!demon!xara.net!gxn.net!blue.nl.gxn.net!cyan.nl.gxn.net!not-for-mail
From: j.w.vandijk@hetnet.nl (Jaap W. van Dijk)
Newsgroups: comp.databases.oracle.server
Subject: Re: Troublesome Trigger
Date: Sat, 02 Feb 2002 23:09:12 GMT
Organization: XO Communications B.V.
Lines: 65
Message-ID: <3c5c712c.789650@news.jaapwvandijk.myweb.nl>
References: <a3hmk8$mgq$1@nntp9.atl.mindspring.net>
NNTP-Posting-Host: asd-tel-ap01-d13-013.dial.freesurf.nl
X-Trace: cyan.nl.gxn.net 1012691037 12126 62.100.12.13 (2 Feb 2002 23:03:57 GMT)
X-Complaints-To: abuse@freesurf.nl
NNTP-Posting-Date: 2 Feb 2002 23:03:57 GMT
X-Newsreader: Forte Free Agent 1.21/32.243
Xref: easynews comp.databases.oracle.server:133702
X-Received-Date: Sat, 02 Feb 2002 16:07:53 MST (news.easynews.com)

Do you have an empty line between the INSERT line and the VALUES line?

I don't think this is allowed within an SQL statement.

Jaap.

On Sat, 2 Feb 2002 15:42:36 -0800, "William O'Neill"
<wfoneill@mindspring.com> wrote:

>Am using Oracle9i(Enterprise) on WIN2K with Powerbuilder 8.0.1.  Am trying
>to create this trigger which basically inserts data into an audit table,
>based on inserting a new record into the SERVICE table.  Here is a fragment
>of my trigger:
>CREATE OR REPLACE TRIGGER trg_service_service_audit_log
>
>AFTER INSERT OR UPDATE OR DELETE ON service
>
>FOR EACH ROW
>
>DECLARE
>
>StatementType CHAR(1);
>
>var_serv_id NUMBER;
>
>var_service CHAR(4);
>
>BEGIN
>
>IF INSERTING THEN
>
>StatementType := 'I';
>
>Select seq_service_serv_id.currval into :=var_serv_id from dual;
>
>Select to_char(seq_service_serv_id.currval) into :=var_service from dual;
>
>
>
>Insert into service_audit_log
>
>
>VALUES(seq_service_audit_log_log_no.nextval,var_serv_id,
>
>:new.serv_name,var_service,
>
>:new.servtype,:new.street1,
>
>:new.street2,:new.town,:new.state,:new.zipcode,:new.region,
>
>:new.district,:new.phone,:new.cont1_title,:new.contact1,
>
>:new.contact2,:new.contact3,:new.contact4,:new.contact5,
>
>:new.rec_hosp,:new.lic_exp,:new.transport,sysdate,
>
>sysdate,:new.user_id,:new.emr_phone,:new.archive,:new.user_id,sysdate,
>
>StatementType);
>
>Keep getting a compile error, eg. "Invalid SQL Statement."  Any ideas...
>
>
>