Can constraint replace a trigger?

[lenygold via DBMonster.com]

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

[Serge Rielau]

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

[lenygold via DBMonster.com]

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

[Serge Rielau]

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

[--CELKO--]

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;

[lenygold via DBMonster.com]

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

[Serge Rielau]

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

[--CELKO--]

>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.