I am trying to create a trigger in a DB2
database that runs on the update of a column in one table, and then fills in another table with certain values.
For example, there is a power unit table with a FLEET_ID
column. Every time the FLEET_ID
is changed, I need to to create a new row in the TRANS_AUDIT
table.
The TRANS_AUDIT
schema is as follows:
CREATE TABLE LYNX.TRANS_AUDIT (
TA_ID INTEGER NOT NULL,
TA_KEY_VALUE VARCHAR(100),
TA_TABLE_CHANGED VARCHAR(40),
TA_FIELD_CHANGED VARCHAR(40),
TA_OLD_FIELD_VALUE VARCHAR(100),
TA_NEW_FIELD_VALUE VARCHAR(100),
TA_USER_WHO_CHANGED VARCHAR(128),
TA_DATE_CHANGED TIMESTAMP,
TA_COMMENT VARCHAR(40),
TA_OLD_FIELD_DOUBLE DOUBLE DEFAULT 0,
TA_NEW_FIELD_DOUBLE DOUBLE DEFAULT 0,
PRIMARY KEY (TA_ID)
);
Here is what I have so far, but I can't seem to get it to work, I am getting a "function sequence" error.
CREATE TRIGGER PU_UPD_FLEETID
AFTER UPDATE OF FLEET_ID ON PUNIT
REFERENCING OLD AS O NEW AS N
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
DECLARE
vTA_ID INTEGER;
IF(N.FLEET_ID <> O.FLEET_ID) THEN
SELECT MAX(TA_ID)+1 INTO vTA_ID; --generate a unique sequential id
INSERT INTO LYNX.TRANS_AUDIT
(TA_ID, TA_KEY_VALUE, TA_TABLE_CHANGED, TA_FIELD_CHANGED, TA_OLD_FIELD_VALUE,
TA_NEW_FIELD_VALUE, TA_USER_WHO_CHANGED, TA_DATE_CHANGED, TA_COMMENT,
TA_OLD_FIELD_DOUBLE, TA_NEW_FIELD_DOUBLE)
VALUES
(TA_ID, N.UNIT_ID , 'PUNIT', 'FLEET_ID', O.FLEET_ID, N.FLEET_ID , SESSION_USER
,CURRENT TIMESTAMP , '', '0' ,'0' );
END IF;
END;
Best Answer
1.
I notice in your trigger, your code says:
But shouldn't the VALUES clause contain
vTA_ID
instead?However, calculating vTA_ID with your query, may not be your most efficient option.
2.
You could create a SEQUENCE object, and let it always calculate new values.
Then in your trigger,
3.
But what may suit you best, may be to define your ID column AS IDENTITY
Then in your trigger, you do not need to specify the value, since it will be generated for you:
This is most likely the best of the 3 solutions for you, unless there are other factors not mentioned.