If you're in SQL Server then you just need to use triggers on Boxes
and Buckets to prevent the insert:
create table boxes (
barcode_num char(4) primary key,
box_num smallint identity(1,1)
)
create table buckets (
barcode_num char(4) primary key,
bucket_num smallint identity(1,1)
)
go
create view dbo.barcodes
as
select barcode_num from dbo.boxes
union all
select barcode_num from dbo.buckets
go
create trigger box_barcodes on boxes instead of insert, update
as
if exists (
select
i.barcode_num
from
inserted i
left join barcodes b on b.barcode_num = i.barcode_num
left join deleted d on d.barcode_num = b.barcode_num
where
b.barcode_num is not null
and d.barcode_num is null)
begin
PRINT 'Boxes: Insert / Update Aborted: Duplicate Barcode'
end
else
begin
delete
boxes
from
boxes b
join deleted d on d.barcode_num = b.barcode_num
insert into
boxes
select
barcode_num
from
inserted
end
go
create trigger bucket_barcodes on buckets instead of insert, update
as
if exists (
select
i.barcode_num
from
inserted i
left join barcodes b on b.barcode_num = i.barcode_num
left join deleted d on d.barcode_num = b.barcode_num
where
b.barcode_num is not null
and d.barcode_num is null)
begin
PRINT 'Buckets: Insert / Update Aborted: Duplicate Barcode'
end
else
begin
delete
buckets
from
buckets b
join deleted d on d.barcode_num = b.barcode_num
insert into
buckets
select
barcode_num
from
inserted
end
go
insert into
dbo.boxes (barcode_num)
select
'1111'
union all select '1112'
union all select '1113'
union all select '1114'
union all select '1115'
union all select '1116'
union all select '1117'
union all select '1118'
update
boxes
set
barcode_num = convert(char(4), convert(int, barcode_num) + 1)
from
boxes
insert into buckets (barcode_num) values ('1118')
select * from boxes
select * from buckets
drop view barcodes
drop table boxes
drop table buckets
Cheers,
Jason Lepack
