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

Trigger structure

P: n/a
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';
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Bob Stearns wrote:
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';

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
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2

P: n/a
Serge Rielau wrote:
Bob Stearns wrote:
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';


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


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.
Nov 12 '05 #3

P: n/a
Bob Stearns wrote:
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
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #4

P: n/a
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


Serge Rielau wrote:
Bob Stearns wrote:
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';


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

Nov 12 '05 #5

P: n/a
Phil Sherman wrote:
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.

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
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.