Archives of the TeradataForum

Message Posted: Wed, 04 Aug 1999 @ 16:12:23 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I apologize from the start for the length of this message.

Can anyone help with this trigger syntax? We have placed a call with our NCR Hardware VAR (BULL HN) and have not received any response after several days.

I am including a show of two tables involved as well as several attempts to create a valid trigger. We are trying to establish a before insert trigger that will check for a parent table entry and if it is not present, insert a 'System Inserted' entry so that valid RI exists.

Thanks in advance for any help.

Regards,

Dale Leake
Graybar Electric Co.
St. Louis MO USA

     show table graybar.customer_master;
     ---------------------------------------------------------------------------
     CREATE SET TABLE graybar.customer_master ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL
          (
           TDWS_IDN_C_COMP CHAR(8) CHARACTER SET LATIN CASESPECIFIC NOT NULL,
           CMST_CDE_C_COMP_TYPE CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL,
           CMST_NM_C_MASTER VARCHAR(40) CHARACTER SET LATIN CASESPECIFIC NOT NULL)
     UNIQUE PRIMARY INDEX RCMST_UPI ( TDWS_IDN_C_COMP ,CMST_CDE_C_COMP_TYPE );


     show table graybar.cust_bill_to;
     ---------------------------------------------------------------------------
     CREATE SET TABLE graybar.cust_bill_to ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL
          (
           F1000_IDN_LOC_C SMALLINT NOT NULL,
           F1000_PFX_C CHAR(3) CHARACTER SET LATIN CASESPECIFIC NOT NULL,
           CMST_CDE_C_COMP_TYPE CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL,
           F1000_CDE_C_TYPE CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL,
           F1000_CDE_CSR_RFRN CHAR(2) CHARACTER SET LATIN CASESPECIFIC NOT NULL,
           F1000_CDE_FNL_ASG CHAR(1) CHARACTER SET LATIN CASESPECIFIC NOT NULL,
           OMST_CDE_ORGN_COMP_TYPE_DIST CHAR(2) CHARACTER SET LATIN CASESPECIFIC NOT NULL,
           TDWS_IDN_C_COMP CHAR(8) CHARACTER SET LATIN CASESPECIFIC NOT NULL,
           F1000_IDN_C_XREF INTEGER NOT NULL,
           F1000_IDN_CPA INTEGER NOT NULL,
           F1000_IDN_CPA_XCP INTEGER NOT NULL,
           F1000_IDN_CUST INTEGER NOT NULL,
           LTBL_IDN_DIST BYTEINT NOT NULL,
           F1000_IDN_NATL_ACC INTEGER NOT NULL,
           TDWS_IDN_ORGN_COMP_DIST SMALLINT NOT NULL,
           F1000_NM_BL2 VARCHAR(25) CHARACTER SET LATIN CASESPECIFIC NOT NULL)
     UNIQUE PRIMARY INDEX R1000_UPI ( F1000_IDN_LOC_C ,F1000_PFX_C );


     REPLACE TRIGGER GRAYBAR.TI_CUST_BILL_TO_1 ENABLED BEFORE
      INSERT ON GRAYBAR.CUST_BILL_TO ORDER 1
      REFERENCING NEW AS INCOMING
      FOR EACH ROW WHEN  (0 <>
         INCOMING.F1000_IDN_NATL_ACC
         )
      (INSERT INTO GRAYBAR.CUSTOMER_MASTER (TDWS_IDN_C_COMP,
         CMST_CDE_C_COMP_TYPE,
         CMST_NM_C_MASTER)
       VALUES (INCOMING.TDWS_IDN_C_COMP,
         INCOMING.CMST_CDE_C_COMP_TYPE,
         'SYSTEM INSERTED '););

      *** Trigger has been created.
      *** Total elapsed time was 1 second.


     REPLACE TRIGGER GRAYBAR.TI_CUST_BILL_TO_1 ENABLED BEFORE
      INSERT ON GRAYBAR.CUST_BILL_TO ORDER 1
      REFERENCING NEW AS INCOMING
      FOR EACH ROW WHEN  (0 <>
         INCOMING.F1000_IDN_NATL_ACC
         )
      (INSERT INTO GRAYBAR.CUSTOMER_MASTER (TDWS_IDN_C_COMP,
         CMST_CDE_C_COMP_TYPE,
         CMST_NM_C_MASTER)
       VALUES (INCOMING.TDWS_IDN_C_COMP,
         INCOMING.CMST_CDE_C_COMP_TYPE,
         'SYSTEM INSERTED ' || current_date););
      *** Trigger has been replaced.
      *** Total elapsed time was 2 seconds.


     REPLACE TRIGGER GRAYBAR.TI_CUST_BILL_TO_1 ENABLED BEFORE
      INSERT ON GRAYBAR.CUST_BILL_TO ORDER 1
      REFERENCING NEW AS INCOMING
      FOR EACH ROW WHEN  (0 <>
         INCOMING.F1000_IDN_NATL_ACC
         AND 0 =
         (SELECT COUNT(*) FROM GRAYBAR.CUSTOMER_MASTER
          WHERE GRAYBAR.CUSTOMER_MASTER.TDWS_IDN_C_COMP = INCOMING.TDWS_IDN_C_COMP
          AND GRAYBAR.CUSTOMER_MASTER.CMST_CDE_C_COMP_TYPE = INCOMING.CMST_CDE_C_COMP_TYPE)
         )
      (INSERT INTO GRAYBAR.CUSTOMER_MASTER (TDWS_IDN_C_COMP,
         CMST_CDE_C_COMP_TYPE,
         CMST_NM_C_MASTER)
       VALUES (INCOMING.TDWS_IDN_C_COMP,
         INCOMING.CMST_CDE_C_COMP_TYPE,
         'SYSTEM INSERTED ' || current_date););

      *** Failure 3990 Table INCOMING is not specified in the FROM clause or
               already aliased by another name.  Statement# 1, Info =0
      *** Total elapsed time was 1 second.


     REPLACE TRIGGER GRAYBAR.TI_CUST_BILL_TO_1 ENABLED BEFORE
      INSERT ON GRAYBAR.CUST_BILL_TO ORDER 1
      REFERENCING NEW AS INCOMING
      FOR EACH ROW WHEN  (0 =
         (SELECT COUNT(*) FROM
     GRAYBAR.CUSTOMER_MASTER
         WHERE GRAYBAR.CUSTOMER_MASTER.TDWS_IDN_C_COMP = INCOMING.TDWS_IDN_C_COMP
         AND GRAYBAR.CUSTOMER_MASTER.CMST_CDE_C_COMP_TYPE = INCOMING.CMST_CDE_C_COMP_TYPE)
         )
      (INSERT INTO GRAYBAR.CUSTOMER_MASTER (TDWS_IDN_C_COMP,
         CMST_CDE_C_COMP_TYPE,
         CMST_NM_C_MASTER)
       VALUES (INCOMING.TDWS_IDN_C_COMP,
         INCOMING.CMST_CDE_C_COMP_TYPE,
         'SYSTEM INSERTED ' || current_date););

      *** Failure 3990 Table INCOMING is not specified in the FROM clause or
               already aliased by another name.  Statement# 1, Info =0
      *** Total elapsed time was 1 second.


     REPLACE TRIGGER GRAYBAR.TI_CUST_BILL_TO_1 ENABLED BEFORE
      INSERT ON GRAYBAR.CUST_BILL_TO ORDER 1
      REFERENCING NEW AS INCOMING
      FOR EACH ROW WHEN  (0 =
         (SELECT COUNT(*) FROM GRAYBAR.CUSTOMER_MASTER
         WHERE GRAYBAR.CUSTOMER_MASTER.TDWS_IDN_C_COMP = INCOMING.TDWS_IDN_C_COMP)
         )
      (INSERT INTO GRAYBAR.CUSTOMER_MASTER (TDWS_IDN_C_COMP,
         CMST_CDE_C_COMP_TYPE,
         CMST_NM_C_MASTER)
       VALUES (INCOMING.TDWS_IDN_C_COMP,
         INCOMING.CMST_CDE_C_COMP_TYPE,
         'SYSTEM INSERTED ' || current_date););

      *** Failure 3990 Table INCOMING is not specified in the FROM clause or
               already aliased by another name.  Statement# 1, Info =0
      *** Total elapsed time was 1 second.


     REPLACE TRIGGER GRAYBAR.TI_CUST_BILL_TO_1 ENABLED BEFORE
      INSERT ON GRAYBAR.CUST_BILL_TO ORDER 1
      REFERENCING NEW AS INCOMING
      FOR EACH ROW WHEN  (SELECT COUNT(*) FROM
     GRAYBAR.CUSTOMER_MASTER
         WHERE
      GRAYBAR.CUSTOMER_MASTER.TDWS_IDN_C_COMP =
          INCOMING.TDWS_IDN_C_COMP = 0
         )
      (INSERT INTO GRAYBAR.CUSTOMER_MASTER (TDWS_IDN_C_COMP,
         CMST_CDE_C_COMP_TYPE,
         CMST_NM_C_MASTER)
       VALUES (INCOMING.TDWS_IDN_C_COMP,
         INCOMING.CMST_CDE_C_COMP_TYPE,
         'SYSTEM INSERTED ' || current_date););

      *** Failure 3707 Syntax error, expected something like
               a '(' between the '(' and 'SELECT'.  Statement# 1, Info =153
      *** Total elapsed time was 1 second.


     REPLACE TRIGGER GRAYBAR.TI_CUST_BILL_TO_1 ENABLED BEFORE
      INSERT ON GRAYBAR.CUST_BILL_TO ORDER 1
      REFERENCING NEW AS INCOMING
      FOR EACH ROW WHEN  ((SELECT COUNT(*) FROM GRAYBAR.CUSTOMER_MASTER
         WHERE GRAYBAR.CUSTOMER_MASTER.TDWS_IDN_C_COMP = INCOMING.TDWS_IDN_C_COMP) = 0
         )
      (INSERT INTO GRAYBAR.CUSTOMER_MASTER (TDWS_IDN_C_COMP,
         CMST_CDE_C_COMP_TYPE,
         CMST_NM_C_MASTER)
       VALUES (INCOMING.TDWS_IDN_C_COMP,
         INCOMING.CMST_CDE_C_COMP_TYPE,
         'SYSTEM INSERTED ' || current_date););
     *** Failure 3706 Syntax error: Did not expect the '=' that follows
              the ')'.  Statement# 1, Info =283
     *** Total elapsed time was 1 second.


     REPLACE TRIGGER GRAYBAR.TI_CUST_BILL_TO_1 ENABLED BEFORE
      INSERT ON GRAYBAR.CUST_BILL_TO ORDER 1
      REFERENCING NEW AS INCOMING
      FOR EACH ROW WHEN  (0 <>
         INCOMING.F1000_IDN_NATL_ACC
         AND 0 =
         (SELECT COUNT(*) FROM GRAYBAR.CUSTOMER_MASTER, INCOMING
         WHERE GRAYBAR.CUSTOMER_MASTER.TDWS_IDN_C_COMP =
          INCOMING.TDWS_IDN_C_COMP
         AND GRAYBAR.CUSTOMER_MASTER.CMST_CDE_C_COMP_TYPE = INCOMING.CMST_CDE_C_COMP_TYPE)
         )
      (INSERT INTO GRAYBAR.CUSTOMER_MASTER (TDWS_IDN_C_COMP,
         CMST_CDE_C_COMP_TYPE,
         CMST_NM_C_MASTER)
       VALUES (INCOMING.TDWS_IDN_C_COMP,
         INCOMING.CMST_CDE_C_COMP_TYPE,
         'SYSTEM INSERTED ' || current_date););
      *** Failure 5429 A triggered action statement contained an invalid
               reference.  Statement# 1, Info =0
      *** Total elapsed time was 1 second.

	<Prev	Next>		<<First	<Prev	Next>	Last>>