OpenEdge Development: Progress 4GL Handbook

Updating Your Database and
Writing Triggers

In this chapter, you’ll learn about updating your database and writing triggers—topics you need to understand to build the business logic that is the heart of your application.

This chapter includes the following sections:

Transactions in Progress
Building updates into an application
Defining database triggers

Transactions in Progress

A transaction is a set of related changes to the database that the database either completes in its entirely or discards, leaving no modification to the database. In other contexts this might be referred to as a physical transaction or commit unit. In the most basic case, Progress assures that if you assign multiple field values to a database table, it either applies all of those changes or none of them. But in many cases, you need to update multiple related records in the database with the assurance that all of the changes are made together. For example, you might update an Order, its OrderLines, and the Customer for the Order in a single transaction. In the business logic for that operation you update the Customer Balance to reflect the total of all the OrderLines. You want to know that the adjustment you make to the Customer Balance is always written to the database along with the OrderLine records that are the detail for that adjustment, and that some subset of these changes are never made without the rest of them. If the changes can’t be completed for any reason (whether it is a validation error that your application detects, a database error, a system hardware failure, or anything else), the database needs to make all the changes successfully or back out any partial changes so that the records are restored to their state before the transaction began. Many users need to be able to update the same database concurrently with the same assurance. Progress transactions and the integrity of the OpenEdge database, together with record locking, assure that this is always the case. Your application procedures define the scope of every transaction that updates the database so that you have complete control over the unit of work that is reliably committed or rolled back.

Transaction blocks

Transactions in Progress are scoped to blocks. You’re already familiar with the concept of building procedures up out of nested blocks of procedural statements and how record scoping is affected by those blocks. Transaction scoping works in much the same way and is closely tied to the scoping of records to blocks. Some blocks in Progress procedures are transaction blocks and some aren’t, according to these rules:

You can explicitly include the TRANSACTION keyword on a FOR EACH or REPEAT block, or on a DO block with the optional error-handling phrase beginning ON ERROR. Any block that uses the TRANSACTION keyword becomes a transaction block.
Any block that directly updates the database or directly reads records with EXCLUSIVE-LOCK likewise becomes a transaction block. This can be a procedure block, a trigger block, or each iteration of a DO, FOR EACH, or REPEAT block.

A direct database update can be, for example, a CREATE, DELETE, or ASSIGN statement. A block is said to be directly reading records with EXCLUSIVE-LOCK if at least one of the FIND or FOR EACH statements that has the EXCLUSIVE-LOCK keyword is not embedded in an inner block enclosed within the block in question.

A DO block without the NO-ERROR phrase does not automatically have the transaction property. Also, a procedure that would start a transaction on its own can run from another procedure that has already started a transaction. In this case, you enclose the called procedure in the larger transaction of the calling procedure. Once a transaction is started, all database changes are part of that transaction until it ends. Each user or session can have just one active transaction at a time. If one transaction block is nested within another, the inner block forms a subtransaction that you can programmatically undo if it causes an error or fails some validation constraint. You’ll learn about subtransactions later in "Managing Transactions."

Building updates into an application

In this section, you build a new window to display data and capture updates, along with a separate procedure to validate those updates and write them to the database. Because you should get into the habit of not mixing user interface procedures and data management procedures, the UI and the database access are in separate procedures. You’ll use temp-tables to pass data back and forth and display data from those temp-tables in the user interface.

To define temporary tables for your window:

In the AppBuilder, select New Window.
Choose the Procedure settings button:



First, you need to define three temp-tables that hold data received from the database. If you let the AppBuilder generate the definitions for them, you can then provide a user interface for them just as you can for database tables.
In the Procedure Settings dialog box, choose the Temp-Table Definitions button .
Choose the Add button, then select the Customer table from the Table Selector dialog box:



By default this generates a temp-table definition for a table with the same name, Customer, that is exactly LIKE the database table.
To change this default, type ttCust as the Table Name:



Note that there is a NO-UNDO toggle box that is checked on by default. Leave this on. This option adds the NO-UNDO keyword to the temp-table definitions, which is appropriate because Progress does not need to roll back any changes to the temp-tables themselves as part of a transaction.
Choose Add again, then this time select the Order table.
In the editor labeled Additional Fields, add a new field definition for a CHARACTER field called TransType. Your procedure uses this to keep track of changed Order records.
In the same editor, define an index for your temp-table called OrderIdx with the OrderNum and TransType fields:



Repeat Step 3 through Step 8 for the OrderLine table. Add a temp-table for OrderLine called ttOline with a TransType field and an INDEX OlineIdx on OrderNum, LineNum, and TransType.
Choose OK to save all these new temp-table definitions.

To set up the user interface of your window:

In the AppBuilder Palette, choose the DB-Fields icon and then click on your design window.
When you use the AppBuilder to define temp-tables for a procedure, it keeps track of them by treating them as if they were in a special database called Temp-Tables, so you see this listed along with the actual database you’re connected to:



Select Temp-Tables from the Databases list and ttOrder from the Tables list.
From the Multi-Field Selector dialog box, choose the fields: OrderNum, OrderDate, PromiseDate, ShipDate, PO, and OrderStatus, and lay them out in the frame.
Give your window the title Order Updates.
Give the window’s frame the name OrderFrame.
Save this procedure as h-OrderUpdate.w:



Note that because you used the LIKE keyword to define your ttOrder temp-table based on the Order database table, its fields inherit all the attributes of the corresponding database fields, including their label, data type, and display type.
Add a rectangle under the Order fields and give it a Background Color of brown just to create a divider between the Order fields and the rest of the frame.
Add DB-Fields from the temp-table ttCust.
When you go to do this, the AppBuilder tries to define a default join between ttCust and the ttOrder temp-table you’ve already used in the frame. It’s unable to do this for temp-tables so it puts up an Advisor message to this effect.
Select the Cancel option to tell the AppBuilder not to worry about the default join, then choose OK:



Your procedure will receive the correct Customer for an Order from the database procedure it uses.
Arrange the fields from ttCust in the lower part of the frame.
Disable the ttCust fields. These are used only to display values from the Order’s Customer.
When you’re done, your design window should look something like this:

To add a browse for the Order’s OrderLines:

Select the Browse icon from the AppBuilder Palette and drop it onto the bottom of the design window.
Add ttOline from the list of Available Tables.
Once again the AppBuilder tries to provide you with a default join that you won’t need.
Select ttOline from the Selected Tables list and click the Switch Join Partners button.
In the Select Related Table dialog box, select (None):



Choose OK. The Query Builder shows ttOline without a join to another table:



Choose the Fields button in the Query Builder. Select all of the ttOline fields in an order such as: OrderNum, Line Num, Item Num, Price, Qty, Extended Price, and Discount.
Note that the fields from the ttCust and ttOrder tables are also in the list, but you don’t want them in the browse.
Check on the Enable toggle box for the columns ItemNum, Price, Qty, Discount, and OrderLineStatus so that the user can change these values for an Order’s lines.
Change the browse Object Name to OlineBrowse.
Now the window should look like this:

To write code that populates the fields and the browse and lets you update the Order:

In the Definitions section, define a handle called hLogic:

/* Local Variable Definitions ---                                    */ DEFINE VARIABLE hLogic AS HANDLE      NO-UNDO.

This variable holds the procedure handle of the procedure you write next where all the logic is to read records for the Order from the database and accept changes back from the client.
In the main block, add a line to run the h-OrderLogic.p logic procedure as a persistent procedure and save its handle:

MAIN-BLOCK: DO ON ERROR   UNDO MAIN-BLOCK, LEAVE MAIN-BLOCK    ON END-KEY UNDO MAIN-BLOCK, LEAVE MAIN-BLOCK:   RUN enable_UI.   RUN h-OrderLogic.p PERSISTENT SET hLogic.   IF NOT THIS-PROCEDURE:PERSISTENT THEN     WAIT-FOR CLOSE OF THIS-PROCEDURE. END.

Add two buttons to the right of the OrderNum field: call one btnFetch with the label Fetch, and call the other btnSave with the label Save.
You’ll use these buttons to retrieve data from the logic procedure and then to return any changes. The user can enter an Order number and then chooses Fetch to retrieve it with its Customer and OrderLines, make changes, and then send the changes back to the logic procedure by choosing Save.
In the CHOOSE trigger for btnFetch, define a buffer named bUpdateOline for the ttOline table:

DO:   DEFINE BUFFER bUpdateOline FOR ttOline.

You’ll use this buffer to have two records for each OrderLine: one with any changes that are made and one that saves the original values before changes.
Empty the three temp-tables to prepare for fetching a requested Order and its customer and lines:

/* Remove any data leftover from previous calls. */     EMPTY TEMP-TABLE ttCust.     EMPTY TEMP-TABLE ttOrder.     EMPTY TEMP-TABLE ttOline.

Run an internal procedure called fetchOrder in the logic procedure’s handle. This needs to pass in the value in the OrderNum field. It gets back three temp-tables for the Order and its related data:

/* Retrieve all related records for the Order. */     RUN fetchOrder IN hLogic       (INPUT INTEGER(ttOrder.OrderNum:SCREEN-VALUE),        OUTPUT TABLE  ttOrder,        OUTPUT TABLE  ttCust,        OUTPUT TABLE  ttOline) .

Add code so that if the Order isn’t found or if there’s some other error, this comes back in the RETURN-VALUE:

/* Check for an error such as order-not-found. */     IF RETURN-VALUE NE "" THEN     DO:       MESSAGE RETURN-VALUE.       RETURN NO-APPLY.     END.

Because there’s always exactly one ttOrder record (the one the user requested) and one ttCust record for that Order, you can just FIND these in the temp-tables that came back to bring them into their respective buffers, and then display their field values:

FIND ttOrder.     FIND ttCust.     DISPLAY ttOrder.OrderNum ttOrder.OrderDate ttOrder.PromiseDate           ttOrder.ShipDate ttOrder.PO ttOrder.OrderStatus           ttCust.CustNum ttCust.NAME ttCust.City ttCust.State           ttCust.CreditLimit ttCust.Balance       WITH FRAME OrderFrame.

For each of the ttOlines, you need to create a copy of the record that holds any updates that are made to it. This is so that you can also keep the original before image of each record to compare with the database, to see if the record has been changed by another user. The copy that can be updated is marked with a TransType of “U”:

/* For each OrderLine, create a record to hold any updates. */     FOR EACH ttOline WHERE ttOline.TransType = "":         CREATE bUpdateOline.         BUFFER-COPY ttOline TO bUpdateOline ASSIGN TransType = "U".     END.

To display the OrderLine records the user can update, you just open the browse’s query:

/* Display just the updatable records in the browse. */    OPEN QUERY OlineBrowse FOR EACH ttOline WHERE ttOline.TransType = "U". END.

To create the separate logic procedure that retrieves data from the database and later applies any updates to the database:

Select New Structured Procedure in the AppBuilder to create a procedure you’ll call h-OrderLogic.p.
In the Definitions section, repeat the temp-table definitions. Make the temp-tables NO-UNDO (which is how you defined them in the calling procedure):

/* *************************** Definitions ************************ */ DEFINE TEMP-TABLE ttCust NO-UNDO LIKE Customer. DEFINE TEMP-TABLE ttOrder NO-UNDO LIKE Order FIELD TransType AS CHARACTER     INDEX OrderIdx OrderNum TransType. DEFINE TEMP-TABLE ttOline NO-UNDO LIKE OrderLine FIELD TransType AS CHARACTER     INDEX OlineIdx OrderNum LineNum TransType.

You define them NO-UNDO because changes to the records in the temp-tables themselves do not need to be rolled back by the Progress transaction mechanism. Defining them as NO-UNDO saves Progress the overhead of preparing to roll back changes.
In a larger application, these could become include files used in all the procedures that reference these tables.
Add the fetchOrder internal procedure to load all the needed data for the OrderNum passed in:

/*---------------------------------------------------------------------   Procedure fetchOrder:   Purpose:  Return an Order, its Customer, and all its related OrderLines             to the caller. ---------------------------------------------------------------------*/ DEFINE INPUT PARAMETER piOrderNum AS INTEGER    NO-UNDO. DEFINE OUTPUT PARAMETER TABLE FOR ttOrder. DEFINE OUTPUT PARAMETER TABLE FOR ttCust. DEFINE OUTPUT PARAMETER TABLE FOR ttOline. FIND Order WHERE Order.OrderNum = piOrderNum NO-LOCK  NO-ERROR. IF NOT AVAILABLE(Order) THEN     RETURN "Order not found". EMPTY TEMP-TABLE ttOrder. CREATE ttOrder. BUFFER-COPY Order TO ttOrder. FIND Customer OF Order. EMPTY TEMP-TABLE ttCust. CREATE ttCust. BUFFER-COPY Customer TO ttCust. EMPTY TEMP-TABLE ttOline. FOR EACH OrderLine OF Order:     CREATE ttOline.     BUFFER-COPY OrderLine TO ttOline. END. RETURN "". END PROCEDURE.

Now the retrieval end of your sample procedure window should work.
To test it, run h-OrderUpdate.w, type an Order Number, and then choose the Fetch button:



You can modify fields in the Order record and in one or more of the browse rows for OrderLines. Remember that you are not making changes to the database when you do this, because your user interface is just working with temp-tables. So you need to write trigger code for the Save button and a procedure in the logic procedure to handle the updates.

To add the code that handles the database updates:

Add this trigger code for the Save button btnSave. It defines a second buffer for each of the updateable temp-tables, and a variable to hold the result of a buffer compare:

DO:   DEFINE BUFFER bOldOrder FOR ttOrder.   DEFINE BUFFER bOldOline FOR ttOline.   DEFINE VARIABLE cCompare AS CHARACTER  NO-UNDO.

The procedure hasn’t saved changes for the Order into the temp-table record from the screen buffer yet.
Create a temp-table record to hold the updates and then assign all the screen fields, saving the original version in the separate buffer bOldOrder:

/* Create an Update record in the Order temp-table for any changes. */   FIND bOldOrder.   CREATE ttOrder.   BUFFER-COPY bOldOrder TO ttOrder.   ASSIGN ttOrder.PromiseDate ttOrder.ShipDate ttOrder.PO          ttOrder.OrderStatus ttOrder.TransType = "U".

Include the following code to check whether any fields were actually changed by comparing the two records. If there are no changes, it deletes the before image as a signal to the SAVE procedure:

/* Check to see if anything was changed, and if not, then delete the     before image record. */   BUFFER-COMPARE ttOrder EXCEPT TransType TO bOldOrder SAVE cCompare.   IF cCompare = "" THEN      DELETE bOldOrder.

The code on the Fetch button creates an update record for every OrderLine.
Add code to check which records were actually updated and delete the before image for those records that weren’t. This code tells the save procedure which records changed and allows the window to browse all the OrderLines by selecting those marked with a “U”:

/* For every OrderLine, compare the original and Update records to see      if anything was in fact changed; if not, delete the original before      saving. */   FOR EACH bOldOline WHERE bOldOline.TransType = "U":       FIND ttOline WHERE ttOline.OrderNum = bOldOline.OrderNum AND          ttOline.LineNum = bOldOline.LineNum AND ttOline.TransType = "".       BUFFER-COMPARE bOldOline EXCEPT TransType TO ttOline SAVE cCompare.       IF cCompare = "" THEN  /* If there were no changes, */          DELETE ttOline. /* delete the unchanged version of the rec. */   END.

Run a saveOrder procedure to return the changes. The Order and OrderLine tables are passed as INPUT-OUTPUT parameters to allow the logic procedure to return either changes made by another user, if the update is rejected for that reason, or the final versions of all the records, in case they are further changed by update logic:

RUN saveOrder IN hLogic       (INPUT-OUTPUT TABLE ttOrder,       INPUT-OUTPUT TABLE ttOLine).

Add code so that the RETURN-VALUE indicates the Order was changed out from under you. The new values are displayed:

IF RETURN-VALUE = "Changed Order" THEN   DO:       MESSAGE "The Order has been changed by another user.".       FIND ttOrder WHERE ttOrder.TransType = "U".       DISPLAY ttOrder.OrderDate ttOrder.PromiseDate ttOrder.Shipdate               ttOrder.PO ttOrder.OrderStatus WITH FRAME OrderFrame.       RETURN NO-APPLY.   END.

Add code that, if any OrderLines changed, returns and displays the updates made by another user. Otherwise, it reopens the browse query to display the final versions of all the OrderLines, including any changes made in the logic procedure. Those changes are all in the temp-table record versions marked with a “U”:

ELSE IF RETURN-VALUE = "Changed Oline" THEN      MESSAGE "One or more OrderLines have been changed by another user.".    OPEN QUERY OlineBrowse FOR EACH ttOline WHERE ttOline.TransType = "U". END.  /* END trigger block */

To create another internal procedure called saveOrder in the logic procedure h-OrderLogic.p:

In the save procedure, define the INPUT-OUTPUT parameters for the two updated tables:

/*---------------------------------------------------------------------   Procedure saveOrder:   Purpose:     Accepts updates to an Order and its related records and                saves them to the database, returning any field values                calculated during the update process. ---------------------------------------------------------------------*/ DEFINE INPUT-OUTPUT PARAMETER TABLE FOR ttOrder. DEFINE INPUT-OUTPUT PARAMETER TABLE FOR ttOline.

Define a second buffer for each of them to allow original values to be compared with the database:

DEFINE BUFFER bUpdateOrder FOR ttOrder. DEFINE BUFFER bUpdateOline FOR ttOline.

Define explicit buffers for the database tables to make sure that no record is inadvertently scoped to a higher level in the procedure (which is always a good idea in writing code that does updates):

DEFINE BUFFER Order FOR Order. DEFINE BUFFER OrderLine FOR OrderLine.

Add a character variable that holds a record of any differences found by a buffer compare:

DEFINE VARIABLE cCompare AS CHARACTER NO-UNDO.

Open a transaction block so that all the comparisons with the original database records and all the update are made together. Then try to find the before image of the Order temp-table record. If it’s there, then the Order was changed. Next, find the corresponding database record using the table’s unique primary key. You do this with an EXCLUSIVE-LOCK because you’ll later update this database record if no one else has changed it. This also assures that no one else can change it after you first read it. You compare the two and reject the update if the record has been changed by someone else:

DO TRANSACTION ON ERROR UNDO, LEAVE:     FIND ttOrder WHERE ttOrder.TransType = "" NO-ERROR.     IF AVAILABLE (ttOrder) THEN       /* If this rec is there then the Order was updated on the client. */     DO:       FIND Order WHERE Order.OrderNum = ttOrder.OrderNum EXCLUSIVE-LOCK.       BUFFER-COMPARE ttOrder TO Order SAVE cCompare.       IF cCompare NE "" THEN       DO:     /* Somebody else has changed the record since we read it. */             BUFFER-COPY Order TO ttOrder.   /* Return the changes. */             RETURN "Changed Order".          END. /* END DO IF cCompare Not "" */

If it hasn’t been changed, then you find the Update version of the ttOrder and copy your changes from there to the database record:

ELSE DO: /* FIND the updated tt rec and save the changes. */             FIND bUpdateOrder WHERE bUpdateOrder.TransType = "U".             BUFFER-COPY bUpdateOrder TO Order.  /* Save our changes. */         END.   /* END ELSE DO */       END.     /* END OF AVAILABLE ttOrder */

Do the same for any changed OrderLines. For each before-image record, which you read into the ttOline buffer, find the changed version in the bUpdateOline buffer, find the corresponding database record, compare the before version with the database, and reject the update if someone else has changed it:

/* For each OrderLine that has a before-image (unchanged) record,       make sure it hasn't been changed by another user. */     FOR EACH ttOline WHERE ttOline.TransType = "":         /* Bring the updated version into the other buffer. */         FIND bUpdateOline WHERE bUpdateOline.TransType = "U" AND                 bUpdateOline.OrderNum = ttOline.OrderNum AND                 bUpdateOline.LineNum = ttOline.LineNum.         FIND OrderLine WHERE OrderLine.OrderNum = ttOline.OrderNum AND                 OrderLine.LineNum = ttOline.LineNum EXCLUSIVE-LOCK.         BUFFER-COMPARE ttOline TO OrderLine SAVE cCompare.         IF cCompare NE "" THEN         DO: /* Somebody else has changed the record since we read it.       Copy the changes to the Update version to display on the client. */         BUFFER-COPY OrderLine TO bUpdateOline. /* Return the changes. */             RETURN "Changed Oline".         END.  /* END DO IF cCompare NE "" */

Otherwise, apply your changes to the database:

ELSE DO:  /* Save our OrderLine changes. */            BUFFER-COPY bUpdateOline TO OrderLine.

There’s some additional code here that needs explanation. You release the OrderLine, which forces it to be written immediately to the database without waiting for the iteration of the FOR EACH ttOline block within the transaction. As the record is written out, any database trigger procedures for the table execute. Trigger procedures let you execute standard update logic when a database record is modified, created, or deleted, so that it is always run no matter where the update occurs within your application. You learn about how to write and use trigger procedures in the "Defining database triggers" . The trigger is a kind of side effect to the update.
To see its effects, you need to re-read the record after the RELEASE forces the trigger to fire and then bring any changes the trigger made back into the temp-table, where it can be returned to the client and displayed:

RELEASE OrderLine.      /* Re-find the db record to capture any changes made by a trigger. */      FIND OrderLine WHERE OrderLine.OrderNum = bUpdateOline.OrderNum AND                OrderLine.LineNum = bUpdateOline.LineNum NO-LOCK.      BUFFER-COPY OrderLine TO bUpdateOline.      END.    /* END ELSE DO If we updated the OrderLine */    END.      /* END DO FOR EACH ttOline */   END.        /* END DO Transaction */ END PROCEDURE.

To test your logic procedure:

Save h-OrderLogic.p.
Rerun the OrderUpdate window.
Select Order 124 again (or any other Order you like), then choose Fetch.
Make a change to one or more of the Order fields. For example, enter a purchase order number (PO).
Change the Price or Qty for some of the OrderLines:



Choose the Save button.
Notice that the Extended Price field (which is not enabled for input) changes for any OrderLine whose Price or Qty you changed:



This is the effect of the database trigger procedure for the WRITE event on the OrderLine table. Re-reading the changed record into the temp-table, sending it back to the client window, and reopening the browse query displayed those changes. Later, you’ll learn how to write these trigger procedures yourself.

Defining database triggers

There are certain basic operations that you would always like to have occur when an application updates a record in a database table, regardless of where in a procedure the update occurs. In this way, you can be assured that code that acts as a validation check on an attempted update or a side effect of an update is executed consistently, without having to remember to add the code to every place in the application that changes that table. You can create special Progress 4GL procedures, called database trigger procedures, that contain this common code. You can register them in the Data Dictionary so that Progress knows to always run them when a corresponding database event occurs. They are also called schema trigger procedures because their names are registered in the database schema. It’s important to understand that the procedures themselves are not actually stored in the schema.

For the most part, you should use these triggers for basic referential integrity checks to maintain the consistency and integrity of your application data. For example, the CustNum field in the Order table acts as a foreign key pointing to a Customer record with the same CustNum value that is the parent record for the Order. You would normally never want a user to save an Order (to the database) that didn’t have a valid Customer Number. Your application code itself should enforce this, but you might want to have a check at a lower level so that you can be certain it always executes.

A trigger can also change other database records or other field values in the updated record as a side effect of a change. For example, you might want to delete all OrderLines for an Order if the user deletes the Order. Alternatively, you might want to check whether there are any outstanding OrderLines before allowing the user to delete the Order. Or you might want to calculate values for other fields, in the same table or in other tables, when one field value changes. The ExtendedPrice field that you saw updated in the OrderLine table is an example of this kind of trigger.

Database trigger guidelines

You can write just about any 4GL code you want into a trigger procedure, but there are a few guidelines that will help you use them widely and effectively. You must decide what the appropriate practice will be for your own application. These are recommendations that are generally useful:

Use trigger procedures sparingly, for truly global and non-changing integrity checks. Resist the temptation to write real business logic into a trigger procedure. Basic integrity constraints are themselves a part of your business logic, of course, but you can think of business logic in this sense as being rules that are complex, subject to change, different for different customers or user groups, or otherwise difficult to pin down precisely.
Always remember that trigger procedures execute on the server-side of a distributed application. Never put messages into a trigger procedure, for example, because they are not seen on the client. A trigger procedure should never have any user interface or contain any statements that could possibly block the flow of the application, such as a statement that requests a value.
Remember that trigger procedures operate in the background of an application. That is, you don’t see the trigger code itself when you are looking through the procedures that make up your application, so it is important that they not have surprising or strange effects. If a trigger verifies a Customer Number in an Order record in a consistent way, developers come to see this as a welcome check and understand why it is there and what it is doing. If you put complex or obscure code into a trigger procedure, you might confuse developers who cannot understand why the application procedures they are coding or looking at are executing in a strange way.
Return errors in a standard way from all your triggers. If a trigger procedure does an integrity check, it must be able to reject the record update that fired it. Without being able to display a message, your procedure must generate the error in a way that application code can deal with it consistently. One method is to RETURN ERROR with a message that becomes the RETURN-VALUE of the trigger and code your application to be prepared to handle errors of this kind, by taking the RETURN-VALUE and turning it into a standard message box on the client, for example.
Write your applications so that errors from triggers are as unlikely as possible. You should use integrity procedures as a last defense for your application to make sure that casually written procedures don’t compromise your database. The heart of your application logic should enforce all integrity at a level that is visible to the application. Where appropriate, you can take specific actions when errors occur, and when updates change other database values that the user might need to see. For example, the user interface for your Order Entry screen probably should present the user with a lookup list of some sort to choose a Customer from, where the Customer Name or other identifying information verifies that the Customer Number is correct. If you do this, then it is very unlikely that an invalid CustNum will find its way back to an actual update to be detected and rejected by a trigger procedure.

Database events

There are five database events you can associate with a trigger procedure:

CREATE — Executes when Progress executes a CREATE or INSERT statement for a database table, after the new database record is created. You can use the procedure to assign initial values to some of the table’s fields, such as a unique key value.
DELETE — Executes when Progress executes a DELETE statement for a database table, before the record is actually deleted. You can use the procedure to check for other related records that would prevent deletion of the current one, to delete those related records if you wish, or to adjust values in other records in other tables to reflect the delete.
WRITE — Executes when Progress changes the contents of a database record. More specifically, it occurs when a record is released, normally at the end of a transaction block, or when it is validated. This book recommends against using the field validation expressions that the Data Dictionary allows you to define because these have the effect of mixing validation logic with user interface procedures. The WRITE trigger happens in conjunction with those validations, if they exist, when the record is released or you execute an explicit VALIDATE statement. The WRITE trigger can replace those kinds of validations without combining validation with the UI.
ASSIGN — Monitors a single field rather than an entire database record, so you can use it to write field-level checks. An ASSIGN trigger executes at the end of a statement that assigns a new value to the field, after any necessary re-indexing. If a single ASSIGN statement (or UPDATE statement, but you know not to use that anymore) contains several field assignments, Progress fires all applicable ASSIGN triggers at the end of the statement. If any trigger fails, Progress undoes all the assignments in the statement.
FIND — Executes when Progress reads a record using a FIND or GET statement or a FOR EACH loop. A FIND trigger on a record executes only if the record first satisfies the full search condition on the table, as specified in the WHERE clause. FIND triggers do not fire in response to the CAN-FIND function. If a FIND trigger fails, Progress behaves as though the record has not met the search criteria. If the FIND is within a FOR EACH block, Progress simply proceeds to the next iteration of the block. Generally, you should not use FIND triggers. They are expensive—consider that you are executing a compiled procedure for every single record read from that table anywhere in your application. Also, they are typically used for security to provide a base level of filtering of records that the user should never see. For various reasons, including the fact that a CAN-FIND function does not execute the FIND trigger, this security mechanism is not terribly reliable. You are better off building a general-purpose filtering mechanism into your application architecture in a way that is appropriate for your application, rather than relying on the FIND trigger to enforce it.

There are also trigger events to support replication of data, so that any change to a database can be copied to another database. These REPLICATION-CREATE, REPLICATION-DELETE, and REPLICATION-WRITE events are described in OpenEdge Development: Progress 4GL Reference.

Trigger procedure headers

A trigger procedure must be an external 4GL procedure. That is, it must be a procedure file of its own, not an internal procedure within some larger procedure file. It can contain just about any 4GL code. It is identified by a special header statement at the top of the procedure file.

CREATE, DELETE, and FIND headers

The header statement for a CREATE, DELETE, or FIND procedure has this syntax:

TRIGGER PROCEDURE FOR { FIND | CREATE | DELETE } OF table-name.

This statement effectively defines a buffer automatically with the same name as the table, scoped to the trigger procedure.

WRITE header

The header statement for a WRITE trigger has this syntax:

TRIGGER PROCEDURE FOR WRITE OF table-name   [ NEW [ BUFFER ] new-buffer-name ]   [ OLD [ BUFFER ] old-buffer-name ].

When executing a WRITE trigger, Progress makes two record buffers available to the trigger procedure. The NEW buffer contains the modified record that is being validated. The OLD buffer contains the most recent version of the record before the latest set of changes was made. This is the template record that holds initial values for a table if it is a newly-created record, the record from the database if the record has not been validated or the most recently validated record if it has been validated. The default for the new buffer is the automatically-created buffer named for the table itself, which makes the NEW phrase optional. If you wish to compare the new buffer to the old, you must use the OLD phrase to give the old one a name. The BUFFER keyword is just optional syntactic filler.

You can make changes to the NEW buffer, but the OLD buffer is read-only.

You can determine whether the record being written is newly created using the Progress NEW function, which returns true if Progress has not written the record to the database before, and false otherwise:

NEW table-name

For example:

IF NEW Customer THEN . . .

ASSIGN header

The header statement for an ASSIGN trigger has this syntax:

TRIGGER PROCEDURE FOR ASSIGN   { OF table.field }     | NEW [VALUE] new-field { AS data-type | LIKE other-field> }     [ OLD [VALUE] old-field { AS data-type | LIKE other-field2 } ] .

If you use the OF form, the expression table.field identifies the field, but you can in fact refer to any field in the record where the field has been changed.

If you need to compare the field value before and after it was changed, you must use the NEW and OLD phrases to give those versions of the field names. If you do this, you cannot refer to the rest of the record buffer. You can change the NEW field, and this changes the field value in the record, but changing the OLD field value has no effect. The VALUE keyword here is just optional syntactic filler.

Accessing and defining triggers

You can create a trigger procedure in the Procedure Editor, but you attach it to your database in the Data Dictionary, which provides an editor designed to let you code your triggers there as well.

To access triggers:

From the AppBuilder menu, select Tools Data Dictionary and then select a database table such as OrderLine.
Choose the Triggers button in the Table Properties dialog box. The Table Triggers dialog box appears:



To see the WRITE trigger that calculated the Extended Price for the OrderLine when it changed, select WRITE from the Event drop-down list:

When you create a new trigger procedure in the Data Dictionary, it supplies the header statement for you. Then you simply write the rest of the code for the procedure. You must give the procedure a name and specify a pathname either in the Procedure field or by selecting the Files button. It is this name that the Data Dictionary associates with the procedure so that Progress executes it at the right times. As you can see, the trigger procedures for the Sports2000 database are located in the directory sports2000trgs under the install directory for OpenEdge. The write trigger for the OrderLine table is called wrordl.p. You can use any naming convention you want for your trigger procedures.

You can choose the Help button to access descriptions of all the other buttons here. This section mentions just the two Options that are shown as toggle boxes in the corner of the dialog box.

A trigger procedure provides a certain measure of security that a validation check, or an effect elsewhere in the database, occurs reliably whenever a certain type of update occurs. If you wish to protect yourself against a trigger procedure being replaced by another procedure that doesn’t do the same job, you can check on the Check CRC toggle box. If this option is on, then Progress stores in the metaschema, along with the trigger procedure name, a unique Cyclic Redundancy Check (CRC) identifier for the compiled version of the trigger procedure. Progress raises an error if the r-code file it encounters at run time doesn’t match or if there is no compiled version of the procedure.

You can check on the other toggle box, Overridable, if you want to let the trigger procedure be overridable by a trigger local to a specific application procedure, called a session trigger. Session triggers are discussed briefly in the "Session triggers" . Session triggers allow you to provide the effects of a trigger but without making it global to the entire application. Among other things, they let you override a trigger procedure with behavior more appropriate to a particular application module. If you don’t check on the Overridable toggle box, then Progress raises an error if a session trigger executes that tries to override the behavior of this trigger procedure.

Having said all this, look at the statement in wrordl.p that calculates the Extended Price:

ExtendedPrice = Price * Qty * (1 - (Discount / 100)).

Is this a good use for a trigger? Probably not, because it definitely violates the guideline that trigger procedures shouldn’t contain real business logic. In any real application, this kind of price calculation is complex and variable, depending on any number of factors. It’s probably better to provide access to the price calculation algorithm in the application module that controls OrderLine maintenance and to make sure that your application is put together in such a way that the code is always executed when it needs to be. Burying the code in a trigger is not a good thing. Generally, the Sports2000 trigger procedures can serve as examples of how to write triggers, but are often not good examples. This is partly because the database is simplified in ways that are not always realistic and, partly because many of these example procedures predate the architecture for distributed applications and other features that have changed the way you build applications. (Many contain MESSAGE statements, for example, which is definitely a bad idea.)

Using database sequences in CREATE triggers

This section describes database sequences in CREATE triggers.

To see one of the basic uses for triggers, look at an example trigger procedure:

Cancel out of the Table Triggers dialog box and select the Order table.
Choose the Triggers button in its Table Properties dialog box. The Table Triggers dialog box appears again:



To assign each Order a unique number, the procedure uses a Database Sequence that stores the latest value assigned to an Order.
Define sequences in the Data Dictionary by choosing the Sequences button in the Data Dictionary main window:

The online help tells you more about how to create sequences in your own database. Basically, each sequence is an Integer value maintained for you in the database. When you define it, you simply give the sequence a name, a starting value, a maximum value, and a value to increment by. Then, each time Progress encounters the NEXT-VALUE function in your application, it increments the sequence and returns the new value, as in the example from this trigger:

ASSIGN order.ordernum = NEXT-VALUE(NextOrdNum)

You can also use the CURRENT-VALUE function to access the current sequence value without incrementing it. Also, there is a CURRENT-VALUE statement that allows you to assign a new value to the sequence:

CURRENT-VALUE ( sequence-name ) = integer-expression.

You should use the CURRENT-VALUE statement only to reset a sequence in a database that is not being actively used. You should never put such a statement in your application code to assign individual sequence values, as this is not reliable in a multi-user environment.

Both NEXT-VALUE and CURRENT-VALUE allow you to specify a logical database name as an optional second argument if the sequence name might not be unique among all your connected databases.

Assigning a unique Integer key to a new record is the most common use of CREATE triggers.

You can also use a CREATE trigger to assign other initial values that need to be expressions that can’t be represented in the Data Dictionary when you define fields. This trigger assigns the value of the built-in function TODAY to the OrderDate, and TODAY + 14 as the default Promise Date:

order.orderdate = TODAY order.promisedate = TODAY + 14

Session triggers

In addition to defining schema trigger procedures that are always executed when an operation occurs on a table, you can also define trigger blocks within your application that act on these events, much as you can define triggers for user interface events. These triggers are not of great utility, but there may be circumstances where you need the same block of code to execute regardless of which of a number of different update or find statements against a table are executed in a portion of your application.

A session trigger is in scope while the procedure that defines it is running. The code in the trigger executes in the context of the procedure that defines it, regardless of where the event occurs that fires the trigger. Therefore, it can access local variables and other procedure objects not available to the procedure where the event occurs.

The syntax for session triggers is modeled on the syntax for user interface events:

ON event OF object [ reference-clause ] [ OVERRIDE ]    { trigger-clock | REVERT }

The event can be CREATE, WRITE, DELETE, FIND, or ASSIGN, as for schema triggers.

The object is a database table name in the case of CREATE, DELETE, FIND, and WRITE triggers, or a database field qualified by its table name for the ASSIGN trigger.

The reference-clause applies only to the WRITE and ASSIGN triggers. For the WRITE trigger it is:

[ NEW [BUFFER] new-buffer-name ] [ OLD [BUFFER] old-buffer-name ]

For the ASSIGN trigger it is:

OLD [VALUE] old-value-name [ COLUMN-LABEL label | FORMAT format |                               INITIAL constant | LABEL string | NO-UNDO ]

If you include the OVERRIDE option, then this trigger block executes in place of any schema trigger for the same event and object. This is allowed only if you checked on the Overridable toggle box when you defined the schema trigger in the Data Dictionary. Otherwise, an error results when the trigger block is executed.

The trigger-block is a single statement or DO END block just as for user interface triggers. The trigger block executes as would a call to an internal procedure in the same place in the code.

If Progress encounters an additional session trigger for an event and object when a trigger is still in scope for that combination, the new one replaces the old one for the duration of the new trigger’s scope. Alternatively, if you use the REVERT option in a session trigger block, the current session trigger for the event and object is cancelled and the previously executed session trigger definition is reactivated. If there is no other session trigger on the stack, then the session trigger is deactivated altogether.

General trigger considerations

In general, a schema trigger procedure or session trigger executes within the larger context of the procedure containing the statement that causes the trigger to fire, just as if the procedure or block of code had been run following the triggering statement. Keep the following considerations in mind about how triggers operate:

Progress does not allow database triggers on events for metaschema tables and fields (which have an initial underscore in their names and a negative internal file number in the schema).
You cannot delete a record in a buffer passed to a database trigger or change the current record in the buffer with a statement such as FIND NEXT or FIND PREV.
An action within one trigger procedure can execute another trigger procedure. For example, if a trigger assigns a value to a field and you have also defined an ASSIGN trigger for that field, the ASSIGN trigger executes. You must take care that this does not result in either unwanted conflicts between the actions of the triggers or a possible loop.
By their nature, triggers are executed within transactions (except possibly for a FIND trigger). Whatever action is encoded in the trigger becomes part of the larger transaction.
For all blocks in a database trigger, the default ON ERROR handling is ON ERROR UNDO, RETURN ERROR, rather than the usual Progress default of ON ERROR UNDO, RETRY. You learn more about the ON ERROR phrase in the next chapter.
You can store collections of precompiled Progress procedures in a single file called a procedure library. If you collect together your application’s schema triggers into a procedure library, you can use the –trig startup parameter to identify either the name of the procedure library for triggers or the operating system directory where they reside. See OpenEdge Deployment: Startup Command and Parameter Reference for more information.
When you dump and load database records, you might want to disable the schema triggers of the database, both to avoid the overhead of the triggers and to deal with the likely possibility that integrity constraints enforced by the triggers might not be satisfied until the database load is complete. See OpenEdge Development: Basic Database Tools for more information on disabling triggers.
For information on how SQL access to your database interacts with 4GL schema trigger procedures, see OpenEdge Data Management: SQL Development.

In the following chapter about transactions, you’ll learn how Progress uses locks to control multi-user access to data, and what the scope of those locks is relative to the transactions they’re used in.