When using separate mock data sets for tables it is not possible to give a generation order number to the data sets. As of this moment the order of the generation of the mock data is in alphabetical order of the data set.
Being able to give a generation order number to mock data will prevent error messages.
An example of this can be found in the attachment
Attachment did not go through.
For this example I have a generic country and a company mock data set. In the company mock data the county mock data set is used. However because county comes after company in alphabetical order the unit test code doesn’t recognize that there are any countries available. This gives an error in creating the company mock data.
Mock data without order number:
Unit test code for the creation of the table. Here company comes first then country. This gives an error because country_id is mandatory in company but no country can be found yet:
-- Transaction for unit test execution
begin transaction;
declare @tsf_error_msg nvarchar(max);
begin try
-- Canary table to check if the transaction is unintentionally committed
create table unit_test_canary(unit_test_canary char(1));
-- Disable triggers
alter table [company] disable trigger all;
alter table [company_bank_account] disable trigger all;
alter table [company_bank_account_mandate] disable trigger all;
alter table [country] disable trigger all;
alter table [sbi_code] disable trigger all;
exec tsf_mock_tab 'company';
exec tsf_mock_tab 'company_bank_account';
exec tsf_mock_tab 'company_bank_account_mandate';
exec tsf_mock_tab 'country';
exec tsf_mock_tab 'sbi_code';
set identity_insert [company] on;
insert into [company] ([company_id], [company_code], [company_name], [kvk_code], [legal_form], [sbi_code_id], [rsin_number], [visit_country_id], [visit_postal_code], [visit_house_number], [visit_street_name], [visit_city], [postal_is_visit_address], [postal_country_id], [postal_postal_code], [postal_house_number], [postal_street_name], [postal_city]) values (1, 'C10001', 'Advanced Programs', '33272180', 1, 1, '12345678', (
select c.country_id
from country c
where c.alpha_three_country_code = 'NLD'
), '1000AA', 1, 'Straatnaam', 'Amsterdam', 1, (
select c.country_id
from country c
where c.alpha_three_country_code = 'NLD'
), '1000AA', 1, 'Straatnaam', 'Amsterdam');
set identity_insert [company] off;
set identity_insert [company_bank_account] on;
insert into [company_bank_account] ([company_id], [company_bank_account_id], [bank_account_number], [bank_account_is_sepa], [country_id]) values (1, 1, 'NL39ABNA8234998285', 1, (
select c.country_id
from country c
where c.alpha_three_country_code = 'NLD'
));
set identity_insert [company_bank_account] off;
set identity_insert [company_bank_account] on;
insert into [company_bank_account] ([company_id], [company_bank_account_id], [bank_account_number], [bank_account_is_sepa], [start_date_bank_account], [end_date_bank_account], [country_id]) values (1, 2, 'NL08INGB4026824314', 1, dateadd(day, -10, cast(getdate() as date))
, dateadd(day, -5, cast(getdate() as date)), (
select c.country_id
from country c
where c.alpha_three_country_code = 'NLD'
)
);
set identity_insert [company_bank_account] off;
set identity_insert [company_bank_account_mandate] on;
insert into [company_bank_account_mandate] ([company_id], [company_bank_account_id], [company_bank_account_mandate_id], [company_bank_account_mandate_code], [start_date_mandate]) values (1, 1, 1, '23000001', dateadd(day, -2, cast(getdate() as date)));
set identity_insert [company_bank_account_mandate] off;
set identity_insert [sbi_code] on;
insert into [sbi_code] ([sbi_code_id], [section_code], [description_section], [department_code], [sbi_code], [description_sbi]) values (1, 'SC-1', 'Omschrijving', 'DP-01', 'SBI_CODE', 'Omschrijving');
set identity_insert [sbi_code] off;
set identity_insert [country] on;
insert into [country] ([country_id], [name], [alpha_two_country_code], [alpha_three_country_code], [iban_length], [is_sepa]) values (1, 'Nederland', 'NL', 'NLD', 18, 1);
set identity_insert [country] off;
set identity_insert [country] on;
insert into [country] ([country_id], [name], [alpha_two_country_code], [alpha_three_country_code], [iban_length], [is_sepa]) values (2, 'België', 'BE', 'BEL', 16, 1);
set identity_insert [country] off;
set identity_insert [country] on;
insert into [country] ([country_id], [name], [alpha_two_country_code], [alpha_three_country_code], [iban_length], [is_sepa]) values (3, 'Duitsland', 'DE', 'DEU', 22, 1);
set identity_insert [country] off;
set identity_insert [country] on;
insert into [country] ([country_id], [name], [alpha_two_country_code], [alpha_three_country_code], [iban_length], [is_sepa]) values (4, 'Albanië', 'AL', 'ALB', 28, 0);
set identity_insert [country] off;
-- Enable triggers
alter table [company] enable trigger all;
alter table [company_bank_account] enable trigger all;
alter table [company_bank_account_mandate] enable trigger all;
alter table [country] enable trigger all;
alter table [sbi_code] enable trigger all;
end try
begin catch
set @tsf_error_msg = error_message();
raiserror (@tsf_error_msg, 16, 1);
end catch
Mock data with order number:
Unit test code for the creation of the table. Here country comes first then company. This does not give an error because the company can now find the referred country:
-- Transaction for unit test execution
begin transaction;
declare @tsf_error_msg nvarchar(max);
begin try
-- Canary table to check if the transaction is unintentionally committed
create table unit_test_canary(unit_test_canary char(1));
-- Disable triggers
alter table [company] disable trigger all;
alter table [company_bank_account] disable trigger all;
alter table [company_bank_account_mandate] disable trigger all;
alter table [country] disable trigger all;
alter table [sbi_code] disable trigger all;
exec tsf_mock_tab 'company';
exec tsf_mock_tab 'company_bank_account';
exec tsf_mock_tab 'company_bank_account_mandate';
exec tsf_mock_tab 'country';
exec tsf_mock_tab 'sbi_code';
set identity_insert [country] on;
insert into [country] ([country_id], [name], [alpha_two_country_code], [alpha_three_country_code], [iban_length], [is_sepa]) values (1, 'Nederland', 'NL', 'NLD', 18, 1);
set identity_insert [country] off;
set identity_insert [country] on;
insert into [country] ([country_id], [name], [alpha_two_country_code], [alpha_three_country_code], [iban_length], [is_sepa]) values (2, 'België', 'BE', 'BEL', 16, 1);
set identity_insert [country] off;
set identity_insert [country] on;
insert into [country] ([country_id], [name], [alpha_two_country_code], [alpha_three_country_code], [iban_length], [is_sepa]) values (3, 'Duitsland', 'DE', 'DEU', 22, 1);
set identity_insert [country] off;
set identity_insert [country] on;
insert into [country] ([country_id], [name], [alpha_two_country_code], [alpha_three_country_code], [iban_length], [is_sepa]) values (4, 'Albanië', 'AL', 'ALB', 28, 0);
set identity_insert [country] off;
set identity_insert [company] on;
insert into [company] ([company_id], [company_code], [company_name], [kvk_code], [legal_form], [sbi_code_id], [rsin_number], [visit_country_id], [visit_postal_code], [visit_house_number], [visit_street_name], [visit_city], [postal_is_visit_address], [postal_country_id], [postal_postal_code], [postal_house_number], [postal_street_name], [postal_city]) values (1, 'C10001', 'Advanced Programs', '33272180', 1, 1, '12345678', (
select c.country_id
from country c
where c.alpha_three_country_code = 'NLD'
), '1000AA', 1, 'Straatnaam', 'Amsterdam', 1, (
select c.country_id
from country c
where c.alpha_three_country_code = 'NLD'
), '1000AA', 1, 'Straatnaam', 'Amsterdam');
set identity_insert [company] off;
set identity_insert [company_bank_account] on;
insert into [company_bank_account] ([company_id], [company_bank_account_id], [bank_account_number], [bank_account_is_sepa], [country_id]) values (1, 1, 'NL39ABNA8234998285', 1, (
select c.country_id
from country c
where c.alpha_three_country_code = 'NLD'
));
set identity_insert [company_bank_account] off;
set identity_insert [company_bank_account] on;
insert into [company_bank_account] ([company_id], [company_bank_account_id], [bank_account_number], [bank_account_is_sepa], [start_date_bank_account], [end_date_bank_account], [country_id]) values (1, 2, 'NL08INGB4026824314', 1, dateadd(day, -10, cast(getdate() as date))
, dateadd(day, -5, cast(getdate() as date)), (
select c.country_id
from country c
where c.alpha_three_country_code = 'NLD'
)
);
set identity_insert [company_bank_account] off;
set identity_insert [company_bank_account_mandate] on;
insert into [company_bank_account_mandate] ([company_id], [company_bank_account_id], [company_bank_account_mandate_id], [company_bank_account_mandate_code], [start_date_mandate]) values (1, 1, 1, '23000001', dateadd(day, -2, cast(getdate() as date)));
set identity_insert [company_bank_account_mandate] off;
set identity_insert [sbi_code] on;
insert into [sbi_code] ([sbi_code_id], [section_code], [description_section], [department_code], [sbi_code], [description_sbi]) values (1, 'SC-1', 'Omschrijving', 'DP-01', 'SBI_CODE', 'Omschrijving');
set identity_insert [sbi_code] off;
-- Enable triggers
alter table [company] enable trigger all;
alter table [company_bank_account] enable trigger all;
alter table [company_bank_account_mandate] enable trigger all;
alter table [country] enable trigger all;
alter table [sbi_code] enable trigger all;
end try
begin catch
set @tsf_error_msg = error_message();
raiserror (@tsf_error_msg, 16, 1);
end catch
+23Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.