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).
