Question & Answer
Question
I'm getting a SQL0723 on my AFTER DELETE trigger - but the SQL0723 message isn't very helpful - It just says the SQL Trigger failed.
Cause
If the trigger event is DELETE, the value for every column in NEW ROW is the NULL value.
This will cause issues if the table the AFTER DELETE trigger has columns that do not allow a NULL value.
So for this SQL0723 message, the key is the SQLCode (-407 in this example)
SQL0407 - Null values are not allowed
Answer
You actually probably want to be using the OLD ROW syntax for DELETE SQL triggers as there isn't a 'NEW ROW' on a DELETE:
Instead of this:
CREATE TRIGGER -trigger name-
AFTER DELETE ON -table-
REFERENCING NEW ROW AS N
FOR EACH ROW MODE ...
INSERT INTO -log table- VALUES (N.COL1, ...)
You probably want this:
CREATE TRIGGER -trigger name-
AFTER DELETE ON -table-
REFERENCING OLD ROW AS O
FOR EACH ROW MODE ...
INSERT INTO -log table- VALUES (O.COL1, ...)
DB2 for i SQL Reference: (this is the IBM i 7.3 version but this information is the same)
https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/db2/rbafzhctrigger.htm
Related Information
Was this topic helpful?
Document Information
More support for:
IBM i
Software version:
Version Independent
Operating system(s):
IBM i
Document number:
687831
Modified date:
18 December 2019
UID
nas8N1022407