CREATE TABLE [dbo].[pr_products_documents]
(
[prdd_product_code] [dbo].[ais_code_short] NOT NULL,
[prdd_branch_id] [int] NOT NULL,
[prdd_act_code] [dbo].[ais_code_short] NOT NULL,
[prdd_document_order] [smallint] NOT NULL,
[prdd_document_relative_path] [varchar] (512) COLLATE French_CI_AS NOT NULL,
[prdd_output_type_code] [dbo].[ais_code_short] NOT NULL CONSTRAINT [DF_pr_products_documents_prdd_output_type_code] DEFAULT ('PDF'),
[prdd_rowver] [timestamp] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[pr_products_documents] ADD CONSTRAINT [IX_pr_products_documents] UNIQUE NONCLUSTERED  ([prdd_product_code], [prdd_act_code], [prdd_document_relative_path], [prdd_branch_id]) ON [PRIMARY]

ALTER TABLE [dbo].[pr_products_documents] ADD CONSTRAINT [PK_pr_products_documents] PRIMARY KEY NONCLUSTERED  ([prdd_product_code], [prdd_branch_id], [prdd_act_code], [prdd_document_order]) ON [PRIMARY]

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

--create trigger order
CREATE TRIGGER [dbo].[t_pr_products_documents_ins]
   ON  [dbo].[pr_products_documents]
   AFTER INSERT
AS 
BEGIN
	SET NOCOUNT ON;

	declare @product_code ais_code_short
	declare @act_code ais_code_short
	declare @order smallint
	declare @new_order smallint

	DECLARE doc_cursor INSENSITIVE CURSOR FOR 
	select prdd_product_code, prdd_act_code, prdd_document_order
	from INSERTED
	where prdd_document_order < 0

	OPEN doc_cursor

	FETCH NEXT FROM doc_cursor INTO @product_code, @act_code,@order

	WHILE @@FETCH_STATUS = 0
	BEGIN

		select @new_order = max(prdd_document_order)
		from pr_products_documents
		where		prdd_product_code = @product_code
				and prdd_act_code = @act_code 
				and prdd_document_order >= 0

		set @new_order = isnull(@new_order,0) + 1

		update pr_products_documents
			set prdd_document_order = @new_order
		where		prdd_product_code = @product_code
				and prdd_act_code = @act_code 
				and prdd_document_order = @order

		FETCH NEXT FROM doc_cursor INTO @product_code, @act_code,@order

	END 
	CLOSE doc_cursor;
	DEALLOCATE doc_cursor;



END
GO

ALTER TABLE [dbo].[pr_products_documents] ADD
CONSTRAINT [FK_pr_products_documents_pol_acts] FOREIGN KEY ([prdd_act_code]) REFERENCES [dbo].[pol_acts] ([pact_act_code])
GO






ALTER TABLE [dbo].[pr_products_documents] ADD
CONSTRAINT [FK_pr_products_documents_pr_products_branches] FOREIGN KEY ([prdd_product_code], [prdd_branch_id]) REFERENCES [dbo].[pr_products_branches] ([prdb_product_code], [prdb_branch_id])

GO






ALTER TABLE [dbo].[pr_products_documents] ADD
CONSTRAINT [FK_pr_products_documents_nm_documents_output_types] FOREIGN KEY ([prdd_output_type_code]) REFERENCES [dbo].[nm_documents_output_types] ([nmdot_output_type_code])
GO
