Question
· Jul 10, 2017

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

Discussion (1)0
Log in or sign up to continue