The update, delete, or insert event can be initiated through the UPDATE, DELETE, or INSERT R:BASE/SQL commands, or through an R:BASE form.
Typical Trigger Usage:
•BEFORE - data validation before the action (inventory checks, account limit checks)
•AFTER- update of data after the action (dependent on primary keys, automated post transaction steps)
Since a "BEFORE" Trigger runs a stored procedure before the row that triggered it is updated, inserted, or deleted, you can cancel the update, insert, or delete with the ABORT TRIGGER command in the stored procedure. Since the modified data has been "committed" with an "AFTER" trigger, you cannot abort the action in the stored procedure.
Also, you can verify the action being performed in an update trigger on the row by using a SELECT command with the WHERE CURRENT OF SYS_OLD/SYS_NEW syntax to check the row before/after the update.
Creating a Trigger
Triggers can be created using the Data Designer, or with the CREATE TABLE or ALTER TABLE commands. When you use the ALTER TABLE command you must define the insert triggers in the same command. The same applies for update and delete. Do not use one alter table command to add the "BEFORE" insert trigger and then another alter table to add the "AFTER" trigger. Do them both in the same command.
Removing a Trigger
When you drop a trigger with the ALTER TABLE command, you do not have to specify the "BEFORE" or "AFTER" trigger. The DROP of the insert trigger, for example, drops both parts if they exist.
Listing Defined Triggers
To LIST all the tables in the open database that have triggers and the triggers, use the following syntax:
LIST TRIGGER
To list triggers for a specified table, use the following syntax:
LIST TRIGGER tblname
Example:
ALTER TABLE TableName ADD TRIGGER INSERT ProcName
ALTER TABLE TableName ADD TRIGGER UPDATE ProcName
ALTER TABLE TableName ADD TRIGGER DELETE ProcName
ALTER TABLE TableName ADD TRIGGER INSERT AFTER ProcName
ALTER TABLE TableName ADD TRIGGER UPDATE AFTER ProcName
ALTER TABLE TableName ADD TRIGGER DELETE AFTER ProcName
See also:
For a sample database using triggers, please locate the "Stored Procedures, Triggers and After Triggers" sample located at http://www.razzak.com/sampleapplications/
