Multiple-condition trigger trouble

[apattin]

Hi experts,

I am having a hard time coding a trigger to handle multiple validation
conditions in DB2 UDB V8.1.9.. I thought that a single trigger might be
better than one-trigger-per-condition, but so far, no cigar. Here is
what I'd like to do (pseudo-code):

CREATE TRIGGER fp_insert
NO CASCADE BEFORE INSERT
ON DB2INST1.FILE_PROPERTY
REFERENCING NEW AS n
FOR EACH ROW
BEGIN ATOMIC
WHEN (n.property_id = 175 and not exists (select 1 from
DICT_TOO_TRUTH where dict_value = n.property_value))
THEN RAISE_ERROR('70001', 'Error')
WHEN (n.property_id = 187 and not exists (select 1 from
DICT_BIOPSY_TRUTH where dict_value = n.property_value))
THEN RAISE_ERROR('70001', 'Error')
END;

DB2 does not like multiple "WHEN" conditions and I cannot find the
correct syntax for CASE. Can anyone help?

Thanks!

Alejandrina

[Serge Rielau]

Create syntax you have there ... :-)

CREATE TRIGGER fp_insert
NO CASCADE BEFORE INSERT
ON DB2INST1.FILE_PROPERTY
REFERENCING NEW AS n
FOR EACH ROW
WHEN (n.property_id = 175
and not exists (select 1 from DICT_TOO_TRUTH
where dict_value = n.property_value)
OR
n.property_id = 187
and not exists (select 1 from DICT_BIOPSY_TRUTH
where dict_value = n.property_value))
VALUES(RAISE_ERROR('70001', 'Error'));

The trigger condition precedes the trigger body.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html

[Knut Stolze]

apattin wrote:

Hi experts,

I am having a hard time coding a trigger to handle multiple validation
conditions in DB2 UDB V8.1.9.. I thought that a single trigger might be
better than one-trigger-per-condition, but so far, no cigar. Here is
what I'd like to do (pseudo-code):

CREATE TRIGGER fp_insert
NO CASCADE BEFORE INSERT
ON DB2INST1.FILE_PROPERTY
REFERENCING NEW AS n
FOR EACH ROW
BEGIN ATOMIC
WHEN (n.property_id = 175 and not exists (select 1 from
DICT_TOO_TRUTH where dict_value = n.property_value))
THEN RAISE_ERROR('70001', 'Error')
WHEN (n.property_id = 187 and not exists (select 1 from
DICT_BIOPSY_TRUTH where dict_value = n.property_value))
THEN RAISE_ERROR('70001', 'Error')
END;

DB2 does not like multiple "WHEN" conditions and I cannot find the
correct syntax for CASE. Can anyone help?

You could resort to IF ... THEN ... ELSE ... or a CASE statement. With the
latter, you need to put an CASE ... END CASE around the stuff in the atomic
compound.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[apattin]

Thanks Serge,

The solution you gave me works only if the trigger action is the same
for all cases, which I'd rather not have. My mistake, because I should
have said that I wanted to implement the following logic:

CREATE TRIGGER fp_insert
NO CASCADE BEFORE INSERT
ON DB2INST1.FILE_PROPERTY
REFERENCING NEW AS n
FOR EACH ROW
BEGIN ATOMIC
WHEN (n.property_id = 175 and not exists (select 1 from
DICT_TOO_TRUTH where dict_value = n.property_value))
THEN RAISE_ERROR('70001', 'Error in property 175')
WHEN (n.property_id = 187 and not exists (select 1 from
DICT_BIOPSY_TRUTH where dict_value = n.property_value))
THEN RAISE_ERROR('70001', 'Error' in property 187)
END;

Notice the error messages are different. How can I do this?

Thanks again for the prompt response!

Alejandrina
Serge Rielau wrote:

Create syntax you have there ... :-)

CREATE TRIGGER fp_insert
NO CASCADE BEFORE INSERT
ON DB2INST1.FILE_PROPERTY
REFERENCING NEW AS n
FOR EACH ROW
WHEN (n.property_id = 175
and not exists (select 1 from DICT_TOO_TRUTH
where dict_value = n.property_value)
OR
n.property_id = 187
and not exists (select 1 from DICT_BIOPSY_TRUTH
where dict_value = n.property_value))
VALUES(RAISE_ERROR('70001', 'Error'));

The trigger condition precedes the trigger body.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html

[apattin]

I did not want to use IF because I thought CASE might be faster. Is
this true?

Can you give me an example of how you'd use CASE in my situation?

Alejandrina
Knut Stolze wrote:

apattin wrote:

Hi experts,

I am having a hard time coding a trigger to handle multiple validation
conditions in DB2 UDB V8.1.9.. I thought that a single trigger might be
better than one-trigger-per-condition, but so far, no cigar. Here is
what I'd like to do (pseudo-code):

CREATE TRIGGER fp_insert
NO CASCADE BEFORE INSERT
ON DB2INST1.FILE_PROPERTY
REFERENCING NEW AS n
FOR EACH ROW
BEGIN ATOMIC
WHEN (n.property_id = 175 and not exists (select 1 from
DICT_TOO_TRUTH where dict_value = n.property_value))
THEN RAISE_ERROR('70001', 'Error')
WHEN (n.property_id = 187 and not exists (select 1 from
DICT_BIOPSY_TRUTH where dict_value = n.property_value))
THEN RAISE_ERROR('70001', 'Error')
END;

DB2 does not like multiple "WHEN" conditions and I cannot find the
correct syntax for CASE. Can anyone help?

You could resort to IF ... THEN ... ELSE ... or a CASE statement. With the
latter, you need to put an CASE ... END CASE around the stuff in the atomic
compound.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Serge Rielau]

apattin wrote:

I did not want to use IF because I thought CASE might be faster. Is
this true?

Can you give me an example of how you'd use CASE in my situation?

case EXPRESSSION is faster than if STATEMENT. Yes

CREATE TRIGGER fp_insert
NO CASCADE BEFORE INSERT
ON DB2INST1.FILE_PROPERTY
REFERENCING NEW AS n
FOR EACH ROW
SELECT CASE WHEN n.property_id = 175
and not exists (select 1 from DICT_TOO_TRUTH
where dict_value = n.property_value)
THEN CAST(raise_error('71001', 'hello') AS INT
WHEN
n.property_id = 187
and not exists (select 1 from DICT_BIOPSY_TRUTH
where dict_value = n.property_value))
RAISE_ERROR('70001', 'World'));
END
FROM SYSIBM.SYSDUMMY1;

raise_error() doesn't have a defined return type, hence the CAST.

Another option would be to use 2 triggers.
Should be the same cost :-)
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html

[Serge Rielau]

apattin wrote:

I did not want to use IF because I thought CASE might be faster. Is
this true?

Can you give me an example of how you'd use CASE in my situation?

case EXPRESSSION is faster than if STATEMENT. Yes

CREATE TRIGGER fp_insert
NO CASCADE BEFORE INSERT
ON DB2INST1.FILE_PROPERTY
REFERENCING NEW AS n
FOR EACH ROW
SELECT CASE WHEN n.property_id = 175
and not exists (select 1 from DICT_TOO_TRUTH
where dict_value = n.property_value)
THEN CAST(raise_error('71001', 'hello') AS INT
WHEN
n.property_id = 187
and not exists (select 1 from DICT_BIOPSY_TRUTH
where dict_value = n.property_value)
THEN RAISE_ERROR('70001', 'World')
END
FROM SYSIBM.SYSDUMMY1;

raise_error() doesn't have a defined return type, hence the CAST.

Another option would be to use 2 triggers.
Should be the same cost :-)

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html

[apattin]

Worked like a charm. Thanks for unscrambling the trigger syntax for me!

Serge Rielau wrote:

apattin wrote:

I did not want to use IF because I thought CASE might be faster. Is
this true?

Can you give me an example of how you'd use CASE in my situation?

case EXPRESSSION is faster than if STATEMENT. Yes

CREATE TRIGGER fp_insert
NO CASCADE BEFORE INSERT
ON DB2INST1.FILE_PROPERTY
REFERENCING NEW AS n
FOR EACH ROW
SELECT CASE WHEN n.property_id = 175
and not exists (select 1 from DICT_TOO_TRUTH
where dict_value = n.property_value)
THEN CAST(raise_error('71001', 'hello') AS INT
WHEN
n.property_id = 187
and not exists (select 1 from DICT_BIOPSY_TRUTH
where dict_value = n.property_value))
RAISE_ERROR('70001', 'World'));
END
FROM SYSIBM.SYSDUMMY1;

raise_error() doesn't have a defined return type, hence the CAST.

Another option would be to use 2 triggers.
Should be the same cost :-)
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html