Problems using new v7.2 features (or, What's wrong with this syntax?) 
Author Message
 Problems using new v7.2 features (or, What's wrong with this syntax?)

Context: IBM DB2 v7.2 (i.e., FP3) running on WinNT 4.0.

I'm trying to make use of the new control statements (e.g., IF, FOR, LOOP,
etc.) now available in triggers.  Unfortunately, I'm not having much luck,
and the 7.2 release notes seem to be pretty sparse (for example, there
is little documentation of the new IF statement, nor any example of it
in use).

For example, the following trigger generates the following error:

    create trigger Users_A_Audit_I01
    no cascade before insert on Users
    referencing new as new
    for each row mode db2sql
    begin atomic
      if (user <> 'MICA')
        begin atomic
          set new.created_by = user;
          set new.created_on = current timestamp;
          set new.updated_by = user;
          set new.updated_on = current timestamp;
        end
    end

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "(user <> 'MICA' )    begin atomic     set " was
found following "ql begin atomic   if".  Expected tokens may include:  
"<name>".  LINE NUMBER=8.  SQLSTATE=42601

Clearly something is wrong with the IF statement, but what?

Thanks!
Doug Morse



Sat, 22 Nov 2003 02:03:29 GMT
 Problems using new v7.2 features (or, What's wrong with this syntax?)

Hi,

IF <condition> THEN <list of statements> END IF;

also BEGIN ATOMIC cannot be nested (neither in triggers nor in procedures)

Cheers
Serge



Sat, 22 Nov 2003 03:04:54 GMT
 Problems using new v7.2 features (or, What's wrong with this syntax?)
.btw.... this (agreable pre V7.2) is a lot denser and therefore faster:
create trigger Users_A_Audit_I01
    no cascade before insert on Users
    referencing new as new
    for each row mode db2sql
    when (user <> 'MICA')
        begin atomic
          set (new.created_by,  new.created_on, new.updated_by,
new.updated_on)
              = (user, current timestamp, user, current timestamp);
         end
Cheers
Serge


Sat, 22 Nov 2003 03:53:18 GMT
 Problems using new v7.2 features (or, What's wrong with this syntax?)
Hi Serge,

I have a follow up question to this message. Can you use ELSE with the
IF/THEN syntax? What about ELSEIF?

Thanks, Nate



Tue, 02 Dec 2003 01:37:16 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. er, 'interesting' new 9i feature

2. OI new 'performance' features

3. One of Paradox 7's best features (that's not on the feature list)

4. What's wrong with this syntax?

5. What's wrong with this syntax?

6. What's wrong with my syntax ???

7. ODBC 'feature not implemented' driver problem

8. ESQL/C V7.10 Trivial Syntax Problem

9. Incorrect syntax near ',' using MSQuery

10. Problem in using Microsoft Word Mail Merge using datasource feature

11. Oracle8's new features - what are they


 
Powered by phpBB® Forum Software