Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 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;

share|improve this question
2  
Hello azoorob, I'm sorry I can't help you (don't know DB2...), but could you perhaps edit your question with the exact error message (especially if there are error codes and/or line numbers)? Also tell us if this error is happening when you create/compile the trigger, or at runtime? –  Mat Mar 21 '13 at 16:36
add comment

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
add comment

If table LYNX.TRANS_AUDIT has a sequence attached to column TA_ID (constraint), then you need to get the next value from the sequencer instead of calculating it yourself.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.