Allow Repeated Change With SQL Triggers

In my previous article, I described how an RPG program as a before trigger, could be used to synchronize two columns in a table. There is an alternative. Instead of using an RPG trigger, we could have used an SQL trigger. An SQL trigger allows us to be more specific about when the trigger is activated and also has the advantage of portability to other database management systems.

The scenario was where a date, stored as a packed numeric column, was to be converted to a proper date field. This was accomplished by duplicating the packed numeric column (and its contents) as a date column, recompiling all programs that accessed the table, and adding a before trigger to the table for insert and update. The trigger ensured that the contents of the packed numeric column and the date column were kept in synch.

The outcome was that programs could then be changed on a one-by-one basis and be put into production. The trigger program would keep the dates in synch.

Even though the RPG trigger program works perfectly and meets all the requirements, there is an overhead in that the trigger program is called before a row is changed, regardless of whether or not one of the date fields has changed.

With SQL triggers we have the ability to specify triggers at the column level. So let’s have a look at how we would achieve the same outcome using SQL triggers.

We will need to define three SQL triggers:

1. A trigger to run before a row is inserted.
2. A trigger to run before the packed numeric column (containing a date) is changed.
3. A trigger to run before the date column is changed.

But First

Since there is some code that will be duplicated between the triggers, we will write a couple of stored procedures. Doing so will save us having to copy and paste some code.

The first snippet below shows the code needed to create the stored procedure SP_DATE_TO_NUMBER. This procedure takes a date field (in ISO format) and converts it to a numeric field (in ISO format).

CREATE PROCEDURE SP_DATE_TO_NUMBER ( 
	IN DATEIN DATE , 
	OUT DATEOUT DECIMAL(8, 0) ) 
	LANGUAGE SQL 
	SPECIFIC SP_DATE_TO_NUMBER 
	DETERMINISTIC 
	CONTAINS SQL 
	CALLED ON NULL INPUT 
	PROGRAM TYPE SUB 
	COMMIT ON RETURN YES 
	SET OPTION  ALWBLK = *ALLREAD , 
	ALWCPYDTA = *OPTIMIZE , 
	COMMIT = *NONE , 
	DECRESULT = (31, 31, 00) , 
	DFTRDBCOL = *NONE , 
	DYNDFTCOL = *NO , 
	DYNUSRPRF = *USER , 
	SRTSEQ = *HEX   
BEGIN NOT ATOMIC 
  
   DECLARE CHARDATE CHAR ( 10 ) ; 
   SET CHARDATE = CHAR(DATEIN) ; 
   SET DATEOUT = INT(SUBSTR(CHARDATE, 1, 4) || 
                     SUBSTR(CHARDATE, 6, 2) || 
                     SUBSTR(CHARDATE, 9, 2)) ; 
END  ;

Next we will look at the code used to create the stored procedure SP_NUMBER_TO_DATE. This procedure takes a numeric field (in ISO format) and converts it to a date field (in ISO format).

CREATE PROCEDURE SP_NUMBER_TO_DATE ( 
	IN DATEIN DECIMAL(8, 0) , 
	OUT DATEOUT DATE ) 
	LANGUAGE SQL 
	SPECIFIC SP_NUMBER_TO_DATE 
	DETERMINISTIC 
	CONTAINS SQL 
	CALLED ON NULL INPUT 
	PROGRAM TYPE SUB 
	COMMIT ON RETURN YES 
	SET OPTION  ALWBLK = *ALLREAD , 
	ALWCPYDTA = *OPTIMIZE , 
	COMMIT = *NONE , 
	DECRESULT = (31, 31, 00) , 
	DFTRDBCOL = *NONE , 
	DYNDFTCOL = *NO , 
	DYNUSRPRF = *USER , 
	SRTSEQ = *HEX   
BEGIN NOT ATOMIC 
  
   DECLARE CHARDATE CHAR ( 8 ) ; 
   SET CHARDATE = CHAR ( DATEIN ) ; 
   SET DATEOUT = DATE (SUBSTR(CHARDATE, 1, 4) || '-' || 
                       SUBSTR(CHARDATE, 5, 2) || '-' || 
                       SUBSTR(CHARDATE, 7, 2)) ; 
  
END  ; 

When The Numeric Column Changes

The next piece of code we will examine is used to create the SQL trigger TRG_SQL_NUMBER_TO_DATE. This trigger is called before a change is made to the DATESOLD column. The important points to note are:

• When the trigger is to be called “before update of DATESOLD”, this means that the trigger will only be called if the value of DATESOLD changes and not if the value of any other column changes.
• To identify column names in the new image, referencing NEW as NEW_ROW, means that all columns in the new image are qualified with NEW_ROW.
• The trigger itself simply calls the SP_NUMBER_TO_DATE stored procedure to perform the conversion of the date in the numeric column to the date column, i.e., the two columns being passed as parameters.
• There is no requirement to specify Allow Repeated Change. With SQL triggers, columns in the NEW row buffer may be changed using a SET operation or, as in this example, using an OUT or INOUT parameter on a stored procedure.

Here’s the code used to create the stored Procedure TRG_SQL_NUMBER_TO_DATE.

CREATE TRIGGER TRG_SQL_NUMBER_TO_DATE 
	BEFORE UPDATE OF DATESOLD ON PRODUCT 
	REFERENCING NEW AS NEW_ROW 
	FOR EACH ROW 
	MODE DB2ROW 
	SET OPTION  ALWBLK = *ALLREAD , 
	ALWCPYDTA = *OPTIMIZE , 
	COMMIT = *NONE , 
	DECRESULT = (31, 31, 00) , 
	DFTRDBCOL = *NONE , 
	DYNDFTCOL = *NO , 
	DYNUSRPRF = *USER , 
	SRTSEQ = *HEX   
BEGIN ATOMIC 
   CALL SP_NUMBER_TO_DATE(NEW_ROW.DATESOLD, 
                          NEW_ROW.LAST_SELL) ; 
END  ; 

When The Date Column Changes

Next, let’s look at the code used to create the SQL trigger TRG_SQL_DATE_TO_NUMBER. This trigger is called before a change is made to the LAST_SELL column. The process is very similar to the TRG_SQL_NUMBER_TO_DATE trigger, except that the SP_DATE_TO_NUMBER stored procedure is called to convert the date from the date column to the numeric column.

CREATE TRIGGER TRG_SQL_DATE_TO_NUMBER 
	BEFORE UPDATE OF LAST_SELL ON PRODUCT 
	REFERENCING NEW AS NEW_ROW 
	FOR EACH ROW 
	MODE DB2ROW 
	SET OPTION  ALWBLK = *ALLREAD , 
	ALWCPYDTA = *OPTIMIZE , 
	COMMIT = *NONE , 
	DECRESULT = (31, 31, 00) , 
	DFTRDBCOL = *NONE , 
	DYNDFTCOL = *NO , 
	DYNUSRPRF = *USER , 
	SRTSEQ = *HEX   
BEGIN ATOMIC 
   CALL SP_DATE_TO_NUMBER(NEW_ROW.LAST_SELL,
                          NEW_ROW.DATESOLD) ; 
END  ; 

When A Row Is Inserted

Finally, the last piece of code is used to create the SQL trigger TRG_SQL_SYNC_NEW_DATES. This trigger is called before a new row is inserted. Based on whether or not the value of the numeric column is zero, the trigger will call either the SP_NUMBER_TO_DATE stored procedure (to perform the conversion of the date in the numeric column to the date column), or the SP_DATE_TO_NUMBER stored procedure (to convert the date from the date column to the numeric column).

CREATE TRIGGER TRG_SQL_SYNC_NEW_DATES 
	BEFORE INSERT ON PRODUCT 
	REFERENCING NEW AS NEW_ROW 
	FOR EACH ROW 
	MODE DB2ROW 
	SET OPTION  ALWBLK = *ALLREAD , 
	ALWCPYDTA = *OPTIMIZE , 
	COMMIT = *NONE , 
	DECRESULT = (31, 31, 00) , 
	DFTRDBCOL = *NONE , 
	DYNDFTCOL = *NO , 
	DYNUSRPRF = *USER , 
	SRTSEQ = *HEX   
BEGIN ATOMIC 
   IF NEW_ROW . DATE_LAST_SOLD = 0 THEN 
      CALL SP_DATE_TO_NUMBER(NEW_ROW.LAST_SELL, 
                             NEW_ROW.DATESOLD) ; 
   ELSE 
      CALL SP_NUMBER_TO_DATE(NEW_ROW.DATESOLD, 
                             NEW_ROW.LAST_SELL) ; 
    END IF ; 
END  ; 

RPG Triggers Vs. SQL Triggers

Both RPG and SQL triggers have their advantages.

With the RPG trigger we only had one trigger program that looked after all of the processing. The only problem was the trigger was activated even when one of the columns we were concerned about was not changed.

With the SQL triggers, we could be more specific about when the triggers are activated but we had to write separate trigger programs.

Either approach is valid. Why not try them both?

Paul Tuohy is CEO of ComCon, an iSeries consulting company, and is one of the co-founders of System i Developer, which hosts the RPG & DB2 Summit conferences. He is an award-winning speaker who also speaks regularly at COMMON conferences, and is the author of “Re-engineering RPG Legacy Applications,” “The Programmers Guide to iSeries Navigator,” and the self-study course called “iSeries Navigator for Programmers.” Send your questions or comments for Paul to Ted Holt via the IT Jungle Contact page.

RELATED STORY

Allow Repeated Change With Before Triggers

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot