CREATE TABLE [dbo].[md_fields_cpl]
(
[fld_table_name] [dbo].[ais_name] NOT NULL,
[fld_field_name] [dbo].[ais_name] NOT NULL,
[fld_rowver] [timestamp] NOT NULL,
[fld_l1] [nvarchar] (64) COLLATE French_CI_AS NOT NULL,
[fld_l2] [nvarchar] (64) COLLATE French_CI_AS NULL,
[fld_l3] [nvarchar] (64) COLLATE French_CI_AS NULL,
[fld_dsc_l1] [nvarchar] (64) COLLATE French_CI_AS NULL,
[fld_dsc_l2] [nvarchar] (64) COLLATE French_CI_AS NULL,
[fld_dsc_l3] [nvarchar] (64) COLLATE French_CI_AS NULL,
[fld_type] [dbo].[ais_name] NOT NULL,
[fld_size] [smallint] NOT NULL,
[fld_nullable] [bit] NOT NULL CONSTRAINT [DF__md_fields_cpl__fld_nullable] DEFAULT ((0)),
[fld_read_only] [bit] NOT NULL CONSTRAINT [DF__md_fields_cpl__fld_read_only] DEFAULT ((0)),
[fld_virtual] [bit] NOT NULL CONSTRAINT [DF__md_fields_cpl__fld_virtual] DEFAULT ((0)),
[fld_mask_code] [dbo].[ais_code_short] NULL,
[fld_default] [nvarchar] (64) COLLATE French_CI_AS NULL,
[fld_link_table_name] [dbo].[ais_name] NULL,
[fld_pk] [bit] NOT NULL CONSTRAINT [DF__md_fields_cpl__fld_pk] DEFAULT ((0)),
[fld_ordinal_position] [int] NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER [dbo].[t_md_fields_cpl__iu]
   ON  [dbo].[md_fields_cpl]
   AFTER INSERT,UPDATE
AS 
BEGIN

	/*
	DGE 06/05/2015
	0000272: Validation des champs de compléments

	Vérifier que les champs insérés contient uniquement _ 0..9 A..Z a..z
	*/

	SET NOCOUNT ON;


	if exists	(
				select	1
				from	inserted i
				where	dbo.f_verify_name(i.fld_field_name) = 0
				)

	begin

		RAISERROR('Incorrect value for field name', 16, 1)
		ROLLBACK TRANSACTION
		RETURN		
	end






END
GO

ALTER TABLE [dbo].[md_fields_cpl] ADD CONSTRAINT [PK_md_fields_cpl] PRIMARY KEY NONCLUSTERED  ([fld_table_name], [fld_field_name]) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_md_fields_cpl_fld_type] ON [dbo].[md_fields_cpl] ([fld_table_name]) ON [PRIMARY]

ALTER TABLE [dbo].[md_fields_cpl] ADD
CONSTRAINT [CK_md_fields_cpl_varchar_linked_table] CHECK ((len(isnull([fld_link_table_name],''))=(0) OR len(isnull([fld_link_table_name],''))>(0) AND [fld_type]='varchar' AND [fld_size]=(7)))
ALTER TABLE [dbo].[md_fields_cpl] ADD
CONSTRAINT [CK__md_fields_cpl_fld_field_name__string_pk] CHECK ((len(ltrim(rtrim([fld_field_name])))>(0) AND charindex('*',[fld_field_name])=(0) AND charindex('%',[fld_field_name])=(0)))

ALTER TABLE [dbo].[md_fields_cpl] WITH NOCHECK ADD
CONSTRAINT [FK_md_fields_cpl_md_link_tables] FOREIGN KEY ([fld_link_table_name]) REFERENCES [dbo].[md_link_tables] ([ltbl_link_table_name]) ON UPDATE CASCADE




ALTER TABLE [dbo].[md_fields_cpl] ADD
CONSTRAINT [FK_md_fields_cpl_md_types] FOREIGN KEY ([fld_type]) REFERENCES [dbo].[md_types] ([typ_type_name])


GO

ALTER TABLE [dbo].[md_fields_cpl] ADD CONSTRAINT [FK_md_fields_cpl_md_masks] FOREIGN KEY ([fld_mask_code]) REFERENCES [dbo].[md_masks] ([msk_mask_code])
GO
ALTER TABLE [dbo].[md_fields_cpl] WITH NOCHECK ADD CONSTRAINT [FK_md_fields_cpl_md_tables] FOREIGN KEY ([fld_table_name]) REFERENCES [dbo].[md_tables] ([tbl_table_name]) ON DELETE CASCADE
GO
