1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51
| /*create table COMPATIBILITY_CONNECTION
(
Id_ProductPin int not null references CDS_PRODUCTS(Id_Product),
Id_ProductBox int not null references CDS_PRODUCTS(Id_Product),
check (Id_ProductPin <= Id_ProductBox),
primary key (Id_ProductPin, Id_ProductBox)
);
go*/
-- Ce trigger est là pour rendre la contrainte sur l'ordre des produits compatibles non restrictive : si on ne respecte pas l'ordre, le trigger remet tout dans l'ordre
create trigger trg_new_compatibility on COMPATIBILITY_CONNECTION
instead of insert
as
begin
insert into COMPATIBILITY_CONNECTION (Id_ProductPin, Id_ProductBox)
select
case when Id_ProductPin <= Id_ProductBox then Id_ProductPin else Id_ProductBox end,
case when Id_ProductPin <= Id_ProductBox then Id_ProductBox else Id_ProductPin end
from inserted;
end;
go
/*insert into COMPATIBILITY_CONNECTION (Id_ProductPin, Id_ProductBox) values
(18, 18),
(18, 48),
(18, 47),
(47, 47),
(47, 48),
(48, 48),
(39, 39); -- Cette ligne doit être remise dans le bon sens par le trigger "trg_new_compatible"
*/
-- Cette fonction retourne 1 si les produits sont compatibles, 0 sinon
create function dbo.AreCompatibles(@Id_ProductPin int, @Id_ProductBox int)
returns bit
as
begin
if @Id_ProductPin > @Id_ProductBox
begin
return dbo.AreCompatibles(@Id_ProductBox, @Id_ProductPin);
end
return (select count(*) from COMPATIBILITY_CONNECTION where Id_ProductPin = @Id_ProductPin and Id_ProductBox = @Id_ProductBox);
end;
select p1.Id_Product, p2.Id_Product, dbo.AreCompatibles(p1.Id_Product, p2.Id_Product)
from CDS_PRODUCTS p1
inner join CDS_PRODUCTS p2 on p2.Id_Product <> p1.Id_Product
select p1.Id_Product, p1.Connection, p2.Id_Product, p2.Connection
from CDS_PRODUCTS p1
inner join COMPATIBILITY_CONNECTION on c.Id_ProductPin = p1.Id_Product or c.Id_ProductBox = p1.Id_Product
inner join CDS_PRODUCTS p2 on p2.Id_Product = case c.Id_ProductPin when p1.Id_Product then c.Id_ProductBox else c.Id_ProductPin end
--where p1.Id_Product = 18 |
Partager