By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE LOG_TABLE
(
ID INT NOT NULL GENERATED BY DEFAULT AS IDENTITY
, ERROR_TEXT VARCHAR(1000) NOT NULL
, TS TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP
, OBJ XML NOT NULL
)
CREATE TABLE MY_TABLE
(
ID INT NOT NULL GENERATED BY DEFAULT AS IDENTITY
, NAME VARCHAR(10) NOT NULL
)
CREATE OR REPLACE PROCEDURE LOG_TABLE
(
P_ERROR_TEXT VARCHAR(1000)
, P_OBJ BLOB(10K)
)
AUTONOMOUS
BEGIN
INSERT INTO LOG_TABLE (ERROR_TEXT, OBJ) VALUES (P_ERROR_TEXT, XMLPARSE(DOCUMENT P_OBJ));
END
CREATE OR REPLACE TRIGGER MY_TABLE_BUR
NO CASCADE BEFORE UPDATE OF NAME ON MY_TABLE
REFERENCING NEW AS OBJ
FOR EACH ROW MODE DB2SQL
BEGIN
DECLARE ERROR_TEXT VARCHAR(1000);
IF
OBJ.NAME = 'Z'
THEN
--Insert into MY_SCHEMA.LOG_TABLE(....);
CALL LOG_TABLE('Name not allowed: ' || OBJ.NAME, XMLSERIALIZE(XMLROW(OBJ.ID AS "ID", OBJ.NAME AS "NAME") AS BLOB(10K)));
SET ERROR_TEXT = 'Name not allowed';
SIGNAL SQLSTATE '70001' SET MESSAGE_TEXT = ERROR_TEXT;
END IF;
END
INSERT INTO MY_TABLE(NAME) VALUES ('X')
UPDATE MY_TABLE SET NAME = 'Z'
[IBM][CLI Driver][DB2/LINUXX8664] SQL0438N Application raised error or warning with diagnostic text: "Name not allowed". SQLSTATE=70001 SQLCODE=-438
SELECT * FROM MY_TABLE
| ID | NAME |
|---|---|
| 1 | X |
SELECT * FROM LOG_TABLE
| ID | ERROR_TEXT | TS | OBJ |
|---|---|---|---|
| 1 | Name not allowed: Z | 2024-12-11 07:51:02.850763 | <?xml version="1.0" encoding="UTF-8" ?><row><ID>1</ID><NAME>Z</NAME></row> |