Defines a schema trigger.

Syntax

TRIGGER PROCEDURE FOR event OF object [ options ]
event

The event for which the schema trigger is being defined. The valid events are CREATE, DELETE, FIND, WRITE, and ASSIGN.

object

The object on which the event is defined. If the event is CREATE, DELETE, FIND, or WRITE, the object must be a reference to a database table. If the event is ASSIGN, the object must be a reference to a database field qualified by a table name.

options

Optional parts of the trigger header.

Headers for CREATE, DELETE, and FIND triggers take no options. Their syntaxes are as follows:

TRIGGER PROCEDURE FOR CREATE OF table
 
TRIGGER PROCEDURE FOR DELETE OF table
 
TRIGGER PROCEDURE FOR FIND OF table

In the header for a WRITE trigger you can optionally include one or two buffer names.

TRIGGER PROCEDURE FOR WRITE OF table
  [ NEW [ BUFFER ] buffer-name1 ]
  [ OLD [ BUFFER ] buffer-name2 ]

For an ASSIGN trigger procedure, there are two ways you can specify the field/value for validation. The first way is to use the OF phrase and the second way is to use the NEW [VALUE] phrase. If you use the OF phrase, the value from the ASSIGN has been put into the buffer, so you can check it from there. However, the change has not been committed yet, so if this trigger returns error, the new value will be backed out. If you use the NEW VALUE phrase, then the new value is available for validation via the value1 variable. Each way accomplishes the same task; it's a matter of coding preference.

TRIGGER PROCEDURE FOR ASSIGN
  {     { OF table .field }
     |  { NEW [ VALUE ] value1
            { AS data-type | LIKE db-field }
         }
  }
  [ COLUMN-LABEL label ]
  [ FORMAT format-string ]
  [ INITIAL constant ]
  [ LABEL label-string ]
  [ NO-UNDO ]
  [ OLD [ VALUE ] value2
      { AS data-type | LIKE db-field  }
      [ COLUMN-LABEL label ]
      [ FORMAT format-string ]
      [ INITIAL constant ]
      [ LABEL label-string ]
      [ NO-UNDO ]
  ]

Examples

The following is a WRITE trigger for the Customer table. It uses the OLD BUFFER option so that it can determine whether the CustNum value has changed. If the Customer's outstanding balance exceeds its Credit Limit, the trigger returns the error condition (in which case the record is not updated).

r-wrcust.p 

TRIGGER PROCEDURE FOR WRITE OF Customer OLD BUFFER oldCustomer.

/* Variable Definitions */
DEFINE VARIABLE ix          AS INTEGER NO-UNDO.
DEFINE VARIABLE Outstanding AS INTEGER NO-UNDO.

/* Check to see if the user changed the Customer Number */
IF Customer.CustNum <> oldCustomer.CustNum 
  AND oldCustomer.CustNum <> 0 THEN DO:
  /* If the user changed the Customer Number, find all related Orders and
     change their Customer numbers. */
  FOR EACH order OF oldCustomer:
    Order.CustNum = Customer.CustNum.
    ix = ix + 1.
  END.
  IF ix > 0 THEN
    MESSAGE ix "Orders changed to reflect the new Customer number!".
END.

/* Ensure that the Credit Limit value is always greater than the sum of
   this Customer's outstanding balance */
FOR EACH Order OF Customer NO-LOCK:
  FOR EACH OrderLine OF Order NO-LOCK:
    Outstanding = Outstanding + (OrderLine.Qty * OrderLine.Price).
  END.
END.
FOR EACH Invoice OF Customer NO-LOCK:
  Outstanding = Outstanding + 
    (Invoice.Amount - (Invoice.TotalPaid + Invoice.Adjustment)).
END.

IF Customer.CreditLimit < Outstanding THEN DO:
  MESSAGE "This Customer has an outstanding balance of: " Outstanding 
    ".  The Credit Limit MUST exceed this amount!".
  RETURN ERROR.
END.

The following is an example of an ASSIGN trigger using the OF phrase. 

TRIGGER PROCEDURE FOR ASSIGN OF Customer.NAME.

IF Customer.NAME = "Customer X" THEN

DO:

   MESSAGE "Customer X not allowed."

       VIEW-AS ALERT-BOX INFORMATION BUTTONS OK.

   RETURN ERROR.        

END.

The following is an example of an ASSIGN trigger using the NEW [VALUE] phrase.

TRIGGER PROCEDURE FOR ASSIGN NEW VALUE value1 AS CHARACTER.

IF value1 = "Customer X" THEN

DO:

   MESSAGE "Customer X not allowed."

       VIEW-AS ALERT-BOX INFORMATION BUTTONS OK.

   RETURN ERROR.        

END.

Notes

  • Use the Data Dictionary to associate a trigger procedure with a table or field in the database.
  • Some 3GL applications execute schema triggers. Triggers might also be executed in batch mode. Therefore, you should avoid any user-interface interactions within schema trigger procedures.
  • For a TRIGGER PROCEDURE FOR WRITE the OLD BUFFER does not contain a before image for CLOB or BLOB columns.
  • For WRITE and REPLICATION-WRITE triggers, do not try to access any lob fields in the OLD BUFFER buffer. If the value of the field was changed, the old buffer would not contain the old value of the lob field. This is because the actual data for the lob field does not reside in the record itself, and the lob field in the old buffer would possibly be pointing at random data or have the wrong data size.

See also

PROCEDURE statement