Archives of the TeradataForum

Message Posted: Thu, 06 Mar 2003 @ 21:42:53 GMT

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

<-- Anonymously Posted: Thursday, March 06, 2003 16:19 -->

Glenn,

These are my settings:

table 1: create table seq (seqnbr integer); this is a one row one column table, to start with a value of 1 in seqnbr.

table 2: create table tmp1( col1 integer, col2 integer, col3 varchar(100)) unique primary index (col1); Transactional table where DMLs occur

table 3: create table tmp2(seqnbr integer, col1 integer, col2 integer, col3 varchar(100)) unique primary index (seqnbr); The log table in to which I am trying to serially insert the transactions with a sequence nbr

In the first method 'New' is assumed as a table/procedure/view and errored out.. So I tried an "insert" first then followed by an "update" that came out with still more interesting error (please see below)..

I appreciate your response... and everyone's

Thanks

  METHOD-I
  Below is the trigger and the Error:
  ------------------------------------
  Create trigger tmp1_ins_trig enabled after insert on Tmp1 order 1
  referencing new as newrow
  for each row
  (
    locking table seq for write
    Insert into tmp2 (
             SeqNbr
            ,Col1
            ,Col2
            ,Col3
           )
    Select    Seq.SeqNbr
            ,NewRow.Col1
            ,NewRow.Col2
            ,NewRow.Col3
    from    seq
    ;  update seq set seqnbr = seqnbr + 1 ;
  );

  Trigger Compilation Error:
  ---------------------------
  *** Failure 3807 Table/view/trigger/procedure 'NewRow' does not exist.
               Statement# 1, Info =0

 METHOD-II
 ---------
  Replace trigger tmp1_ins_trig enabled after insert on Tmp1 order 1
  referencing new as newrow
  for each row
   (

      locking table seq for write
      Insert into tmp2 (
               SeqNbr
              ,Col1
              ,Col2
              ,Col3
             )
      values ( Null, Newrow.col1, newrow.col2, newrow.col3)
     ; Update tmp2 from seq set seqnbr= seq.seqnbr where
        col1=newrow.col1 and col2=newrow.col2 and col3=newrow.col3
      ;  update seq set seqnbr = seqnbr + 1 ;
  );

 Trigger compilation error:
 ---------------------------
 *** Failure 5529 A triggered statement inside a row trigger can not
refer to a table that is being changed by the row  trigger.
                Statement# 3, Info =0

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