At 04:14 AM 6/26/2003 -0700, Dennis McGrath wrote:
I can immagine lots of scenarios where both a before trigger and an after trigger would be desirable. One might want to do before processing on some columns and after processing on others.
Anyone know for sure the real scoop on this?
Dennis,
R:BASE 6.5++ (Build:1.852xRT03) and higher supports BEFORE and AFTER TRIGGERS. The current triggers are all "BEFORE" triggers in that they run just before the actual INSERT, UPDATE, or DELETE and give you the ability to ABORT the action. The new triggers are "AFTER" triggers in that they run just after the action. Since the modified data has been "committed" you cannot abort the action in an AFTER trigger.
The new triggers can be created by the CREATE TABLE command or by the ALTER TABLE command. When you use the ALTER TABLE command you must define the insert triggers in the same command. The same goes 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. When you drop a trigger, you do not specify the "BEFORE" or "AFTER" trigger. The DROP of the insert trigger, for example, drops both parts of they are there.
LIST TRIGGERS and UNLOAD command has also been updated to include these changes.
New syntax:
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
A table can have both "BEFORE" and "AFTER" TRIGGERs, only one, or none.
Typical TRIGGER Usage:
BEFORE- data validation before the action (inventory checks, account limit checks)
AFTER- update of data dependent on primary keys, automated post transaction steps.
TRIGGERS use Stored Procedures
Procedures are stored in the database in the system table called SYS_PROCEDURES
Supporting system tables are SYS_PROC_COLS and SYS_PROC_MODS
Hope that helps!
If you need further details or examples of using BEFORE and AFTER TRIGGERS, feel free to ask on this Official List Server of R:BASE!
Enjoy and make sure to have fun!
Very Best R:egards,
Razzak.
