Implementing a Database Trigger in Unidata 3.3

A database trigger is something that happens whenever an item is filed in a database. Triggers are an integral part of all modern databases and are designed to provide a standard mechanism for updating secondary files consistently. Unidata 3.3 does not directly support triggers this feature is supposed to be in release 4.x however.

You can implement a database trigger in Unidata 3.3 even though this feature is not officially supported until release 4.x. The trick is to use the index function to cause the execution of a basic program. Normally Indexes update only an index, but Unidata allows the Index feature to call "I" type dict items, which can call basic programs, you just have the I type dict item, call the subroutine you want to have execute whenever an item is filed. This subroutine should return a null so that when the Null is encountered by Unidata's index feature, no index entry is created because of the no-nulls option on the index.

Steps:

1. Define an Index on an I -type dict item,
2. Have the I type call a basic program but always return a null.
3. Define the index with No nulls.
4. This will force the system to run the program every time an item is filed, but will not create an index or waster disk space

Example:

CREATE.FILE TRIGGER.TEST 1 3

CREATE.FILE TRIGGER.XREF 1 3

ED DICT TRIGGER.TEST TRIGGER

Top of "TRIGGER" in "DICT TRIGGER.TEST", 6 lines, 57 characters.

*--: P

001: I

002: SUBR('TRIGGER.TEST.TRIGGER',@RECORD,@ID)

003:

004: TRIGGER

005: 10L

006: S

Bottom.

ED SUBR.DICT.BP TRIGGER.TEST.TRIGGER

001: SUBROUTINE TRIGGER.TEST.TRIGGER(RETURN.VALUE, TRIGGER.ITEM, TRIGGER.ID)

002: *

003: RETURN.VALUE='' ;* always return null

004: *

005: OPEN '','TRIGGER.XREF' TO TRIGGER.XREF THEN

006: MAX.WORDS=DCOUNT(TRIGGER.ITEM,@AM)

007: FOR INDX=1 TO MAX.WORDS

008: WRITE TRIGGER.ID ON TRIGGER.XREF, TRIGGER.ITEM<INDX>

009: NEXT INDX

010: *

011: END

012: RETURN

013: *

Bottom.

CREATE.INDEX TRIGGER.TEST TRIGGER NO.NULLS

ED TRIGGER.TEST 1

Top of "1" in "TRIGGER.TEST", 6 lines, 28 characters.

*--: P

001: TOM

002: JOHN

003: SHEILA

004: LISE

005: SAM

006: BOB

*--: FI

Filed "1" in file "TRIGGER.TEST"

LIST TRIGGER.XREF

TRIGGER.XREF

TOM

JOHN

SHEILA

SAM

LISE

BOB

6 records listed

LIST.INDEX TRIGGER.TEST

Alternate Key Index Details for File TRIGGER.TEST Page 1

File.................. TRIGGER.TEST

Alternate key length.. 20

Node/Block size....... 4K

OV blocks............. 1 (0 in use, 0 overflowed)

Indices............... 1 (0 D-type)

Index updates......... Enabled, No updates pending

Index name F-type K-type Build Nulls In-DICT S/M F-No/I-type expr

TRIGGER V Txt Required No Yes S SUBR('TRIGGER.T

EST.TRIGGER',@R

ECORD,@ID)

Writing Sequential Files with Unidata

I have seen a lot of strange programs in Primac attempting to write sequential files. Below is a sample program that details how to create a DIR type file and how to write sequential items into this DIR type file.

Sequential files are UNIX files in a UNIX directory. When you want to create a SEQUENTIAL file, you always need to have a DIR type file to place the sequential file (ie ITEM) into. Some programmers get confused during the OPEN statement because the you are actually opening an 'item' to write to and then reading/writing attributes into this file.

Summary of Unidata and Sequential file functions.

Note that that APPEND statement is critical to the success of the WRITESEQ statement and that all of the commands have error handling messages. You should expand the function of the error handling commands for any live program.

NOTE: the WRITESEQ command always inserts a CHAR(10) at the end of the every record as a delimiter. If you need to send truly flat file without this delimiter, you can not use the WRITESEQ option, you then need to use the OSWRITE options which are beyond the scope of this document.

Many errors found during the use of the Primac system are related to unchecked return codes from executes. There is one especially irritating shortcoming with the CREATE-FILE command. If the program executes a CREATE-FILE in Unidata, then it may abort without creating the file and then the program will abort because the file does not exist. The main cause of this is a missing VOC entry. If a file exists in UNIX and does not have a VOC entry, then when a Unidata program or Proc tries to create the file, it will abort, because the file exists in the Unix account but it will NOT create the VOC item. This is especially frequent in the Month end close processes in Primac. ALWAYS CHECK the RETURN CODES!!!

You can get really confused with sequential files in Unidata because Unidata DOES allow you to use normal READ and WRITE statements on SEQUENTIAL files. I think it is a better style to use the SEQuential file commands to write sequential files so it is clear exactly what you are intending on doing.

There are also other commands to write directly to the OS without the need to have VOC pointers to the items. These commands are OSOPEN, OSREAD, OSWRITE etc. and are beyond the scope of this document.

TEST.SEQ

* PROGRAM TO DEMONSTRATE HOW TO OPEN, INITIALIZE AND WRITE A

* SEQUENTIAL FILE IN UNIDATA

*

* TJP - 10/17/97

*

WEEKLY.FILE.NAME='PR971017.TXT'

*

* first create the DIR type file that will hold all of the records

* then scan the return codes to see whether the file was created

* successfully, if the DIR file exists then the result code will be

* 10805 indicating the file exists

*

*

EXECUTE 'CREATE-FILE DIR PAYREG.004' RETURNING RETURN.CODE

*

DIRECTORY.EXISTS=0; CREATED.DIR.OK=0

MAX.CODES=DCOUNT(RETURN.CODE,@AM)

*

FOR INDX=1 TO MAX.CODES UNTIL DIRECTORY.EXISTS

DIRECTORY.EXISTS=(RETURN.CODE<INDX,1>='10805')

NEXT INDX

*

FOR INDX=1 TO MAX.CODES UNTIL CREATED.DIR.OK

CREATED.DIR.OK=(RETURN.CODE<INDX,1>='10036' )

NEXT INDX

*

IF DIRECTORY.EXISTS ! CREATED.DIR.OK ELSE

PRINT 'I COULD NOT FIND OR CREATE THE DIRECTORY FILE'

STOP

END

*

* first check top see if the file exists and if it does, then delete it

* notice the slightly different syntax of the OPENSEQ statement from the

* normal OPEN syntax - this is critical to understand that you are opening

* a Unix file in a Unix Directory with the OPENSEQ. The file name here

* PAYREG.004 is actually a DIR type file with the following entry in the

* VOC of the account

*

*001: DIR

*002: PAYREG.004

*003: D_PAYREG.004

*

OPENSEQ 'PAYREG.004',WEEKLY.FILE.NAME TO SOME.FILE THEN

*

* if you do not check for the existance of the file first and then delete

* it if it exists, then a rerun of the program will place another 100 records

* in the item so that after 2 runs there will be 200 lines.

*

* also, if a user on another port has the file open in this process, this DELETE

* will be held up until the user finishes this the file and exits the program

* There is no indication to the user that the file is in use by someone else.

*

EXECUTE 'DELETE PAYREG.004 SOME.FILE.NAME'

END

*

* then attempt to open the file again and this time, check the status

* codes the indicate whether you have a DIR type file and whether you

* can write to it or not.

*

OPENSEQ 'PAYREG.004',WEEKLY.FILE.NAME TO SOME.FILE THEN

PRINT 'I TRIED TO DELETE THE FILE BUT FAILED'

END ELSE

PRINT 'FILE WAS NOT OPENED'

RESULT.CODE=STATUS()

BEGIN CASE

CASE RESULT.CODE = '0'

ERRMSG = 'SEQUENTIAL FILE IS OK'

FILE.OK=1

CASE RESULT.CODE = '1'

ERRMSG = 'SEQUENTIAL FILE IS NOT A SEUQENTIAL FILE'

FILE.OK=0

CASE RESULT.CODE = '2'

ERRMSG = 'SEQUENTIAL FILE DOES NOT EXIST'

FILE.OK=0

CASE RESULT.CODE = '3'

ERRMSG = 'SEQUENTIAL FILE RECORD IS MISSING'

FILE.OK=0

CASE RESULT.CODE = '4'

ERRMSG = 'UNKNOWN FILE ERROR - POSSIBLY PERMISSIONS PROBLEM'

FILE.OK=0

END CASE

PRINT 'RESULT = ':RESULT.CODE:' ':ERRMSG

IF FILE.OK ELSE

PRINT 'FILE OPEN ERROR - STOPPING'

STOP

END

*

END

*

INPUT RSP,1

*

FOR INDX=1 TO 100

CRT INDX:

LINE=STR(MOD(INDX,10),'60')

WRITESEQ LINE APPEND ON SOME.FILE ON ERROR PRINT 'FATAL IO ERROR' THEN

PRINT 'WROTE SUCCESSFULLY'

END ELSE

PRINT 'DID NOT WRITE'

END

NEXT INDX

*

When using translations in dictionary items in Unidata, there seems to be a bug when the lookup id parameter begins with a number. The lookup id parameter is the third parameter.

In the samples below, the first sample works, but the second does not and reports a syntax error. The 1099FLAG and FLAG.1099 dict items in the VEND file are identical.

ED DICT SQV VEND.1099.FLAG

Top of "VEND.1099.FLAG" in "DICT SQV", 6 lines, 60 characters.

*--: P

001: I

002: TRANS("VEND",CONO:VEND,FLAG.1099,"V")

003:

004: VEND²1099²FLAG

005: 4L

006: S

Bottom.

ED DICT SQV VEND.1099.FLAG

Top of "VEND.1099.FLAG" in "DICT SQV", 6 lines, 60 characters.

*--: P

001: I

002: TRANS("VEND",CONO:VEND,1099FLAG,"V")

003:

004: VEND²1099²FLAG

005: 4L

006: S

Bottom.

The dictionary below, translates through a different fle depending on whther the category is MSC or not.

ED DICT INVOICE SALES.CODE.ACCT SALES.CODE.ACCT

< 1 > Top of "SALES.CODE.ACCT" in "DICT INVOICE", 6 lines, 210 characters.

*--: P

001: I

002: SUBR('-IFS',SUBR('-EQS',CHG.CAT,REUSE('MSC')),TRANS('SALES.CODE',SUBR('-CAT

S',REUSE(CONO),TAX.JURS),'GL.ACCT',"X"),TRANS('SALES.CODE',SUBR('-CATS',REUSE(CO

NO),CHG.CAT),'GL.ACCT',"X"))

003:

004: SALES CODE²GL ACCT

005: 8R

006: M

Bottom.

<-- click here for document on basic programs called from dict items

In the Primac system there are many reporting programs that do not need to exist. Many of the programs seem to have been created to avoid implementing dictionaries for manipulating multi-values. Below are two methods to produce a quick report without having to write a largish basic program to produce a report.

In this example, I need to produce a total of the amounts that have been deducted from a person's payroll for 401K contributions. There is a multi valued attribute (#18) that contains the deduction codes and the attribute (#19) that contains the amounts. We need Unidata to sum up the amounts that have a corresponding deduction code = "4K".

LIST QHR 401K.AMT F18 F19 GROSS 10:50:00 Sep 10 1997 1

401K

QHR............. .......... MISC.CODE. MISC.AMT.. GROSS.....

0024439820775753 0.00 200000

0024439820876148 0.00 200000

0024439820976538 0.00 200000

0024439821076940 0.00 200000

0024439821177341 0.00 200000

00444398012469 0.00 200000

00444398013576 0.00 200000

00444398014684 0.00 200000

00444398015788 0.00 200000

00444398016890 100.00 4K 10000 200000

004443980171001 100.00 4K 10000 200000

AH 3392

004443980181111 100.00 4K 10000 200000

AH 3392

12 records listed

I have defined the following dictionary items to do this in two ways:

:ED DICT QHR 401K.AMT

Top of "401K.AMT" in "DICT QHR", 7 lines, 48 characters.

*--: P

001: V

002: SUBR('!SUM401K',F18,F19)

003: MR2,

004: 401K]AMT

005: 10R

006: MV

007: MISC_DED_INFO

Bottom.

I put any and all subroutines in the DICT.BP (this is not the dictionary of the BP file, it is a file called "DICT.BP") file where they are compiled and catalogued. I use the "!" character to indicate that the subroutine is used by a dictionary item.

Top of "!SUM401K" in "DICT.BP", 12 lines, 262 characters.

*--: P

001: SUBROUTINE SUM401K(AMT.401K, DEDUCTION.CODES, DEDUCTION.AMTS)

002: *

003: AMT.401K= 0

004: MAX.DEDS=DCOUNT(DEDUCTION.CODES,@VM)

005: FOR INDX.DED=1 TO MAX.DEDS

006: IF DEDUCTION.CODES<1,INDX.DED>='4K' THEN

007: AMT.401K=AMT.401K+DEDUCTION.AMTS<1,INDX.DED>

008: END

009: NEXT INDX.DED

010: *

011: RETURN

012: *

Bottom.

Below are the dictionary items that should be setup in order make the report work without coding any basic programs.

Note: When comparing multivalues the REUSE() function is critical to the correct functioning of these dictionary items. If you do not include the REUSE() statement in the function definition, the results will be incorrect.

SUBR('-EQS',MISC_CODE,'4K') - is incorrect and will compare only the first value of MISC_CODE to the 4K. If the codes in MISC_CODE are '4F':VM:'4K':VM:'3H' then when Unidata executes this statement what it does is compare '4F':VM:'4K':VM:'3H' to '4K':VM:'':VM:''.

SUBR('-EQS',MISC_CODE,REUSE('4K')) - is the correct mechanism and will result in the following comparison '4F':VM:'4K':VM:'3H' to '4K':VM:'4K':VM:'4K'.

MISC_DED_INFO

PH

MISC_CODE MISC_AMT

MISC_CODE

D

18

MISC]CODE

5L

MV

MISC_DED_INFO

MISC_AMT

D

19

MR2

MISC]AMT

10R

MV

MISC_DED_INFO

401K.AMT1

V

SUBR('-EQS',MISC_CODE,REUSE('4K'))

401K]AMT1

10R

MV

401K.AMT2

V

SUBR('-IFS',SUBR('-EQS',MISC_CODE,REUSE('4K')),MISC_AMT,'0')

MR2

401K]AMT2

10R

MV

401K.SUM

V

SUM(SUBR('-IFS',SUBR('-EQS',MISC_CODE,REUSE('4K')),MISC_AMT,'0'))

MR2

401K]SUM

10R

MV

This version of the 401k.SUM dict item implements an OR condition to allow the amount to display whether the code is either 4K or 4F

401K.SUM

V

SUM(SUBR('-IFS',SUBR('-ORS',SUBR('-EQS',MISC_CODE,REUSE('4K')),SUBR('-EQS',MISC_CODE,REUSE('4F'))),MISC_AMT,'0'))

MR2

401K]SUM

10R

MV

Using only the dictioary items above the following report can be produced.

LIST QHR MISC_CODE MISC_AMT 401K.AMT1 401K.AMT2 401K.SUM DBL.SPC 1

1997 2

MISC MISC 401K 401K 401K

QHR............. CODE. AMT....... AMT....... AMT2...... SUM.......

00444398016890 4K 100.00 1 100.00 100.00

004443980171001 4K 100.00 1 100.00 100.00

AH 33.92 0

004443980181111 4K 100.00 1 100.00 100.00

AH 33.92 0

00422508012378 AH 56.73 0 0.00 424.62

4K 424.62 1 424.62

LI 55.38 0

LD 2.04 0

UW 7.00 0

00422508013487 AH 56.73 0 0.00 384.62

4K 384.62 1 384.62

LI 55.38 0

LD 2.04 0

This may be a bit of a repeat for some. <-- go to multivalued attribute manipulation document

You can easily call basic programs from Dict items using Unidata. Generally you should avoid designing files that need to do things like this btu sometimes it just can not be helped because of the need to interface to a file in an unexpected way. When this happens a simple basic program called from the dict item, can save many hours of programming an entire report in Basic and will allow you to reuse the dict item in multiple reports.

In the sales reports, there is the need to determine the SALES date of a job. Unfortunately, there is not a single sales date available due to the fact that there are multiple invoices and multiple invoice dates. For sales forcasting purposes, it is required to have a single date for all sales of a job.

There is no single attribute where this data can be retrieved. It needs to be determined programmatically. In order to solve this, I created the subroutine in the DICT.BP file called COMM.SALES.DATE. This program determines the Sales date by finding the invoice date of the largest, non-pre-bill invoice. You can not use the most recent invoice date because jobs often have small invoices issued after the main "FINAL" invoice. The best guess for Sales period is the perion in which the largest invoice amount that is a non-prebill (ie invoice number does not end in "PB").

The rules for calling basic programs from dict items: