By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,018 Members | 901 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,018 IT Pros & Developers. It's quick & easy.

Triggers - can I have it fire only if certain columns actually change?

P: n/a
I can't find it anywhere in the manual, but I have a developer that
wants to know if we can code a trigger to capture the data that has
changed in a table only if certain columns have changed. It looks like
I can do it with the when clause and check the old vs new, but that
would get very ugly with a large table. Has anyone done something like
this?

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Yes, you can specify columns in the create trigger statement like this:
create trigger <trigger name> after update of col1, col2, col3 on <table>
........

Regards
Odd B Andersen
ErgoGroup AS
Oslo, Norway

"dataguy" <ba*********@progressive.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
I can't find it anywhere in the manual, but I have a developer that
wants to know if we can code a trigger to capture the data that has
changed in a table only if certain columns have changed. It looks like
I can do it with the when clause and check the old vs new, but that
would get very ugly with a large table. Has anyone done something like
this?

Nov 12 '05 #2

P: n/a
dataguy wrote:
I can't find it anywhere in the manual, but I have a developer that
wants to know if we can code a trigger to capture the data that has
changed in a table only if certain columns have changed. It looks like
I can do it with the when clause and check the old vs new, but that
would get very ugly with a large table. Has anyone done something like
this?

You have to comper ateh old and new values (make sure you handle NULLs,
too).
Note that you can have more than one trigger of a kind per table.
If teh code partitions well (it often does) you simply create a trigger
per column or per group of columns also exploiting the column clause for
an UPDATE trigger.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #3

P: n/a
RdR
We normally do this to pass on to MQ Series the row change based on the
column that changed so that MQ based on its rules can deliver the data to
the right message queue.

I do not know what platform you are in but you can do something to the
equivalent of a DB2 Instrumentation Facility Interface read on the logs, the
before Image will have a UB image designation in ne of the log information
and the after Image will have a UP and then in your trigger, call a stored
procedure that will compare the UB and the UP and then pass any differences
on the trigger variables any differences. Your trigger must be prepared to
handle multiple column changes because it is possible that there are more
than one changed field on a single row.

You are correct, it will get very ugly if you start comparing data pulled
from the table itself, so our approach was to get it from the DB2 logs. The
basic steps are:

1) Turn on Logging for the table you wil ldetect the column change.
2) Have an update trigger that will:
a) Call a program to do the DB2 IFI using an IFCID306 to read the
log for this particular table.
b) Read the UB and UP portion of the read result.
c) Compare every column and dump the differences in a flat file.
d) Get the after image from a flat file.
** Since we are forwarding the info to MQ, we proceeded with the following
steps bus based on what you need, you can proceed appropriate to what you
need to accomplish**
e) Format the information gathered in the correct XML format (in our
case it is XML).
f) Deposit this XML to the message queue.
g) Go back to the trigger and report that it is done, otherwise send
an error.
h) Once in the message queue, th erules we set in can forward the
xml document to the right queue or database based on the changed column
information.

If you do not want to write that program, you can use Q Replication and
doing the DB2 IFI will be handled by it, you will just have to write the
program detecting the differences on the rows of the before and after
images. The queue delivery I believe will also be handled by Q Replication.

Hope this helps.

RdR

"dataguy" <ba*********@progressive.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
I can't find it anywhere in the manual, but I have a developer that
wants to know if we can code a trigger to capture the data that has
changed in a table only if certain columns have changed. It looks like
I can do it with the when clause and check the old vs new, but that
would get very ugly with a large table. Has anyone done something like
this?

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.