The SYS_OLD parameter is used in a WHERE clause within, and only within, the context of a Trigger.
This virtual pointer is available to UPDATE and DELETE triggers. It allows code to access the contents of the row as it will be prior the UPDATE or DELETE action. Using this in the body of a WHERE clause allows code to act on the contents of that virtual row and NOT fire off another trigger.
The following is a list the trigger types available for use with SYS_OLD, and whether or not they are updatable:
•BEFORE DELETE: Read only
•BEFORE UPDATE: Read only
Note: The use of functions or expressions must be performed outside of the virtual pointer SELECT statement, after the variable values are captured.
Example
The following command is within the body of an Delete Trigger and is being used to automatically archive a message from an employee messaging table.
SELECT EmployeeID,MsgDate,MsgTime,MsgBody +
INTO vEID INDIC v1,vMsgDate INDIC v2, +
vMsgTime INDIC v3,vMsgBody INDIC v4 +
FROM EmpMessage WHERE CURRENT OF SYS_OLD
INSERT INTO ArchMessage +
(EmployeeID,MsgDate,MsgTime,MsgBody,DeletedOn) +
VALUES .vEID,.vMsgDate,.vMsgTime,.vMsgBody,.#DATE
