I'm new, very new to DB2, so please excuse if this seems like an ignorant question.

I'm trying to create a trigger that updates after an insert. It looks like this:

CREATE OR REPLACE TRIGGER IDENTITY_LOCATOR AFTER insert on IDENTITY_COLUMNS referencing NEW AS N FOR EACH ROW UPDATE IDENTITY_COLUMN SET SEQUENCE_NAME = (SELECT SEQNAME FROM syscat.sequences SN,SYSCAT.COLIDENTATTRIBUTES CN WHERE CN.SEQID = SN.SEQID AND CN.TABNAME = N.TABLE_NAME) WHERE IDENTITY_COLUMNS.TABLE_NAME = N.TABLE_NAME

Now, when I manually insert from the command line the trigger works fine and updates the field. Also, If I set the trigger to UPDATE without the SELECT the trigger works too:

CREATE OR REPLACE TRIGGER IDENTITY_LOCATOR AFTER insert on IDENTITY_COLUMN referencing NEW AS N for each row UPDATE IDENTITY_COLUMN SET SEQUENCE_NAME = 'RANDOMSTRING' WHERE IDENTITY_COLUMN.TABLE_NAME = N.TABLE_NAME

Am I missing something completely obvious? I've tried using begin/begin atomic and if inserting then in various ways. Nothing seems to work. So I'm breaking down and asking for help!

Thanks in advance.

After scanning the site a little bit I found a post where someone resolved a similar problem with something that looked like:

CREATE OR REPLACE TRIGGER IDENTITY_LOCATOR AFTER insert on IDENTITY_COLUMN referencing NEW AS N for each row begin atomic DECLARE seq_placeholder varchar(255); set seq_placeholder = (SELECT SEQNAME FROM syscat.sequences SN,SYSCAT.COLIDENTATTRIBUTES CN WHERE CN.SEQID = SN.SEQID AND CN.TABNAME = N.TABLE_NAME); UPDATE IDENTITY_COLUMN SET SEQUENCE_NAME = seq_placeholder WHERE IDENTITY_COLUMN.TABLE_NAME = N.TABLE_NAME;end

Do I need to somehow quote the seq_placeholder value even though its declared as varchar?

(1) What are your DB2 version/release and platform OS?

(2) What are your questions/issues?

(2-1) Did you received error message(s)?
If so, is it at compile time or execution time?
Copy and paste the messages including text and codes.

(2-2) If result was different from your expectations.
What are your expected result and actually received result?


First trigger: AFTER insert on IDENTITY_COLUMNS
Second trigger: AFTER insert on IDENTITY_COLUMN
Third trigger: AFTER insert on IDENTITY_COLUMN

Copy and paste your code exactly.
If you want to hide your table names and/or column names, or some other objects,
change your code carefully, not to loose consistency.

I remembered that I replied similar things to another person somedays ago.
See 7th post(my fourth post) in this thread
SQL delete function by RRN


And, I also found those were written in
Must Read before posting

You only need to quote it if you reference a literal value, not if you select the value from a another table.

When you run a script to create a Trigger or SP, it may (like yours) contain some intermediate command delimeters (. But there must only be one command delimeters in the create/repace command (at the end). I am not sure if this is your problem because you did not provide the error message details you are getting.

Some client tools take care of this automatically, but you may have change your statement delimeter to @ and use it after the last END, so DB2 knows when your create/replace ends and when a line in your trigger ends.

If using the command line editor, you can do this:
db2 -td@ -vf script-name
and put a @ after the last END statement in your create/replace command.

Ok, the basics - I'm running DB2 v9.7.4 Express-C on linux.

Table being inserted on:

~$ db2 "describe table identity_column"

Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
TABLE_NAME SYSIBM VARCHAR 50 0 No
COLUMN_NAME SYSIBM VARCHAR 10 0 Yes
SEQUENCE_NAME SYSIBM VARCHAR 20 0 Yes

Application inserts TABLE_NAME/COLUMN_NAME and trigger is supposed to update SEQUENCE_NAME.

After an and insert on the table by the application this is what I see (SEQUENCE_NAME is '-' for all fields):

~$ db2 "select * from IDENTITY_COLUMN WHERE TABLE_NAME = 'BLOCKED_IPS'"

TABLE_NAME COLUMN_NAME SEQUENCE_NAME
-------------------------------------------------- ----------- --------------------
BLOCKED_IPS IID -

However, if I remove this record and insert manually from the command line

~$ db2 "DELETE FROM IDENTITY_COLUMN WHERE TABLE_NAME = 'BLOCKED_IPS'"
DB20000I The SQL command completed successfully.

~$ db2 "INSERT INTO IDENTITY_COLUMN (TABLE_NAME,COLUMN_NAME) VALUES ('BLOCKED_IPS','IID')"
DB20000I The SQL command completed successfully.

~$ db2 "select * from IDENTITY_COLUMN WHERE TABLE_NAME = 'BLOCKED_IPS'"

TABLE_NAME COLUMN_NAME SEQUENCE_NAME
-------------------------------------------------- ----------- --------------------
BLOCKED_IPS IID SQL111023151930150

Here's some more info:

~$ db2level
DB21085I Instance "demo" uses "32" bits and DB2 code release "SQL09074" with
level identifier "08050107".
Informational tokens are "DB2 v9.7.0.4", "s110330", "IP23242", and Fix Pack
"4".
Product is installed at "/opt/ibm/db2/V9.7".

~$ db2licm -l
Product name: "DB2 Express-C"
License type: "Unwarranted"
Expiry date: "Permanent"
Product identifier: "db2expc"
Version information: "9.7"
Max number of CPUs: "2"
Max amount of memory (GB): "2"

Like I said, I'm pretty new, so I'm not exactly sure where to look for trigger execution errors in logs.

Also, if I create the trigger and set the update to a static value instead of a SELECT result the trigger does update the SEQUENCE_NAME field with that value.

Sorry, I did not understand your original problem when I replied above. I am still not sure that I understand your problem and the following comment may be irrelevant to your issue; however, you should be aware that values in the syscat.sequences are not always current as sequences can be cached.

Thanks so much Marcus, you are 100% correct. As a test I used this trigger:

CREATE OR REPLACE TRIGGER IDENTITY_LOCATOR AFTER insert on IDENTITY_COLUMN referencing NEW AS N for each row begin atomic DECLARE seq_placeholder varchar(255); set seq_placeholder = (SELECT TABLE_NAME FROM IDENTITY_COLUMN WHERE TABLE_NAME = N.TABLE_NAME); UPDATE IDENTITY_COLUMN SET SEQUENCE_NAME = seq_placeholder WHERE IDENTITY_COLUMN.TABLE_NAME = N.TABLE_NAME;end

Not querying syscat.sequences at all but still selecting and updating values. Sure enough, the trigger worked properly and updated all fields! Good to know that the issue wasn't with how I wrote the query itself.

I guess a few additional details would help here. The trigger will mostly be used only during install of an application and occasionally when a new table is created by the application. As a fall back, I can always update the field programmatically but figured the off load would be nice if it were possible.

Do you have any other suggestions on how to overcome this before I fall back to doing it programmatically?

Thanks again!