Validation trigger

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.