DB2 UPDATE TRIGGER

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;

1.

I notice in your trigger, your code says:

INSERT INTO LYNX.TRANS_AUDIT
        (TA_ID, TA_KEY_VALUE, TA_TABLE_CHANGED, TA_FIELD_CHANGED, ...)
      VALUES 
        (TA_ID, N.UNIT_ID , 'PUNIT', 'FLEET_ID',  ...);

But shouldn't the VALUES clause contain vTA_ID instead?

INSERT INTO LYNX.TRANS_AUDIT
        (TA_ID, TA_KEY_VALUE, TA_TABLE_CHANGED, TA_FIELD_CHANGED, ...)
      VALUES 
        (vTA_ID, N.UNIT_ID , 'PUNIT', 'FLEET_ID', ...);

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.

CREATE SEQUENCE TRANS_AUDIT_SEQ AS INTEGER;

Then in your trigger,

INSERT INTO LYNX.TRANS_AUDIT
        (TA_ID, TA_KEY_VALUE, TA_TABLE_CHANGED, TA_FIELD_CHANGED, ...)
      VALUES 
        (NEXT VALUE FOR TRANS_AUDIT_SEQ, N.UNIT_ID , 'PUNIT', 'FLEET_ID', ...);

3.

But what may suit you best, may be to define your ID column AS IDENTITY

CREATE TABLE LYNX.TRANS_AUDIT (
 TA_ID INTEGER  GENERATED BY DEFAULT AS IDENTITY,
    ...
 PRIMARY KEY (TA_ID)
);

Then in your trigger, you do not need to specify the value, since it will be generated for you:

INSERT INTO LYNX.TRANS_AUDIT
        (TA_KEY_VALUE, TA_TABLE_CHANGED, TA_FIELD_CHANGED, ...)
      VALUES 
        (N.UNIT_ID , 'PUNIT', 'FLEET_ID',  ...);

This is most likely the best of the 3 solutions for you, unless there are other factors not mentioned.

StackExchange Database Administrators Q#37243, answer score: 3