Trigger Question

From: Scott Blankenship <sjblanky_at_ix.netcom.com>
Date: Fri, 23 Apr 1999 15:51:45 -0400
Message-ID: <7fqj0v$bcl@dfw-ixnews7.ix.netcom.com>

I have a trigger, that I've basically modeled from a working trigger. When I run the sql file to create the trigger, it shows a compile error. How do I get more information about the compile error? I do 'show errors', and it says no errors...

SQL> describe iqfrs.int_network_identifier

 Name                            Null?    Type
 ------------------------------- -------- ----
 NODE_SID                        NOT NULL NUMBER(6)
 NETWORK_ID                      NOT NULL CHAR(2)
 COUNTRY_CODE                    NOT NULL CHAR(3)
 INTERCHANGE_NETWORK_SID         NOT NULL NUMBER(6)

SQL> describe iqfrs.network_reconciliation
 Name                            Null?    Type
 ------------------------------- -------- ----
 NODE_SID                        NOT NULL NUMBER(6)
 TRANS_DATE                      NOT NULL NUMBER(10)
 TERMINAL_IDENTIFIER             NOT NULL VARCHAR2(10)
 TERM_SERIAL_NUMBER              NOT NULL VARCHAR2(8)
 CUSTOMER_ID_NUM                 NOT NULL VARCHAR2(19)
 TRANS_AMOUNT                    NOT NULL NUMBER(12)
 RESULT_CODE                              CHAR(1)
 REASON_CODE                              CHAR(2)
 TERMINAL_ADDRESS                         VARCHAR2(18)
 TERMINAL_CITY                            VARCHAR2(13)
 TERMINAL_STATE                           CHAR(2)
 TERMINAL_COUNTRY_CODE           NOT NULL CHAR(3)
 NETWORK_ID                      NOT NULL CHAR(2)
 FEE_DISCOUNT_AMT                         CHAR(6)
 FROM_ACC_QUALIFIER                       CHAR(2)
 FROM_ACC                                 VARCHAR2(19)
 TO_ACC_QUALIFIER                         CHAR(2)
 TO_ACC                                   VARCHAR2(19)
 INTERCHANGE_NETWORK_SID                  NUMBER(6)


SQL> @network_reconciliation_trigger
SQL>
SQL> spool network_reconciliation_trigger.log
SQL> set verify on

SQL>
SQL> CREATE OR REPLACE TRIGGER T_NetworkReconciliation   2 BEFORE INSERT or UPDATE ON iqfrs.network_reconciliation   3 FOR EACH ROW
  4 DECLARE

  5     NetworkId     CHAR(2);
  6     CountryCode   CHAR(3);
  7     NodeSid       NUMBER(6);
  8     InterNetSid   NUMBER(6);
  9     State         CHAR(2);

 10
 10 BEGIN
 11
 11
 11 IF INSERTING THEN
 12

 12           NetworkId     := :new.network_id;
 13           CountryCode   := :new.terminal_country_code;
 14           NodeSid       := :new.node_sid;
 15           State         := :new.terminal_state;
 16
 16        ELSIF UPDATING THEN
 17
 17           NetworkId     := :old.network_id;
 18           CountryCode   := :old.terminal_country_code;
 19           NodeSid       := :old.node_sid;
 20           State         := :old.terminal_state;
 21
 21        END IF;
 22
 22        IF NetworkId IN

('01','02','03','06','07','08','09','13','14','24') THEN
 23
 23 IF State IN
('DE','IL','IN','KY','NJ','OH','PA','WI','WV','NY') THEN
 24
 24 InterNetSid = 1; -- MAC REGION A  25
 25 ELSE
 26
 26 InterNetSid = 2; -- MAC REGION B  27
 27 END IF;
 28
 28 ELSE
 29

 29           BEGIN
 30             SELECT interchange_network_sid
 31               INTO InterNetSid
 32               FROM iqfrs.int_network_identifier
 33              WHERE network_id = NetworkId
 34                AND country_code = CountryCode
 35                AND node_sid = NodeSid;
 36
 36           EXCEPTION
 37              WHEN NO_DATA_FOUND THEN
 38                 InterNetSid = -1;
 39
 39              WHEN OTHERS THEN
 40                 RAISE;
 41
 41           END;
 42
 42        END IF;
 43
 43        -- Set the interchange_network_sid value based on the results
above.
 44        :new.interchange_network_sid := InterNetSid;

 45
 45 END;
 46 /

Warning: Trigger created with compilation errors.

SQL> show errors
No errors.

SQL> spool off
SQL>
SQL> --quit;
SQL>

Received on Fri Apr 23 1999 - 14:51:45 CDT