Written by

Question John Matson · 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).

Comments

Vitaliy Serdtsev · Jul 11, 2017

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.

0