Triggers

Triggers

Top  Previous  Next

 

A trigger is an optional user written subroutine associated with a QM data file and configured to be executed when certain file operations are performed. Executed before a write or delete, the trigger can be used to apply data validation. Executed after a record is written or deleted, the function can trigger other events such as related file updates. Trigger functions can also be executed after a read and before or after a clear file operation.

 

The trigger function is simply a catalogued QMBasic subroutine which is automatically executed as part of the file operation. The subroutine is passed a mode flag to indicate the action being performed, the record id, the record data (read or write operations) and a flag indicating whether the QMBasic ON ERROR clause is present. The subroutine may do whatever processing the application designer wishes. If the write or delete is to be disallowed, the pre-write or pre-delete trigger function should set the @TRIGGER.RETURN.CODE variable to a non-zero value such as an error number or an error message text to cause the write or delete to take its ON ERROR clause if present or to abort if omitted. The STATUS() function will return ER$TRIGGER when executed in the program that initiated the file operation. Programs should test STATUS() rather than testing for @TRIGGER.RETURN.CODE being non-zero to determine whether the trigger function has disallowed the write or delete as @TRIGGER.RETURN.CODE is only updated when the error status is set.

 

The trigger function name is limited to 32 characters and is set up using the SET.TRIGGER command. After it has been set up, the trigger function is loaded into memory when first needed and is called for all operations defined by the mode settings in the SET.TRIGGER command. Setting or removing a trigger function or modifying and re-cataloguing the trigger function will take immediate effect even if the file is open.

 

A trigger function on a hashed file will be called by all updates to the file in the modes for which the trigger is active. A trigger function on a directory file will be called only by updates from within QM and not for use of sequential file operations (WRITESEQ, WRITEBLK, etc), OSWRITE or OSDELETE. Some implications of this are that query processor CSV or delimited reports directed to a file and QMBasic compiler listing files will not call the trigger function.

 

If the trigger function is not in the catalogue or has the incorrect number of arguments, no error occurs until the first action that would call the function. Note that the trigger function must be visible to all accounts that may reference the file. Where a file is used by multiple accounts, this can be achieved by using global cataloguing, sharing a private catalogue, or ensuring that the VOC entry for a locally catalogued trigger function is present in each account. Although it would be possible for a shared file to use a different trigger function depending on the account from which it is referenced, this is not recommended.

 

Important Note: Because information about the trigger is stored in the file, copying the directory that represents a QM file that uses triggers will result in the new file also using the trigger.

 

 

The interface into a trigger function is:

 

SUBROUTINE name(mode, id, data, on.error, fvar)

 

where

nameis the trigger subroutine name.
modeindicates the point at which the trigger function is being called:

1

0x01

FL$TRG.PRE.WRITE

before writing a record

2

0x02

FL$TRG.PRE.DELETE

before deleting a record

4

0x04

FL$TRG.POST.WRITE

after writing a record

8

0x08

FL$TRG.POST.DELETE

after deleting a record

16

0x10

FL$TRG.READ

after reading a record

32

0x20

FL$TRG.PRE.CLEAR

before clearing the file

64

0x40

FL$TRG.POST.CLEAR

after clearing the file

Other values may be used in the future. Trigger functions should be written to ignore unrecognised values. Use of the hexadecimal values (e.g. 0x04) makes it easier to form composite mode values as described below.

idis the id of the record to be written or deleted.
datais the data. This is a null string for a delete or clearfile action. When using triggers on data collection files, the data argument will be a data collection.
on.errorindicates whether the program performing the file operation has used the ON ERROR clause to catch aborts.
fvaris the file variable that can be used to access the file. Beware that reading, writing or deleting records via this file variable may cause a recursive call to the trigger function. This argument can be omitted for compatibility with earlier releases.

 

When writing trigger functions, the original data of the record to be written or deleted can be examined by reading it in the usual way. Trigger functions should not attempt to write the record for which they are called. Neither should they release the update lock on this record as this could cause concurrent update of the record.

 

If a pre-write trigger modifies the value of the data argument, the modified data is written to the file but the variable used in the write operation that fired the trigger is not updated. In a post-read trigger, the data argument on entry to the trigger function contains the data read from the file and any modification made to data will be seen in the data returned from the read operation that fired the trigger. In all modes, changes to the value of id will not affect database updates in any way.

 

Trigger functions may perform all of the actions available to other QMBasic subroutines including performing updates that may themselves cause trigger functions to be executed.

 

The mode values correspond to bit positions in a binary value and hence a condition such as

IF MODE = 4 OR MODE = 8 THEN ...

is equivalent to

IF BITAND(MODE, 12) THEN ...

which can simplify some trigger functions. Writing this with the mode value in hexadecimal form can make it easier to combine modes.

IF BITAND(MODE, 0x0C) THEN ...

 

 

Example

 

The following simple trigger function could be used to capture all writes and deletes, logging the new record data in a "replication log" file which can then be used to maintain a copy of the data on a separate server. The same trigger function can be applied to many files. Whilst this is a valid example of the use of triggers, QM's data replication facilities provide a better solution.

 

SUBROUTINE REPTRIGGER(MODE, ID, DATA, ON.ERROR, FVAR)

$CATALOGUE GLOBAL

 

$INCLUDE KEYS.H

 

  COMMON /REPLOG/RLG.F     ;* REPLOG file variable, persistent across calls

 

  FN = FILEINFO(FVAR, FL$VOCNAME)

 

  IF NOT(FILEINFO(RLG.F, FL$OPEN)) THEN

     OPEN REPLOG TO RLG.F ELSE

        LOGMSG 'Unable to open REPLOG'

        RETURN

     END

  END

 

  BEGIN CASE

     CASE MODE = FL$TRG.PRE.WRITE

        RECORDLOCKU RLG.F, FN:' ':ID

        WRITE DATA TO RLG.F, FN:' ':ID

 

     CASE MODE = FL$TRG.PRE.DELETE

        RECORDLOCKU RLG.F, FN:' ':ID

        WRITE '' TO RLG.F, FN:' ':ID

  END CASE

END

 

On first call, this trigger routine will open a file named REPLOG which is used for logging. Because the file variable is stored in a common block, the file will remain open for subsequent calls to the trigger function.

 

For write operations (mode FL$TRG.PRE.WRITE), the trigger routine writes a copy of the data to a record in REPLOG with its id constructed from the file name and record id of the write that is being replicated.

 

For delete operations (mode FL$TRG.PRE.DELETE), the trigger routine writes a null record to REPLOG with its id constructed in the same way.

 

A separate program, perhaps running via QMNet from another server, could periodically read all records from the REPLOG file and apply the changes to a copy of the original data, deleting the REPLOG entry. Note that the REPLOG is not a sequential audit trail but stores only the last update to any record. Thus a long series of updates will only ever produce a single REPLOG record.

 

There are two assumptions made by this example. Firstly, the data files being replicated never contain null records. We can, therefore, recognise a delete operation from the presence of a null record in the REPLOG. If this were not a safe assumption, we would need to add an extra field to the REPLOG data to say whether this was a write or a delete.

 

Secondly, there is an assumption that a file only has a single VOC entry defining it. If this were not the case, a combination of the filename (FN variable) and record id (ID argument) would not be a unique reference to the record. If this assumption was not valid, it would be possible to use FILEINFO() to get the pathname of the file and use this instead.

 

 

Use of a THEN/ELSE Clause in WRITE, MATWRITE or DELETE

 

For compatibility with the way in which triggers operate in some other multivalue products, the WRITE, MATWRITE and DELETE statements have an optional THEN/ELSE clause. Because this would otherwise lead to a syntactic ambiguity, compilation of programs that use this clause requires the WRITE.DELETE.THEN.ELSE option of the $MODE compiler directive to be enabled. Once this is done, the optional THEN/ELSE clauses can be included in their usual position, after the ON ERROR clause. The on.error argument to the trigger function will be True if there is either an ON ERROR or THEN/ELSE clause, indicating that trigger errors are handled programmatically.

 

When a WRITE, MATWRITE or DELETE statement has a THEN/ELSE clause, a failure returned from a trigger function, typically as a result of a pre-write or pre-delete data validation error, will cause the ELSE clause to be executed instead of the ON ERROR clause.