Trigger structure

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob Stearns - 26 Oct 2005 20:39 GMT

I have two (actually many) dates in a table I want to validate on
insertion. The following works in the case of only one WHEN clause but
fails with two (or more), with the (improper? inappropriate?) error message:

SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: CREATE TRIGGER
IS3.date_later_001i NO C;BEGIN-OF-STATEMENT;<space>

which is interpreted as:

An unexpected token "CREATE TRIGGER IS3.date_later_001i NO C" was found
following "BEGIN-OF-STATEMENT". Expected tokens may include: "<space>".
Line: 8

How can I create such a trigger?

And as an efficiency question, should I create 1 BEFORE UPDATE OF x ON t
trigger for each column x or one BEFORE UPDATE ON t? Does the answer
depend on the number of columns being checked?

CREATE TRIGGER IS3.date_later_001i
NO CASCADE BEFORE INSERT ON is3.yearling
REFERENCING NEW AS n
FOR EACH ROW MODE DB2SQL
WHEN (N.weight_date>CURRENT_DATE)
SIGNAL SQLSTATE '75001'
SET MESSAGE_TEXT='Weight_date in Future';
WHEN (N.scrotal_date>CURRENT_DATE)
SIGNAL SQLSTATE '75001'
SET MESSAGE_TEXT='Scrotal_date in Future';

Reply to this Message

Serge Rielau - 26 Oct 2005 21:42 GMT

> I have two (actually many) dates in a table I want to validate on
> insertion. The following works in the case of only one WHEN clause but
[quoted text clipped - 26 lines]
> SIGNAL SQLSTATE '75001'
> SET MESSAGE_TEXT='Scrotal_date in Future';

Hmm.. which part of the syntax diagram gave you that idea? ;-)
You have two choices or maximum performance:
1. Use one trigger per condition. A bit verbose, but the runtime
performance will we good. Compiletime will suffer, so no constants in
these dynamic update statements please!
2. Use one trigger, but another way to signal:
CREATE TRIGGER IS3.date_later_001i
NO CASCADE BEFORE INSERT ON is3.yearling
REFERENCING NEW AS n
FOR EACH ROW MODE DB2SQL
VALUES CASE WHEN N.weight_date>CURRENT_DATE
THEN CAST(raise_error('75001', 'Weight_date in Future')
AS INT)
WHEN N.scrotal_date>CURRENT_DATE
THEN raise_error('75001', 'Scrotal_date in Future')
...
END;

Note that CAST in the first THEN clause. raise_error() has an undefined
result type so you need to give the CASE expression a nudge once.

This is the densest trigger you can write for thsi kind of thing.

Cheers
Serge

Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Reply to this Message

Bob Stearns - 26 Oct 2005 22:15 GMT

>> I have two (actually many) dates in a table I want to validate on
>> insertion. The following works in the case of only one WHEN clause but
[quoted text clipped - 53 lines]
> Cheers
> Serge

Thanks for the quick reply and suggestion.

I didn't check the syntax diagrams for this, I was proceeding with (the
mistaken) assumption that this WHEN was like the WHEN in the CASE
statement. A kind of orthoganality assumption.

As a matter of curiosity, where does the empty (on success), unnamed
table created by the values clause go?

This is the insert case, so one trigger should be more efficient, since
every insert trigger must be fired. I can see the better efficiency of
separate triggers in the update case, given that they are only invoked
when the named column changes.

Reply to this Message

Serge Rielau - 26 Oct 2005 23:34 GMT

> As a matter of curiosity, where does the empty (on success), unnamed
> table created by the values clause go?

Nowhere. In 20/20 hindsight this wasn't the best choice (and non
standard), but it's DB2 V2 (comming from Starburst) and with us for
good. It sure is convenient though :-)

> This is the insert case, so one trigger should be more efficient, since
> every insert trigger must be fired. I can see the better efficiency of
> separate triggers in the update case, given that they are only invoked
> when the named column changes.

You could use the trigger column specification for update. Whether that
gives you a runtime improvement or not depends on the UPDATE usage
(typically one column updated?).
Given that we are talking only codepath and not particularly much
assuming you don't have hundrets of columns I wouldn't worry about it.
There are bigger fish to fry in your system more worthy of your salary I
dare guess.

Cheers
Serge

Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Reply to this Message

Phil Sherman - 28 Oct 2005 15:20 GMT

Serge's response, while yielding a very compact solution to the
validation requirement raises a philosophical question concerning how
validation should be performed. Serge's trigger will stop processing at
the first column containing an error. If five columns have date errors,
then it could take five attempts at an insert to correct all of them.

The alternative is to check all of the columns, building a composite
error message listing all of the ones in error. A failed insert will
identify all of the columns with the date error.

Phil Sherman

>> I have two (actually many) dates in a table I want to validate on
>> insertion. The following works in the case of only one WHEN clause but
[quoted text clipped - 53 lines]
> Cheers
> Serge

Reply to this Message

Serge Rielau - 28 Oct 2005 23:19 GMT

> Serge's response, while yielding a very compact solution to the
> validation requirement raises a philosophical question concerning how
[quoted text clipped - 5 lines]
> error message listing all of the ones in error. A failed insert will
> identify all of the columns with the date error.

True. This brings us quickly to an app development question.
Most apps I have seen involving forms (which is where I would expect
multiple errors to occur) do their own sanity checking.
So the trigger is more of a firewall than meant to raise end user errors.
A question of coding philosophy really.

Cheers
Serge

Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Reply to this Message