Can constraint replace a trigger?

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

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

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

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

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;

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

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

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