Oracle 10 g
-- I got a funky error trying to create the trigger within the package
-- the code ran but the trigger was invalid
-- with an error of
-- PLS-00103 Encountered the symbol '''' when expected one fo the following:
-- begin function package pragma procedure subtype use
-- <an identifier><a double-quoted delimited-identifier>form
-- current cursor
-- the error says it is on the DECLARE line
-- touching any line and recompiling, the error goes away
-- not sure why
An alter trigger compile externally won't compile it also
Here is the code:
CREATE OR REPLACE
PROCEDURE ceg_iface_cr8trig_proc (table_name IN VARCHAR2
, in_sysid IN VARCHAR2 DEFAULT 'EXTSYS2'
, in_action IN VARCHAR2 DEFAULT 'Add'
, in_sender_field IN VARCHAR2 DEFAULT 'sendersysid'
)
AS
v_create_trigger VARCHAR2(4000);
CRLF VARCHAR2(2) := CHR(13)||CHR(10);
v_trigger_name VARCHAR2(100);
BEGIN
v_trigger_name := 'ceg_' || lower(table_name) || '_q_b4trig';
v_create_trigger := 'CREATE OR REPLACE TRIGGER ' || v_trigger_name || ' ' || CRLF;
v_create_trigger := v_create_trigger || 'BEFORE INSERT ON ' || lower(table_name) || ' ' || CRLF;
v_create_trigger := v_create_trigger || 'REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW ' || CRLF;
v_create_trigger := v_create_trigger || 'DECLARE ' || CRLF;
v_create_trigger := v_create_trigger || ' myTransID NUMBER; ' || CRLF;
v_create_trigger := v_create_trigger || 'BEGIN' || CRLF;
v_create_trigger := v_create_trigger || ' -- Handle inbound transactions ' || CRLF;
v_create_trigger := v_create_trigger || ' IF :NEW.TransID IS NULL AND upper(:NEW.' || in_sender_field || ') = ''' || upper(in_sysid) || ''' THEN' || CRLF;
v_create_trigger := v_create_trigger || ' SELECT maxifacetransseq.NEXTVAL ' || CRLF;
v_create_trigger := v_create_trigger || ' INTO myTransID ' || CRLF;
v_create_trigger := v_create_trigger || ' FROM dual; ' || CRLF;
v_create_trigger := v_create_trigger || CRLF;
v_create_trigger := v_create_trigger || ' :NEW.transID:= myTransID;-- set the transaction ID' || CRLF;
v_create_trigger := v_create_trigger || CRLF;
v_create_trigger := v_create_trigger || ' INSERT INTO mxin_inter_trans(extsysname,ifacename,transid,action)' || CRLF;
v_create_trigger := v_create_trigger || ' VALUES (''' ||upper(in_sysid) || ''',''' || upper(table_name) || ''', myTransID ,''' || in_action || ''');' || CRLF;
v_create_trigger := v_create_trigger || CRLF;
v_create_trigger := v_create_trigger || ' IF:NEW.transseq IS NULL THEN -- no seq set' || CRLF;
v_create_trigger := v_create_trigger || ' :NEW.transseq:= 1;' || CRLF;
v_create_trigger := v_create_trigger || ' END IF;' || CRLF;
v_create_trigger := v_create_trigger || ' END IF;' || CRLF;
v_create_trigger := v_create_trigger || CRLF;
v_create_trigger := v_create_trigger || 'END;' || CRLF;
-- if you spool the output you can just grab the code and run it separately
DBMS_OUTPUT.put_line(v_create_trigger);
--
execute IMMEDIATE v_create_trigger;
END;
ASKER