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

[furulo]

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

[Serge Rielau]

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

[Lennart]

On Dec 12, 10:38 pm, Serge Rielau <[e-mail]:

[e-mail] 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

[Serge Rielau]

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

[Lennart]

On Dec 13, 6:35 pm, Serge Rielau <[e-mail]:

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

[...]

[furulo]

On Dec 12, 4:38 pm, Serge Rielau <[e-mail]:

[e-mail] 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