Re: Troublesome Trigger

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_at_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...
>
>
>
Received on Sat Feb 02 2002 - 17:09:12 CST