Cache SQL and multiple-event triggers
Greetings,
I am working on the first of many triggers which will have identical code upon row insertion or update of a single column. According to the document I should be able to define a multiple-event trigger using Cache SQL/DDL.
Here is a link to the current CREATE TRIGGER documentation. Within the description section is the following paragraph:
A single-event trigger is triggered by a specified INSERT, DELETE, or UPDATE operation. A multiple-event trigger is defined to execute when any one of the specified events occurs on the specified table. You can define an INSERT/UPDATE, an UPDATE/DELETE, or an INSERT/UPDATE/DELETE multiple-event trigger.
I have no issue creating the single-event triggers, but I cannot work out the proper syntax to create a multiple-event trigger.
Here's a sample table:
CREATE TABLE TESTING.MULTI_EVENT_TRIGGERS ( %PUBLICROWID , FIELD_DATA VARCHAR(80) , FIELD_UPD_TS TIMESTAMP )
and a corresponding update trigger:
CREATE TRIGGER TESTING.MULTI_EVENT_TRIGGERS_DATA AFTER UPDATE OF FIELD_DATA ON TESTING.MULTI_EVENT_TRIGGERS REFERENCING NEW ROW AS NEWDATA BEGIN UPDATE TESTING.MULTI_EVENT_TRIGGERS SET FIELD_UPD_TS = CURRENT_TIMESTAMP WHERE ID = NEWDATA.ID; END
Here are the three various ways that I've tried to create the multiple-event trigger:
CREATE TRIGGER TESTING.MULTI_EVENT_TRIGGERS_DATA AFTER INSERT/UPDATE OF FIELD_DATA CREATE TRIGGER TESTING.MULTI_EVENT_TRIGGERS_DATA AFTER INSERT OR UPDATE OF FIELD_DATA CREATE TRIGGER TESTING.MULTI_EVENT_TRIGGERS_DATA AFTER INSERT UPDATE OF FIELD_DATA
The error message I receive is always the same:
[%msg: < ON expected, OR found^CREATE TRIGGER TESTING.MULTI_EVENT_TRIGGERS_DATA AFTER INSERT or>]Does anyone have insight into creating multiple event triggers? At this point I'm starting to think that the documentation is not correct and multiple-event triggers can only be created in ObjectScript, not in DDL. I can also accept that I am the source of the problem and this is a case of me not being able to see the forest through the trees and I'm just not using the syntax correctly (or reading the documentation correctly).
Comments
It seems that "CREATE TRIGGER" does not support INSERT/UPDATE, INSERT/UPDATE/DELETE, etc. It's either a bug or a feature. I believe that is still a bug. In any case you need to contact the WRC.