The SYS_NEW parameter is used in a WHERE clause within, and only within, the context of a Trigger.
This virtual pointer is available to INSERT and UPDATE triggers. It allows code to access the contents of the row as it will be after the INSERT or UPDATE 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_NEW, and whether or not they are updatable:
•BEFORE INSERT: Updatable
•AFTER INSERT: Read only
•BEFORE UPDATE: Updatable
•AFTER 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 Insert Trigger and is being used to increment the count of how many items have been used.
SELECT ProductType INTO vPType INDIC v1 +
FROM SalesDetails +
WHERE CURRENT OF SYS_NEW
UPDATE ProductCount +
SET ProductCount = (ProductCount + 1) +
WHERE ProductType = .vPType
