469,946 Members | 1,929 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,946 developers. It's quick & easy.

Multiple-condition trigger trouble

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

Dec 1 '06 #1
7 8690
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
Dec 1 '06 #2
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
Dec 1 '06 #3
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
Dec 2 '06 #4
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
Dec 2 '06 #5
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
Dec 2 '06 #6
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
Dec 2 '06 #7
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
Dec 5 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Rolf Wester | last post: by
66 posts views Thread by Darren Dale | last post: by
6 posts views Thread by Ben Hallert | last post: by
22 posts views Thread by Matthew Louden | last post: by
9 posts views Thread by Graham | last post: by
2 posts views Thread by areef.islam | last post: by
4 posts views Thread by Matt Kruse | last post: by
35 posts views Thread by keerthyragavendran | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.