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

Can constraint replace a trigger?

P: n/a
Hi everybody.
I wrote a trigger for the follwing requirement:
In a given table T with columns A,B,C are up to 5 entries allowed for a given
combination
of (A,B).
create table t (a varchar2(10), b number, c number,
constraint t_pk primary key (a,b,c));

insert into t(a,b,c) values ('A', 1, 1);
insert into t(a,b,c) values ('A', 1, 4);
insert into t(a,b,c) values ('A', 1, 99);
insert into t(a,b,c) values ('A', 1, 1000);
insert into t(a,b,c) values ('A', 1, 3);

Next insert should raise an error, because
-- it is the 6th row with a='A' and b=1
insert into t(a,b,c) values ('A', 1, 15);

Can constraint in DB2 to replace a trigger?
Thank's in advance
Leny G.

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200809/1

Sep 12 '08 #1
Share this Question
Share on Google+
7 Replies


P: n/a
lenygold via DBMonster.com wrote:
Hi everybody.
I wrote a trigger for the follwing requirement:
In a given table T with columns A,B,C are up to 5 entries allowed for a given
combination
of (A,B).
create table t (a varchar2(10), b number, c number,
constraint t_pk primary key (a,b,c));

insert into t(a,b,c) values ('A', 1, 1);
insert into t(a,b,c) values ('A', 1, 4);
insert into t(a,b,c) values ('A', 1, 99);
insert into t(a,b,c) values ('A', 1, 1000);
insert into t(a,b,c) values ('A', 1, 3);

Next insert should raise an error, because
-- it is the 6th row with a='A' and b=1
insert into t(a,b,c) values ('A', 1, 15);

Can constraint in DB2 to replace a trigger?
No. A check constraint in DB2 (and most other DBMS I know of) operates
on a single row only.
To make this work you would need to define an MQT and then a check
constraint on that. That is not allowed today.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Sep 12 '08 #2

P: n/a
Thank ypu Serge.
Trigger is working fine.
What about to create extra column with concatenated values A and B.
Can contraint be used to this new column?

Serge Rielau wrote:
>Hi everybody.
I wrote a trigger for the follwing requirement:
[quoted text clipped - 15 lines]
>>
Can constraint in DB2 to replace a trigger?
No. A check constraint in DB2 (and most other DBMS I know of) operates
on a single row only.
To make this work you would need to define an MQT and then a check
constraint on that. That is not allowed today.

Cheers
Serge
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200809/1

Sep 12 '08 #3

P: n/a
lenygold via DBMonster.com wrote:
Thank ypu Serge.
Trigger is working fine.
What about to create extra column with concatenated values A and B.
Can contraint be used to this new column?

Serge Rielau wrote:
>>Hi everybody.
I wrote a trigger for the follwing requirement:
[quoted text clipped - 15 lines]
>>Can constraint in DB2 to replace a trigger?
No. A check constraint in DB2 (and most other DBMS I know of) operates
on a single row only.
To make this work you would need to define an MQT and then a check
constraint on that. That is not allowed today.
I don't see how that is meant to help. you need to be able to COUNT
rows. That is what your trigger is doing, correct?

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Sep 13 '08 #4

P: n/a
Why did you post Oracle dialect to a DB2 newsgroup? Here is a trick
that will require some effort on your part to keep things right and
handle gaps in tally.

CREATE TABLE Foobar
(a VARCHAR(10) NOT NULL,
b INTEGER NOT NULL,
c INTEGER NOT NULL,
tally INTEGER DEFAULT 1 NOT NULL
CHECK (tally BETWEEN 1 AND 5),
UNIQUE (a, b, tally)
PRIMARY KEY (a, b, c));

INSERT INTO Foobar (a, b, c, tally)
VALUES ('A', 1, 1, 1),
('A', 1, 4, 2),
('A', 1, 99, 3),
('A', 1, 1000, 4)
('A', 1, 3, 5);

Another declarative trick is a VIEW and the WITH CHECK OPTION. You
can only use the VIEW for updates, tho.

CREATE VIEW LimitedFoobar (a,b,c)
AS
SELECT a,b,c
FROM Foobar
WHERE NOT EXISTS
(SELECT COUNT(*)
FROM Foobar
GROUP BY a, b
HAVING CIYBT(*) 5)
WITH CHECK OPTION;

Sep 13 '08 #5

P: n/a
I just tested your solutions in DB2 9.5C Express and it is working great.
I never used such table DDL.
Thank you very much.
Will it work for other DB2 versions?

--CELKO-- wrote:
>Why did you post Oracle dialect to a DB2 newsgroup? Here is a trick
that will require some effort on your part to keep things right and
handle gaps in tally.

CREATE TABLE Foobar
(a VARCHAR(10) NOT NULL,
b INTEGER NOT NULL,
c INTEGER NOT NULL,
tally INTEGER DEFAULT 1 NOT NULL
CHECK (tally BETWEEN 1 AND 5),
UNIQUE (a, b, tally)
PRIMARY KEY (a, b, c));

INSERT INTO Foobar (a, b, c, tally)
VALUES ('A', 1, 1, 1),
('A', 1, 4, 2),
('A', 1, 99, 3),
('A', 1, 1000, 4)
('A', 1, 3, 5);

Another declarative trick is a VIEW and the WITH CHECK OPTION. You
can only use the VIEW for updates, tho.

CREATE VIEW LimitedFoobar (a,b,c)
AS
SELECT a,b,c
FROM Foobar
WHERE NOT EXISTS
(SELECT COUNT(*)
FROM Foobar
GROUP BY a, b
HAVING CIYBT(*) 5)
WITH CHECK OPTION;
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200809/1

Sep 13 '08 #6

P: n/a
lenygold via DBMonster.com wrote:
I just tested your solutions in DB2 9.5C Express and it is working great.
I never used such table DDL.
Thank you very much.
Will it work for other DB2 versions?

--CELKO-- wrote:
>Why did you post Oracle dialect to a DB2 newsgroup? Here is a trick
that will require some effort on your part to keep things right and
handle gaps in tally.

CREATE TABLE Foobar
(a VARCHAR(10) NOT NULL,
b INTEGER NOT NULL,
c INTEGER NOT NULL,
tally INTEGER DEFAULT 1 NOT NULL
CHECK (tally BETWEEN 1 AND 5),
UNIQUE (a, b, tally)
PRIMARY KEY (a, b, c));

INSERT INTO Foobar (a, b, c, tally)
VALUES ('A', 1, 1, 1),
('A', 1, 4, 2),
('A', 1, 99, 3),
('A', 1, 1000, 4)
('A', 1, 3, 5);

Another declarative trick is a VIEW and the WITH CHECK OPTION. You
can only use the VIEW for updates, tho.

CREATE VIEW LimitedFoobar (a,b,c)
AS
SELECT a,b,c
FROM Foobar
WHERE NOT EXISTS
(SELECT COUNT(*)
FROM Foobar
GROUP BY a, b
HAVING CIYBT(*) 5)
WITH CHECK OPTION;
Yes. Should work across all versions of DB2 for LUW since DB2 V2.
Reasonably certain it works against DB2 for zOS as well.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Sep 14 '08 #7

P: n/a
>I never used such table DDL. Thank you very much. *<<

Welcome. Buy my books and make me rich :) In particular, THINKING IN
SETS will help. It takes time to switch from procedural thinking to a
data-driven, set oriented mindset.
>Will it work for other DB2 versions? <<
Those two solutions were straight Standard SQL 92 and higher; they
ought to work on any SQL product.
Sep 14 '08 #8

This discussion thread is closed

Replies have been disabled for this discussion.