Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2001
    Posts
    40

    Unanswered: Simple trigger question

    creating TRIGGER as follows

    C:\testproject>db2 "create trigger testt after insert on db2admin.testproc referencing NEW as x for each row mode db2sql insert into testproc values (999);"
    DB20000I The SQL command completed successfully.

    testing TRIGGER as follows

    C:\testproject>db2 "insert into testproc values (11)"
    DB21034E The command was processed as an SQL statement because it was not avalid Command Line Processor command. During SQL processing it returned:
    SQL0723N An error occurred in a triggered SQL statement in trigger
    "DB2ADMIN.TESTT". Information returned for the error includes SQLCODE "-724",SQLSTATE "54038" and message tokens
    "DB2ADMIN.TESTT". SQLSTATE=09000


    Nothing in the diag file. What am I doing wrong?

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Simple trigger question

    Your insert calls a trigger which in turn inserts into the same table and hence there is a cascade of triggers ...

    SQL0724N The activation of trigger "<trigger-name>" would exceed
    the maximum level of cascading.

    Explanation: Cascading of triggers occurs when a triggered SQL
    statement in a trigger would result in another trigger being
    activated or when a referential constraint delete rule causes
    additional triggers to be activated. The depth of this cascading
    is limited to 16.

    Note that recursive situations where a trigger includes a
    triggered SQL statement that directly or indirectly causes the
    same trigger to be activated is a form of cascading that is very
    likely to cause this error if there are no conditions to prevent
    cascading from exceeding the limit.

    The "<trigger-name>" specified is one of the triggers that would
    have been activated at the seventeenth level of cascading.

    User Response: Start with the triggers that are activated by the
    UPDATE, INSERT or DELETE statement that received this error. If
    any of these triggers are recursive, ensure that there is some
    condition that prevents the trigger from being activated more
    than the limit allows. If this is not the cause of the problem,
    follow the chain of triggers that are activated to determine the
    chain that exceeds the cascading limit.

    sqlcode: -724

    sqlstate: 54038
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Dec 2001
    Posts
    40
    That was it. Thanks. But what I really want to do is, let the orginal value go in and also insert another statement into the same table.

    So I tried using no cascade but it compains no cascade only works with BEFORE triggers.

    Thank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •