Wednesday, November 2, 2016

Validation trigger

creating a trigger to validate the data

Triggers are a versatile database tool. A few weeks ago I wrote about using triggers to write data to an output file. I can also use triggers to validate data before it is written to the file or table. By placing the business rules and validation processes into the database with a trigger guarantees that:

  1. The same business rules are used every time. If the rules change I just need to change the logic in the trigger, rather than find all of the programs that output to the file and change the logic within them.
  2. It is not possible to circumvent the trigger. No matter which programming language or tool I use with the file the trigger will be executed. I will no longer have to worry about a rogue colleague changing data behind the scenes using something like DFU.

The triggers that writes changes to an output file are, what I call, "after" triggers, i.e. the trigger is executed have the data has been added or changed in the triggered file. Validation triggers have to be the opposite, "before" triggers, that are executed before the data in the triggered file is changed. If you think about it that is the only way they should be, as the business rules need be checked and the data rejected before the triggered file is changed.

Before I get started with my example code let me explain the scenario. I am going to create a very simple DDL table to contain customer transactions and their amounts. The very simple validation trigger will determine if the value of the customer transaction exceeds their maximum allowed credit balance. If it does the trigger will need not to add the incoming row to the table, and return a message to whatever is doing the update.

Let me start with my very simple table:

01  CREATE OR REPLACE TABLE MYLIB.TABLE1 (
02    CUSTOMER CHAR(5) NOT NULL,
03    TRANSACTION_ID FOR COLUMN "TRANSID" DECIMAL(10) NOT NULL,
04    AMOUNT DECIMAL(10,2) NOT NULL,
05    PRIMARY KEY(CUSTOMER,TRANSACTION_ID))
06    RCDFMT TABLE1R ;

Line 1: If you are on a release previous to IBM i 7.2 and the later TRs of 7.1 the CREATE OR REPLACE TABLE will not work. You will need to replace that part of the statement with just CREATE TABLE.

Lines 2 – 4: Define my three columns.

Line 5: By defining a primary key is the equivalent of defining a unique key on a DDS file, no duplicate key records allowed.

Line 6: I always like to give a record format name, just in case I decide I want to read this table in a RPG program.

I have included my SQL trigger in the same source member as the definition of my table, therefore, the line numbers run on:

07  CREATE OR REPLACE TRIGGER MYLIB.TRIGR1
08    BEFORE INSERT OR UPDATE ON MYLIB.TABLE1
09    REFERENCING NEW ROW AS N
10    FOR EACH ROW
11    MODE DB2ROW

12    BEGIN
13      DECLARE CURRENT_BALANCE DECIMAL(10,2) ;
14      DECLARE MAX_BALANCE DECIMAL(10,2) ;

15      IF N.CUSTOMER = ' ' THEN
          SIGNAL SQLSTATE 'U0000'
          SET MESSAGE_TEXT = 'Key field is blank' ;
16      END IF ;

17      IF N.TRANSACTION_ID = 0 THEN
          SIGNAL SQLSTATE 'U0001'
          SET MESSAGE_TEXT = 'Transaction id cannot be 0' ;
18      END IF ;

19      IF N.AMOUNT <= 0 THEN
          SIGNAL SQLSTATE 'U0002'
          SET MESSAGE_TEXT = 'Amount cannot be less or equal to 0' ;
20      END IF ;

21      SELECT TOTAL
          INTO MAX_BALANCE
          FROM MYLIB.CUSTBAL
         WHERE KEYFLD = N.CUSTOMER ;

22      SELECT SUM(AMOUNT)
          INTO CURRENT_BALANCE
          FROM MYLIB.TABLE1
         WHERE KEYFLD = N.CUSTOMER ;

23      SET CURRENT_BALANCE = CURRENT_BALANCE + N.AMOUNT ;

24      IF CURRENT_BALANCE > MAX_BALANCE THEN
          SIGNAL SQLSTATE 'U0003'
          SET MESSAGE_TEXT = 'Customer maximum balance exceeded' ;
25    END IF ;
26  END ;

Line 7: Another CREATE OR REPLACE, as I said before you may need to change this depending on your IBM i release. The rest of this line is self explanatory.

Line 8: This is where I say that this is a "before" trigger, and I am only interested if a row is being added or changed to the table I defined above.

Line 9: All columns from the "before" image of the row will be prefixed with "N".

Lines 10 and 11: I need the mode to be DB2ROW as I reference the table that has the trigger on it within the trigger. The alternative DB2SQL does not permit that.

Line 12: This is the beginning of the trigger code.

Lines 13 and 14: I am declaring two variables that I will use within this trigger.

Before I describe any more of the trigger I want to explain, in general terms, what the error handling code does. The standard error handling returns two variables to whatever caused this trigger to execute:

  1. SQL state: The SQL state code is five character string which I give using the SIGNAL SQLSTATE followed by the string I want to return. So as not to cause confusion between my codes and the system ones, my SQL codes all start with "U".
  2. Message text: The message text is set using SET MESSAGE_TEXT.

When an error is encountered, SQL code not equal to "00000", the trigger exits without executing the rest of it.

Lines 15 – 20: Before I can check the credit balance I need to make sure that all of the columns I need are not blank. If they are blank I give each one its own SQL status code and message.

And now onto the real interesting validation:

Line 21: This Select statement gets the maximum allowable balance for the customer and places it in the variable MAX_BALANCE. With this very simple trigger I am assuming that there always will be a matching row in the table. Notice in the Where clause that the column from the file with the trigger is prefixed with the "N", as we stated it would do on line 9.

Line 22: I am summing the values in AMOUNT for the customer I am trying to add a row for. Remember this is the table with the trigger on it, which is why I have to use DB2ROW mode. I put the total for the customer into the variable CURRENT_BALANCE.

Line 23: Before I can test if the customer's maximum balance has been exceeded I need to add the Current Balance to the amount from the new row.

Line 24: Now I can test if the new balance is going to exceed the maximum allowable balance.

When I use RUNSQLSTM with this source file I create TABLE1 and also my trigger. If I go and look in MYLIB I can see that the trigger has been created as a C program,

                          Work with Objects Using PDM
 Library . . . . .   MYLIB     

 Opt  Object      Type        Attribute   Text
 __   TRIGR1      *PGM        CLE         SQL TRIGGER TRIGR1

If I forgot how the trigger was configured I can use the SYSTRIGGER view:

SELECT SUBSTR(TABSCHEMA,1,10) AS FILE_LIB,
       SUBSTR(TABNAME,1,10) AS FILE,
       TRIGTIME AS EXECUTED_WHEN,
       SUBSTR(TRIGPGM,1,10) AS PGM,
       SUBSTR(TRIGPGMLIB,1,10) AS PGM_LIB,
       OPERATIVE,
       ENABLED,
       EVENT_U AS ON_UPDATE,
       EVENT_I AS ON_INSERT,
       EVENT_D AS ON_DELETE,
       TRIGNAME AS TRIGGER_NAME
  FROM QSYS2/SYSTRIGGER
 WHERE TABSCHEMA = 'MYLIB'
   AND TABNAME = 'TRIGR1'

Which shows me everything I need to know:

FILE_LIB   FILE     EXECUTED_WHEN   PGM      PGM_LIB
MYLIB      TABLE1       BEFORE      TRIGR1   MYLIB


OPERATIVE  ENABLED  ON_UPDATE  ON_INSERT  ON_DELETE
    Y         Y         Y          Y          N      

TRIGGER_NAME
TRIGR1

Having coded the table and the trigger let's use it…

01  ctl-opt dftactgrp(*no) ;

02  dcl-s i packed(1) ;

03  exec sql SET OPTION NAMING = *SQL ;

04  exec sql INSERT INTO MYLIB.TABLE1
             VALUES(' ',0,0) ;
05  i = 1 ;
06  Message() ;

Line 1: As I will be using subprocedures rather than subroutines in this program I need to use the control option to not use the default activation group.

Line 2: This variable is only being defined to be used in the Display operation code.

Line 3: I am using the SET OPTION to define that my SQL statements will be using the SQL naming format, rather than system format.

Line 4: Here is my first attempt to add a row. I am sending blank for the CUSTOMER, and zero for TRANSATION_ID and AMOUNT. This should produce an error.

Line 5: I am moving 1 to this variable so it might be used in a Display statement so I know which piece of code was executed.

Line 6: Rather than code the same code to display any errors multiple times, I put the code in the subprocedure Message. And here it is:

30  dcl-proc Message ;
31    dsply (%char(i) + '. SQLCOD = <' +
             %editc(SQLCOD:'X') + '>') ;

32    if (SQLCOD <> 0) ;
33      dsply ('SQLSTATE = <' + SQLSTATE + '>') ;
34      dsply ('SQLERM') ;
35      dsply ('  (27:6) = <' + %subst(SQLERM:27:6) + '>') ;
36      dsply ('  (34) =  <' + %trimr(%subst(SQLERM:34)) + '>') ;
37    endif ;
38  end-proc ;

Line 30: All subprocedures start with a DCL-PROC and end with a END-PROC, line 38.

Line 31: This Display operation will display the SQL code returned from the trigger.

Line 32: If the SQL code is not zero then an error happened.

Line 33: Display the SQL state.

Line 34: The SQL code and message are contained within the SQL error message field, SQLERM.

Line 35: The SQL code starts at position 27 and is six long.

Line 36: The message text starts in position 34 and continues to the end of SQLERM.

What I see for this first attempt at Insert is:

DSPLY  1. SQLCOD = <00000072L>
DSPLY  SQLSTATE = <09000>
DSPLY  SQLERM
DSPLY    (27:6) = <U0000 >
DSPLY    (34) =  <Key field is blank                   >

As the CUSTOMER was blank the SQL code "U0000" was returned.

The next Insert has a CUSTOMER, but the other two columns remain zero.

07  exec sql INSERT INTO MYLIB.TABLE1
             VALUES('1',0,0) ;
08  i = 2 ;
09  Message() ;

And the SQL code and message alert us to that error.

DSPLY  2. SQLCOD = <00000072L>
DSPLY  SQLSTATE = <09000>
DSPLY  SQLERM
DSPLY    (27:6) = <U0001 >
DSPLY    (34) =  <Transaction id cannot be 0           >

The third Insert has values in the CUSTOMER and TRANSACTION_ID, but the AMOUNT remains zero.

10  exec sql INSERT INTO MYLIB.TABLE1
             VALUES('1',1,0) ;
11  i = 3 ;
12  Message() ;

Which gives the following error:

DSPLY  3. SQLCOD = <00000072L>
DSPLY  SQLSTATE = <09000>
DSPLY  SQLERM
DSPLY    (27:6) = <U0002 >
DSPLY    (34) =  <Amount cannot be less or equal to 0  >

Now I have those errors sorted the next two Insert statements are error free:

16  exec sql INSERT INTO MYLIB.TABLE1
             VALUES('1',1,1.00) ;
17  i = 5 ;
18  Message() ;

19  exec sql INSERT INTO MYLIB.TABLE1
             VALUES('1',2,89.99) ;
20  i = 6 ;
21  Message() ;

DSPLY  5. SQLCOD = <000000000>

DSPLY  6. SQLCOD = <000000000>

The total of the AMOUNT column is $99.99. The maximum balance allowed for this customer is $100.00. Therefore, when the next Insert statement occurs for $10.00 an error is returned.

22  exec sql INSERT INTO MYLIB.TABLE1
             VALUES('1',3,10.00) ;
23  i = 7 ;
24  Message() ;

DSPLY  7. SQLCOD = <00000072L>
DSPLY  SQLSTATE = <09000>
DSPLY  SQLERM
DSPLY    (27:6) = <U0003 >
DSPLY    (34) =  <Customer maximum balance exceeded    >

If I try to add that $10.00 using STRSQL it still fails, and displays the message from the trigger.

insert into mylib.table1 values('1',3,10.00)

SQL trigger TRIGR1 in MYLIB failed with SQLCODE -438 SQLSTATE U0003

When I drill down into the error I get to see the SQL code and message.

                         Additional Message Information

 Message ID . . . . . . :   SQL0723       Severity . . . . . . . :   30
 Message type . . . . . :   Diagnostic

 Message . . . . :   SQL trigger TRIGR1 in MYLIB failed with SQLCODE
   -438 SQLSTATE U0003.
 Cause . . . . . :   An error has occurred in a triggered SQL statement
   in trigger TRIGR1 in schema MYLIB.  The SQLCODE is -438, the SQLSTATE
   is U0003, and the message is Customer maximum balance exceeded.

By all means this is a very simple validation trigger, but it does give you an example of what can be done to move the validation of data down to the database level.

 

You can learn more about the CREATE TRIGGER statement from the IBM website here.

 

This article was written for IBM i 7.3, and should work for earlier releases too.

6 comments:

  1. Hi Simon

    Glad to see that you use subprocedures rather than subroutines. But in my opinion the hidden interface (global variable i) in subprocedure message() is not good practice. That should be an incoming parameter.
    In fact I would be very happy if the RPG compiler offered me the possibility to ask for a severity 30 message in order to avoid hidden interfaces to subprocedures.

    What about existing old style RPG programs that change a file for which a validation trigger is added? If the program does not have good error handling, the program will go in abnormal end in stead of showing the message in the message line.

    Regards
    Jan

    ReplyDelete
    Replies
    1. I would hope that anyone implementing any type of validation trigger would realize that there are consequences that will have an effect on all programs that update the file. If some of these programs are "old style RPG", are you meaning RPGIII?, it would be time to convert them to newer RPG.

      Delete
    2. Hi Simon

      Old style was not the right word, I am sorry for the mistake.
      What I mean is this, how do we cope with messages from the BEFORE-trigger in an RPG program that changes the file using native IO (not embedded SQL)?

      if myFileioDs.company = *blanks
      >>sndPgmMsg('Company is required.')
      >>redisplay
      endif

      if myFileioDs.order = *blanks
      >>sndPgmMsg('Order is required.')
      >>redisplay
      endif

      // No errors found, do the write...
      write myFile.myFiler myFileioDs;

      // Now we have to add code to cope with messages from BEFORE-trigger
      // Can you give an example please?

      Regards
      Jan

      Delete
  2. Jan brings up a good point. I have seen legacy code which assumed that any failure on a write must be because the key already exists in the file and then went and did a chain and an update.
    Jan, I think handling write errors are a matter of style. There are any number of ways of doing it. You could:
    - Put your write within a MONITOR and on-error structure.
    - Use the 'E' write operation extender and check %Error and %Status.
    - Use the 'E' write operation extender and check the file information data structure.
    - (ditto) and retrieve further details from the joblog. There are some nice SQL services for reading the joblog. There are also some other techniques preferred by some.

    Regards,
    Rob Berendt

    ReplyDelete
  3. Jan, I am working on a post giving an example of how to code a validation trigger in RPG. It will include how to cope with the returned errors in an RPG program.

    I would hope the before anyone implemented validation triggers they would consider the effects on their existing programs and make changes accordingly.

    ReplyDelete
  4. Thanks Simon and Rob.
    Jan

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.