drop table  dbzOrder;
go

drop table  dbzClient;
go

create table dbzClient
(
	id int primary key ,
	name nvarchar(40) default 80,
	aDateTime datetime2
);
go

create unique index U_dbzClient_name
	on dbzClient (name) ;
go

create table dbzOrder
(
    id int primary key,
    clientId int not null,
    quantity int not null,
    orderRef nvarchar(40),
    aDateTime datetime2,
    constraint FK_orer_client foreign key (clientId) references dbzClient
);
go

create unique index U_order_ref on dbzOrder (orderRef) include (clientId);
go

EXEC sys.sp_cdc_enable_db;
go
EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name   = N'dbzClient', @role_name     = NULL,  @supports_net_changes = 0;
go
EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name   = N'dbzOrder', @role_name     = NULL,  @supports_net_changes = 0;
go

insert into dbzClient(id, name,aDateTime) values (1,'Client1',  getutcdate());
go
insert into dbzClient(id, name,aDateTime) values (2,'Client2',  getutcdate());
insert into dbzClient(id, name,aDateTime) values (3,'Client3',  getutcdate());
go

select * from cdc.dbo_dbzClient_CT;
go

insert into dbzOrder (id, clientId, quantity, orderRef, aDateTime) values (1,1,101,'#0001', getutcdate());
insert into dbzOrder (id, clientId, quantity, orderRef, aDateTime) values (2,1,203,'#0002', getutcdate());
insert into dbzOrder (id, clientId, quantity, orderRef, aDateTime) values (3,2,501,'#0003', getutcdate());
insert into dbzOrder (id, clientId, quantity, orderRef, aDateTime) values (4,2,11,'#0004', getutcdate());
insert into dbzOrder (id, clientId, quantity, orderRef, aDateTime) values (5,3,50,'#0005', getutcdate());
insert into dbzOrder (id, clientId, quantity, orderRef, aDateTime) values (6,3,120,'#0006', getutcdate());
go

update dbzClient set name = 'Client__1' where id = 1;
go
select * from cdc.dbo_dbzClient_CT;
go

update dbzClient set name = name + '__' where id < 3;
go
select * from cdc.dbo_dbzClient_CT;
go
