Notes on UniVerse Triggers

Feb 2017. Updated Sep 2023.

Overview

Triggers were initially introduced to UniVerse as part of its SQL capabilities.

Since UV 10.0, triggers have been available for normal UniVerse files. However, this has been done by adapting the SQL triggers to the multi-value environment, which imposes a number of SQL restrictions on their use.

"Normal" means hashed files. Triggers are not available for directory files (type 1 or 19)
Not all file actions invoke the trigger. Notably, the CLEAR.FILE command will not invoke the trigger (the file is simply shortened at the O/S level, thus does not process the individual records)
Curiously, CNAME (changing the @ID of the record) does not invoke the trigger either, although you would expect this to involve both a delete and a write
If you have multi-files, then the trigger cannot be globally applied to all the files in the multi-file. Nor can you use a Q-pointer using a filename like: dictname,filepart as the comma is invalid syntax for SQL. We will cover how to get around these restrictions later
The trigger subroutine must be either normally or globally catalogued. Locally catalogued subroutines (as per the normal PICK practice) cannot be used. However, local subroutines may be called by the trigger subroutine
If the trigger subroutine changes, you must DROP the trigger from the file, and then CREATE it again.

Create a master trigger subroutine

What we want is to create a globally catalogued program that we can apply to any file. This trigger program won't do anything itself - it will call another subroutine specific to the file to actually carry out the trigger actions.

The advantage of this structure is that we can change the real trigger program without going through the rigmarole of dropping the trigger from the file, and then re-creating it. We will only need to do this if we change our master subroutine.

SUBROUTINE QUV.TRIGGER(triggername, schema, filename, event, timing, newid, newrec, oldid, oldrec,
 assoc, assoc.event, trigcnt, chain.cascade, cascade)
************************************************************************
* Author : BSS
* Created: 17 Jun 2014
* Updated: 24 Jun 2014
* Version: 0.0.1
* Desc   : Master subroutine for UV triggers.
*
* Triggers apply to individual files, so we can't specify a trigger
* to cover all files in a multi-file structure. We have to create a
* file pointer to each of the individual files and then apply the
* trigger to that file pointer. What we want to here is catch those
* instances and apply a global trigger for all the multi-files.
*
IF (INDEX(@PATH, '/', 1)) THEN DS = '/' ELSE DS = '\'
CONVERT '/\' TO DS:DS IN filename     ;* DS = path delimiter
IF INDEX(filename, DS, 1) THEN
  dictname = FIELD(filename, DS, DCOUNT(filename, DS) - 1)
  triggersub = dictname:'.TRIGGER'
END ELSE
  triggersub = filename:'.TRIGGER'
END

OPEN 'VOC' TO voc ELSE RETURN
READ vocrec FROM voc, triggersub THEN
  voctype = vocrec[1, 1]
  dispatchtype = vocrec<3>
  iscatalogued = (voctype EQ 'V') AND (dispatchtype EQ 'B')
END ELSE
  iscatalogued = @FALSE
END
IF NOT(iscatalogued) THEN RETURN

mode = timing:' ':event
CALL @triggersub(filename, mode, newid, newrec, oldid, oldrec)

RETURN
*
* ---------------------------------------------------------------------- *
*
END

Note this subroutine does one other thing - it provides a much reduced interface to the real trigger subroutine. This is essentially the same interface that is used by UniData and OpenQM.

Catalog the master subroutine

We are going to globally catalog this subroutine. If you are using UV in PICK mode, this is awkward.

Recent versions of UV (version 11.0 onwards) have an ICATALOG verb already in the VOC, but if you are on an older version of UV, then we need to create one. The ICATALOG verb uses the INFORMATION syntax for the CATALOG verb.

See if you have the ICATALOG verb in the VOC
- CT VOC ICATALOG
If it isn't present, then create an entry in the `VOC' that looks like this (don't include the line numbers):

0001    V
0002    CATALOG
0003    I
0004    BDGZ
0005    catalog
0006    INFORMATION.FORMAT

Now, CATALOG the trigger subroutine to the global catalog.

ICATALOG filename catalogname programname

ICATALOG  BP.Q  !QUV.TRIGGER QUV.TRIGGER
"!QUV.TRIGGER" cataloged.

Create a file for trigger testing

CREATE.FILE BSTEST 1,4,18 1,4,18 Trigger test file
Creating file "BSTEST" as Type 18, Modulo 1, Separation 4
Creating file "D_BSTEST" as Type 18, Modulo 1, Separation 4
Added "@ID", the default record fro RetrieVe, to "D_BSTEST".

Create a trigger on the file

The syntax to apply a trigger is:

CREATE TRIGGER triggername {BEFORE | AFTER} event [OR event] ON filename
FOR EACH ROW CALLING subroutine ;

where event is one or more of INSERT, UPDATE, or DELETE.

For our test, we will create a trigger named "BEFORE_UPDATE":

CREATE TRIGGER BEFORE_UPDATE BEFORE INSERT OR UPDATE OR DELETE ON BSTEST
FOR EACH ROW CALLING "!QUV.TRIGGER" ;

We need to quote the subroutine name because the catalogued name starts with an exclamation mark (!). If we don't quote the subroutine name, we get the following message:

UniVerse/SQL: ! unexpected

Don't forget the semi-colon at the end of the statement! This is an SQL statement, so we need to run by SQL rules!

The syntax to drop a trigger is:

DROP TRIGGER filename {triggername | ALL} ;

For example, to drop the trigger we have just created, we could use either of the following statements:

DROP TRIGGER BSTEST ALL ;
DROP TRIGGER BSTEST BEFORE_UPDATE ;

To list information about triggers on a file, use the LIST.SICA command:

LIST.SICA BSTEST
LIST.SICA BSTEST 05:20:59pm  07 Sep 2023   Page    1
===========================================
Sica Region for Table "BSTEST"

  Schema:          BSTEST
  Revision:        4
  Checksum is:     4585
    Should be:     4585
  Size:            292
  Creator:         -99
  Total Col Count: 0
    Key Columns:   0
    Data Columns:  0
  Check Count:     0
  Permission Count:0
  History Count:   0

  Trigger "BEFORE_UPDATE" is enabled, creator is "DESKTOP-JS2KF9J\universe".
      calls "!QUV.TRIGGER" for      Row Before Insert Update Delete

Create a trigger subroutine for this file

If we do anything to the BSTEST file now, the trigger will run the master trigger subroutine ... but nothing else will happen because we have not created (and catalogued) a trigger subroutine for this file.

According to the code we put in the master subroutine, the file trigger subroutine should have a name of: BSTEST.TRIGGER

Create the following subroutine, then compile it, and catalog it:

SUBROUTINE BSTEST.TRIGGER(filename, mode, newid, newrec, oldid, oldrec)
*****************************************************************************
* Trigger subroutine for BSTEST file(s).
*
CRT 'BSTEST.TRIGGER v1.0.0'
CRT 'File name: ':filename
CRT 'Mode     : ':mode
CRT 'New id   : ':newid
CRT 'New rec  : ':newrec
CRT 'Old id   : ':oldid
CRT 'Old rec  : ':oldrec

RETURN
*
* ------------------------------------------------------------------------- *
*
END

This trigger subroutine doesn't do anything except display the information passed to it.

Testing the Trigger

Writing to the file

Let's create a program that operates on the file:

PROGRAM BSTEST.TRIGGER.TEST
*****************************************************************************
ss = CONVERT(' ', @AM, UPCASE(@SENTENCE))
id = ''
LOCATE 'BSTEST.TRIGGER.TEST' IN ss SETTING ppos THEN
  id = ss< ppos + 1>
END

IF (id EQ '') THEN
  CRT 'No id specified in command'
  STOP
END

OPEN 'BSTEST' TO bstest ELSE STOP 201,'BSTEST'

READU rec FROM bstest, id LOCKED
  CRT 'Cannot get lock on id ':id:' in BSTEST'
  STOP
END ELSE
  rec = ''
END

rec<1> += 1
WRITE rec ON bstest, id ON ERROR
  CRT 'ERROR: ON ERROR clause: Status = ':STATUS()
THEN
  CRT 'Record written: Status = ':STATUS()
END ELSE
  CRT 'ERROR: ELSE clause: Status = ':STATUS()
END

STOP
END

Compile and catalog the program. Let's test the program (and the trigger):

RUN BP BSTEST.TRIGGER.TEST 1
BSTEST.TRIGGER v1.0.0
File name: BSTEST
Mode     : BEFORE UPDATE
New id   : 1
New rec  : 2
Old id   : 1
Old rec  : 1
Record written: Status = 0

OK. Everything there worked as it should. Of course, a real trigger shouldn't be writing stuff to the screen, but we are doing that just to show the trigger is working.

Using a Trigger to REJECT an update

One function of a trigger is to REJECT the update being made on the file. How do we do that?

In the BSTEST.TRIGGER subroutine, add the following two lines after the CRT statements:

text = 'File is locked. Update not allowed'
dummy = SetDiagnostics(text)

Re-compile the program. Now, run the update program again.

RUN BP BSTEST.TRIGGER.TEST 1
File name: BSTEST
Mode     : BEFORE UPDATE
New id   : 1
New rec  : 3
Old id   : 1
Old rec  : 2
Program "BSTEST.TRIGGER.TEST": Line 24, File is locked. Update not allowed
Program "BSTEST.TRIGGER.TEST": Line 24, Error performing trigger for "BSTEST".
Error: ELSE clause. STATUS: -4

That is really messy. However, we can see that:

the WRITE took the ELSE clause after the trigger failure rather than the ON ERROR clause
the STATUS function has returned a value of -4. According to the documentation, this indicates a trigger failure.

There doesn't appear to be a good way of suppressing the error lines that appear on the screen. If you wrap the WRITE block in HUSH statements, the UniVerse session gets terminated - presumably because the trigger could not write the error messages to the screen.

When does the trigger fire?

Remove (or comment out) the code that rejects the update in the BSTEST.TRIGGER subroutine. This returns the subroutine to the state shown above.

Check that the trigger is firing as it should:

RUN BP BSTEST.TRIGGER.TEST 1
BSTEST.TRIGGER v1.0.0
File name: BSTEST
Mode     : BEFORE UPDATE
New id   : 1
New rec  : 2
Old id   : 1
Old rec  : 1
Record written: Status = 0

So, the trigger is clearly working when we update the item. Now, try changing the name of the item-id in the file:

CNAME BSTEST 1,2
Changed record "1" to "2" in file "BSTEST".

Note there is no output from the trigger.

What about deleting the item?

DELETE BSTEST 2

File name: BSTEST
Mode     : BEFORE DELETE
New id   :
New rec  :
Old id   : 2
Old rec  : 2

1 records DELETEd.

So, the trigger works when we delete an item. And it also worked when we created the item, and when we updated the item. Those were the conditions we set for the trigger ... but it didn't work when we used CNAME to change the item-id - which is curious because we would expect this to involve deleting the record, and creating a new record.

This is not a function of the file size. We created this file with a modulo of 1 - but if we resize it with a modulo of 7, then use GROUP.STAT to see which group the item is in, we can see that the item moves when we change the item-id. Clearly, there is row activity occurring, but the row-based trigger is not firing when this activity occurs. Perhaps the @ID is not considered part of the "row".

Next, put some data back in the file. Now, run a CLEAR.FILE on the file:

CLEAR.FILE DATA BSTEST
File "BSTEST" has been cleared.

Once again, there is no sign that the trigger has executed.

Multi-Files

Let's look at how triggers work with multi-files. We'll start by dropping the existing trigger, and re-creating the file as a multi-file:

DROP TRIGGER BSTEST ALL ;
DELETE.FILE BSTEST
CREATE.FILE DICT BSTEST 1,4,18 Trigger test file
CREATE.FILE DATA BSTEST,BSTEST 1,4,18
CREATE.FILE DATA BSTEST,BSTEST2 1,4,18

Now, let's try adding a trigger to that part directly:

CREATE TRIGGER BEFORE_UPDATE BEFORE INSERT OR UPDATE OR DELETE ON BSTEST,BSTEST2
FOR EACH ROW CALLING "!QUV.TRIGGER" ;
Statement triggers are not supported.

OK. That didn't work. Let's try creating a Q-pointer, and using that as the file name. Here is the Q-pointer for this file:

CT VOC BSTEST2
0001 Q
0002
0003 BSTEST,BSTEST2

Now, try applying the trigger to this Q-pointer:

CREATE TRIGGER BEFORE_UPDATE BEFORE INSERT OR UPDATE OR DELETE ON BSTEST2
FOR EACH ROW CALLING "!QUV.TRIGGER" ;
UniVerse/SQL: Table "BSTEST2" does not exist.

So, that didn't work either. Let's make a full file pointer for the multi-file part:

CT VOC BSTEST2
0001 F
0002 BSTEST\BSTEST2
0003 D_BSTEST

Then apply the trigger to the new VOC reference:

CREATE TRIGGER BEFORE_UPDATE BEFORE INSERT OR UPDATE OR DELETE ON BSTEST2 FOR EACH ROW CALLING "!QUV.TRIGGER" ;
Adding trigger "BEFORE_UPDATE"

Now, go back to the trigger program for the BSTEST file (BSTEST.TRIGGER) and remove the setDiagnostics code.

Next, go back to the trigger test program (BSTEST.TRIGGER.TEST) and change the OPEN statement to operate on file BSTEST2.

Re-compile both programs and try running the trigger test program:

RUN BP BSTEST.TRIGGER.TEST 1
BSTEST.TRIGGER v1.0.0
File name: BSTEST\BSTEST2
Mode     : BEFORE INSERT
New id   : 1
New rec  : 1
Old id   :
Old rec  :
Record written: Status = 0

So, the trigger has run successfully on this multi-file.

It is worthwhile stepping through the logic here so that you can see exactly what happened:

The BSTEST.TRIGGER.TEST program:
- opened the BSTEST2 file (a multifile part).
- read the specified item, added one to it, and wrote it back - which activated the trigger
The trigger ran the globally catalogued subroutine (!QUV.TRIGGER)
- This identified the filename as BSTEST\BSTEST2
- Because this name contained the system delimiter, it extracted the base file name (BSTEST) and formed the name of the appropriate trigger subroutine (BSTEST.TRIGGER)
The real trigger subroutine was then called which displayed the information on the screen.

The important bit here is that we have written our master trigger subroutine in such a way that one trigger subroutine will be called for every part file that makes up a multi-file.