OpenEdge SQL triggers are written in Java. These triggers automatically run the user-defined logic when DML events like INSERT, UPDATE, or DELETE occur on a table or its fields.

Given that triggers can access the data in tables both internally and externally, they may potentially enable an unauthorized user to access sensitive data. However, DDM masks the data and prevents unauthorized access by users who lack the necessary DDM privileges.

Triggers also enable accessing the data of the affected rows in the form of OLD or NEW database objects, either before or after the DML action takes place. The SQL engine creates the OLD and NEW objects internally during a DML event on the table. The OLD and NEW objects refer to the masked values of the DDM-configured fields if you do not have unmasking privileges over those fields.

Example

The following example illustrates the behavior of BEFORE UPDATE trigger event when trigger logic tries to access a DDM-configured field. 
--Create a table to store the value of the discount field 
CREATE TABLE pub.dumpCustInfoBfrUpd(discount int);

-- Before update trigger
CREATE TRIGGER custTrigBfrUpd 
BEFORE 
UPDATE OF Discount 
ON pub.customer 
REFERENCING OLDROW
FOR EACH ROW 
BEGIN 
SQLIStatement insert_t1 = new SQLIStatement ("INSERT INTO pub.dumpCustInfoBfrUpd VALUES (?)");
INTEGER oldValue = (Integer)OLDROW.getValue(15, INTEGER);
System.out.println("OldValue " + oldValue);
insert_t1.setParam(1, oldValue);
insert_t1.execute();
END;

-- Fire an UPDATE statement on the “Name” field which is not DDM-configured. 
-- custTrigBfrUpd should be executed before updating each qualified row.
   
UPDATE pub.customer SET Name=“John Schmidt” WHERE Name = “Lift Tours”;

-- The pub.dumpCustInfoBfrUpd table contains data before updating the row. 
-- It should show the masked values of the DDM-configured “Discount” field for 
-- unauthorized users.

SELECT * FROM pub.dumpCustInfoBfrUpd;
In this example, Discount is a DDM-configured field. The custTrigBfrUpd trigger creates an instance of the OLDROW object using the REFERENCING OLDROW clause. The OLDROW object references the value of the affected row in the pub.customer table prior to the update operation on the Name field of this table. When you run the UPDATE statement without unmasking privileges on the Discount field in the pub.customer table, the BEFORE UPDATE trigger event causes the getValue method to:
  1. Retrieve the masked value of the discount field (column 15) from the row affected by the trigger event.

  2. Store this masked value in the procedure variable named oldValue.

The SQLIStatement class issues the INSERT statement, which inserts the masked value from oldValue into the pub.dumpCustInfoBfrUpd table. As a result, masked data is displayed when you retrieve records from the pub.dumpCustInfoBfrUpd table.

However, with unmasking privileges, the trigger event inserts the unmasked and old value of the Discount field for the affected row into the pub.dumpCustInfoBfrUpd table.
Note: The UPDATE statement can update a DDM-configured field only when you have unmasking privileges over that field; otherwise, it fails with an error.