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'; 5 3270
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
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.
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Dave C. |
last post by:
Hello,
I have created the following trigger:
CREATE TRIGGER tr_UDFTest ON UserDefinedFields FOR INSERT, UPDATE
AS
DECLARE @foobar varchar(100)
SELECT @foobar= foobar FROM inserted
IF (...
|
by: Trevor Fairchild |
last post by:
I am trying to create a very minimal auditing system for a series of
databases.
I am in the process of writing Update triggers for 5 Tablse. I will
write a trigger for each table-the trigger's...
|
by: Marko Damaschke |
last post by:
Hello alltogether,
i'm working on a database-plattform which should work with
fail-over-technics.
Therefor 2 identic machines with internal RAID are available, which work
in...
|
by: Martin |
last post by:
Hello,
I'm new with triggers and I can not find any good example on how to
do the following:
I have two tables WO and PM with the following fields:
WO.WONUM, VARCHAR(10)
WO.PMNUM,...
|
by: coosa |
last post by:
I have a table:
----------------------------------------------------
CREATE TABLE CATEGORY (
CATEGORY_ID INTEGER IDENTITY(1,1) NOT NULL,
CATEGORY_NAME VARCHAR(40) NOT NULL,...
|
by: Jules Alberts |
last post by:
Hello everyone,
Several columns in sereval tables in my DB should always be lowercase.
I now have a simple function:
create or replace function code_lower() returns trigger as '
begin...
|
by: LyzH |
last post by:
Someone else had a question on how to emulate a mouse click. I tried posting
in that thread but I have something of a twist on this problem and I'm
really in trouble here! If I don't get help...
|
by: dean.cochrane |
last post by:
I have inherited a large application.
I have a table which contains a hierarchy, like this
CREATE TABLE sample_table(
sample_id int NOT NULL
parent_sample_id int NOT NULL
....lots of other...
|
by: Oliver |
last post by:
I'm fairly new to DB2.
I have been assigned to build a delete trigger that finds the data
type of each of the table's fields so that the trigger can then build
a string consisting of OLD values...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |