DB2 update trigger

Question

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. Everytime 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 FROM TRANS_AUDIT 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;

I'm not a DB2 expert, but `SELECT MAX(TA_ID)+1 INTO vTA_ID;` looks suspicious. What does this statement select from? — Bob Jarvis - Слава Україні, Mar 21 '13 at 15:16
I was trying to generate a unique id that is one greater than the current id in the TRANS_AUDIT table. What is the correct way to do this? — azoorob, Mar 21 '13 at 15:25
Ah, my mistake, I accidently copied it wrong, I forgot the from statement — azoorob, Mar 21 '13 at 15:26
It looks to me that there should be a BEGIN after the declaration of `vTA_ID`. — Bob Jarvis - Слава Україні, Mar 21 '13 at 17:37
As to "how to generate a unique ID", I suggest you investigate the use of sequences. Doing a SELECT MAX(some_field)+1 is inherently dangerous - consider what happens if you have two processes trying to generate a 'unique' number concurrently. Both could conceivably generate the same 'unique' number and insert it into the database. One process would be able to successfully insert its data, but the second would fail due to a unique key violation. This is what's called a "race condition", where the two processes are racing one another to see who 'wins'. Use sequences to avoid this. — Bob Jarvis - Слава Україні, Mar 21 '13 at 17:45
One other issue with SELECT MAX(some_field)+1 - it stands a fair chance of being a performance pig. The database has to, in some manner, figure out what MAX(some_field) is - which may or may not be possible to determine using an index. If it devolves into a full table scan performance will be OK when the table is new and empty, but will progressively get worse as time goes by. And this is in a *trigger*, which is supposed to execute quickly! Really - sequences: know 'em, use 'em, love 'em. Share and enjoy. — Bob Jarvis - Слава Україні, Mar 21 '13 at 17:54
`SELECT MAX() + 1` is _always_ suspicious - that kind of thing is open to concurrency issues (either it doesn't lock the table, and you get multiple rows with the same value, or it does lock the table, and becomes a serialized bottlenect). Personally, I'd just make that column an auto-generated one. That, or DB2 has [SEQUENCE](http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z9.doc.apsg%2Fsrc%2Ftpc%2Fdb2z_sequenceobject.htm)s that would work for this. I think part of your problem may be that you're referencing the column name during the insert, not the variable. — Clockwork-Muse, Mar 21 '13 at 18:06

score 1 · Accepted Answer · answered Mar 21 '13 at 17:40

1

In your INSERT statement, I believe the first entry in the VALUES should be vTA_ID, not TA_ID.

Share and enjoy.

answered Mar 21 '13 at 17:40

Bob Jarvis - Слава Україні

48,992
9
77
110

DB2 update trigger

1 Answers1