IBM DB2 trigger help needed

Question

I have the following stored procedure:

CREATE PROCEDURE UPDATE_MODIFICATION_DATE (IN TBLNAME VARCHAR(30), IN ROWID INTEGER)
P1: BEGIN
    DECLARE store VARCHAR(1000);
    SET store = 'UPDATE ' || TBLNAME || ' SET modification_date=CURRENT TIMESTAMP WHERE     id='||ROWID;
    PREPARE command FROM store;
    EXECUTE command;
END P1

It gets a name of a table, an id of a row, and update the modification_date column to the current timestamp.

I don't know how to write a trigger what calls this procedure every time after a successful update or insert of a table. (So what i want to accomplish is to store when a row was last touched).

There are plenty of trigger examples in the manuals. And I think updating modification_date in the trigger itself will perform much better than invoking dynamic SQL in a separate stored procedure. — mustaccio
– mustaccio, Commented Jan 29, 2014 at 12:58
Table names can be longer than 30 characters (I believe the "easy" limit on the iSeries is 32, but even there it can be longer). To help avoid SQL Injection, you may want to verify against the sysinfo tables. How does this interact with multiple schemas - does it default to the calling trigger's schema, the user's schema, the compiled procedure's schema? Changing the table like @Charles suggests is probably the best long-term solution - would that work? Otherwise, if you're defining a trigger, I'm with @mustaccio... (this is the reason for before update...) — Clockwork-Muse
– Clockwork-Muse, Commented Jan 30, 2014 at 14:25

Charles · Accepted Answer · 2014-01-30 03:37:38Z

1

Why not just define modification as a ROW CHANGE TIMESTAMP? Then db2 will update it automatically.

answered Jan 30, 2014 at 3:37

Charles

24.2k3 gold badges23 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Clockwork-Muse Over a year ago

Certainly, I see this as the best long-term option, for a number of reasons. Maybe he's not allowed to ALTER the tables, or his db doesn't support it (old version)? +1, although perhaps you should also include a sample ALTER TABLE script.

Collectives™ on Stack Overflow

IBM DB2 trigger help needed

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related