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

Triggers DB2 UDB v8.1.?

P: n/a
I have lots of dates in my schema, almost none of which may be entered
as future dates. The best I could come up with is the following two
triggers, repeated for each table.column which is a date. Anyone have a
better idea? I'm just adding triggers to my toolbox and will appreciate
any suggestions.

Is there any great benefit in the OF clause of the UPDATE trigger?

CREATE TRIGGER IS3.date_later_001u
NO CASCADE BEFORE UPDATE OF weight_date ON is3.yearling
REFERENCING NEW AS n OLD AS o
FOR EACH ROW MODE DB2SQL
WHEN (N.weight_date>CURRENT_DATE)
SIGNAL SQLSTATE '75001'
SET MESSAGE_TEXT='Weight_date in Future'
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'
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Bob Stearns wrote:
I have lots of dates in my schema, almost none of which may be entered
as future dates. The best I could come up with is the following two
triggers, repeated for each table.column which is a date. Anyone have a
better idea? I'm just adding triggers to my toolbox and will appreciate
any suggestions.

Is there any great benefit in the OF clause of the UPDATE trigger?

CREATE TRIGGER IS3.date_later_001u
NO CASCADE BEFORE UPDATE OF weight_date ON is3.yearling
REFERENCING NEW AS n OLD AS o
FOR EACH ROW MODE DB2SQL
WHEN (N.weight_date>CURRENT_DATE)
SIGNAL SQLSTATE '75001'
SET MESSAGE_TEXT='Weight_date in Future'
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'

Yes, if you take a look at the explain both triggers should simply sit
in input stream of the INERT/UPDATE and not cost anything more than the
predicate itself.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.