sql - Create After Trigger works but Create Before doesn't

Question

if I try to create a Trigger with an After as it follows, it is working fine.

CREATE TRIGGER UDO.TG_TEST AFTER UPDATE
    OF LAST_BACKUP ON
    UDO.BACKUP FOR EACH ROW
INSERT
    INTO
    UDO.BACKUP_HIST(BACKUP_ID,
    INST_NAME,
    INST_ID,
    DB_NAME,
    DB_ID,
    SERVER,
    RESTORE_TS,
    LAST_BACKUP,
    BACKUP_TYP,
    BACKUP_DAUER,
    BACKUP_ORT)
SELECT
    BACKUP_ID,
    INST_NAME,
    INST_ID,
    DB_NAME,
    DB_ID,
    SERVER,
    RESTORE_TS,
    LAST_BACKUP,
    BACKUP_TYP,
    BACKUP_DAUER,
    BACKUP_ORT
FROM UDO.BACKUP

But if I do that exact same Create but with a BEFORE or NO CASCADE BEFORE:

CREATE TRIGGER UDO.TG_TEST NO CASCADE BEFORE UPDATE
    OF LAST_BACKUP ON
    UDO.BACKUP FOR EACH ROW
INSERT
    INTO
    UDO.BACKUP_HIST(BACKUP_ID,
    INST_NAME,
    INST_ID,
    DB_NAME,
    DB_ID,
    SERVER,
    RESTORE_TS,
    LAST_BACKUP,
    BACKUP_TYP,
    BACKUP_DAUER,
    BACKUP_ORT)
SELECT
    BACKUP_ID,
    INST_NAME,
    INST_ID,
    DB_NAME,
    DB_ID,
    SERVER,
    RESTORE_TS,
    LAST_BACKUP,
    BACKUP_TYP,
    BACKUP_DAUER,
    BACKUP_ORT
FROM UDO.BACKUP

I get the following error:

SQL-Fehler [42987]: The trigger "UDO.TG_TEST" is defined with an unsupported triggered SQL statement.. SQLCODE=-797, SQLSTATE=42987, DRIVER=3.72.44

I have checked the syntax according to IBM DB2 documentation and I think it is correct.

Do I overlook something?

edit:

Platform: LUW, DB2 Server Version: 11.1.4.4

My actual goal to achieve is to archive the data from UDO.BACKUP into UDO.BACKUP_HIST before UDO.BACKUP is updated.

question from:https://stackoverflow.com/questions/65900977/create-after-trigger-works-but-create-before-doesnt

Answer 1

Yes, you overlooked the following at the link you provided:

NO CASCADE BEFORE
Specifies that the trigger is a before trigger. Db2 executes the triggered action before it applies any changes caused by an insert, delete, or update operation on the subject table. It also specifies that the triggered action does not activate other triggers because the triggered action of a before trigger cannot contain any updates.
NO CASCADE BEFORE must not be specified when view-name is also specified. FOR EACH ROW must be specified for a BEFORE trigger.

"Updates" means INSERT / UPDATE / DELETE / MERGE statements.