triggersdb2

Correct syntax for insert or update trigger in db2

I’m trying to create a single trigger to check a date on insert or update in a table. I’ve seen In DB2, is it possible to have just a single trigger for both update and insert? which suggests that it can be done in modern versions, but all the relevant links appear to have gone stale.

From what I have been able to gather, something like this should work:

DROP TRIGGER trigger_check_customer_dob;
CREATE TRIGGER trigger_check_customer_dob
BEFORE INSERT OR UPDATE OF dob ON customers
REFERENCING NEW AS newrow
FOR EACH ROW
WHEN (newrow.dob > current_date - INTERVAL '22 years')
SIGNAL SQLSTATE '22007' ('Too young')
;

However, I get errors like:

SQL Error [42601]: An unexpected token "OR UPDATE OF" was found following "".
Expected tokens may include: "".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.33.31

Documentation at https://www.ibm.com/docs/en/db2/11.1?topic=statements-create-trigger#r0000931__title__4 , if it hasn’t gone stale, implies that the syntax follows the pattern, but I presume I’m missing something.

What is the correct syntax for this?

I’m running Db2 12.1.1.0 in a Docker image.

Solution

  • From the description of the CREATE TRIGGER statement: https://www.ibm.com/docs/en/db2/12.1?topic=statements-create-trigger:

    trigger-event
    Specifies that the triggered action associated with the trigger is to be executed whenever one of the events is applied to the subject table or subject view. Any combination of the events can be specified, but each event (INSERT, DELETE, and UPDATE) can only be specified once (SQLSTATE.42613). If multiple events are specified, the triggered action must be a compound SQL (compiled) statement (SQLSTATE 42601).

    This means, that the code of your triggered action must be included into the BEGIN ... END block.

    --#SET TERMINATOR @

CREATE TABLE customers(id int, dob date)@

CREATE TRIGGER trigger_check_customer_dob
BEFORE INSERT OR UPDATE OF dob ON customers
REFERENCING NEW AS newrow
FOR EACH ROW
WHEN (newrow.dob > current_date - 22 years)
BEGIN
  SIGNAL SQLSTATE '22007' SET MESSAGE_TEXT = 'Too young';
END@

INSERT INTO customers(id, dob) values (1, current date - 22 year)@

INSERT INTO customers(id, dob) values (2, current date - 21 year)@

[IBM][CLI Driver][DB2/LINUXX8664] SQL0438N  Application raised error or warning with diagnostic text: "Too young".  SQLSTATE=22007 SQLCODE=-438

UPDATE customers SET dob = current date - 20 year WHERE id = 1@

[IBM][CLI Driver][DB2/LINUXX8664] SQL0438N  Application raised error or warning with diagnostic text: "Too young".  SQLSTATE=22007 SQLCODE=-438

    fiddle link: https://dbfiddle.uk/18XFOpnz