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

How to create a trigger that detects updates to columns (includingNULLs)

P: n/a
Hi,

The following trigger does fire if the column has a NULL or if a NULL
is updated to the column. Any ides how to fix this?

AFTER UPDATE
ON <table name>
REFERENCING
NEW AS n
OLD AS o
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC

IF (o.RESOURCE_TYPE <n.RESOURCE_TYPE) THEN
<sql statement here>.
END IF;

IF (o.RESOURCE_STATUS <n.RESOURCE_STATUS) THEN
<sql statement here>.
END IF;
END#
Thanks !

Carlos
Dec 12 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
fu****@gmail.com wrote:
Hi,

The following trigger does
NOT
fire if the column has a NULL or if a NULL
is updated to the column. Any ides how to fix this?
IF (o.RESOURCE_TYPE <n.RESOURCE_TYPE OR
(o.resource_type IS NULL AND o.resource_type IS NOT NULL) OR
(o.resource_type IS NOT NULL AND o.resource_type IS NULL)
) THEN
<sql statement here>.
END IF;
Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Dec 12 '07 #2

P: n/a
On Dec 12, 10:38 pm, Serge Rielau <srie...@ca.ibm.comwrote:
fur...@gmail.com wrote:
Hi,
The following trigger does
NOT
fire if the column has a NULL or if a NULL
is updated to the column. Any ides how to fix this?
IF (o.RESOURCE_TYPE <n.RESOURCE_TYPE OR

(o.resource_type IS NULL AND o.resource_type IS NOT NULL) OR
(o.resource_type IS NOT NULL AND o.resource_type IS NULL)
) THEN
<sql statement here>.
END IF;
Not sure what happened to my post, so I'll repost.

Serge, is there a difference in how an IF stmt such as the above, and
an WHEN clause is evaluated in a trigger? For unknown reasons I have
always preferred WHEN over IF.

FWIW, if we can find a value outside the domain of resource_type (I
assumed 'x') the following will do the trick with less typing ;-)

coalesce(o.resource_type, 'x') <coalesce(n.resource_type, 'x')

/Lennart

Dec 13 '07 #3

P: n/a
Lennart wrote:
Serge, is there a difference in how an IF stmt such as the above, and
an WHEN clause is evaluated in a trigger? For unknown reasons I have
always preferred WHEN over IF.
IF statement is A LOT heavier than WHEN.
>
FWIW, if we can find a value outside the domain of resource_type (I
assumed 'x') the following will do the trick with less typing ;-)

coalesce(o.resource_type, 'x') <coalesce(n.resource_type, 'x')
Yes, that will work.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Dec 13 '07 #4

P: n/a
On Dec 13, 6:35 pm, Serge Rielau <srie...@ca.ibm.comwrote:
Lennart wrote:
Serge, is there a difference in how an IF stmt such as the above, and
an WHEN clause is evaluated in a trigger? For unknown reasons I have
always preferred WHEN over IF.

IF statement is A LOT heavier than WHEN.
Thanks, now there is a known reason why I always preferred WHEN over
IF ;-)

/Lennart

[...]
Dec 13 '07 #5

P: n/a
On Dec 12, 4:38 pm, Serge Rielau <srie...@ca.ibm.comwrote:
fur...@gmail.com wrote:
Hi,
The following trigger does
NOT
fire if the column has a NULL or if a NULL
is updated to the column. Any ides how to fix this?
IF (o.RESOURCE_TYPE <n.RESOURCE_TYPE OR

(o.resource_type IS NULL AND o.resource_type IS NOT NULL) OR
(o.resource_type IS NOT NULL AND o.resource_type IS NULL)
) THEN
<sql statement here>.
END IF;

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab


It worked OK. Thanks for your help!!!
Carlos
Dec 13 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.