CREATE TABLE [dbo].[acc_bordereaux]
(
[bdr_bordereau_id] [int] NOT NULL,
[bdr_operation_code] [dbo].[ais_code_short] NOT NULL,
[bdr_option] [tinyint] NOT NULL,
[bdr_db_date__from] [date] NULL,
[bdr_db_date__to] [date] NULL,
[bdr_start_date__from] [date] NULL,
[bdr_start_date__to] [date] NULL,
[bdr_ec_date__from] [date] NULL,
[bdr_ec_date__to] [date] NULL,
[bdr_user_code] [dbo].[ais_code_medium] NOT NULL,
[bdr_obs] [nvarchar] (64) COLLATE French_CI_AS NULL,
[bdr_tf] [dbo].[ais_decimal2] NULL,
[bdr_taxes] [dbo].[ais_decimal2] NULL,
[bdr_taxes_broker] [dbo].[ais_decimal2] NULL,
[bdr_ti] [dbo].[ais_decimal2] NULL,
[bdr_comm] [dbo].[ais_decimal2] NULL,
[bdr_comm_taxes] [dbo].[ais_decimal2] NULL,
[bdr_comm_taxes_broker] [dbo].[ais_decimal2] NULL,
[bdr_sequence_code] [dbo].[ais_code_short] NULL,
[bdr_reference] [nvarchar] (50) COLLATE French_CI_AS NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[t_acc_bordereaux__d]
   ON  [dbo].[acc_bordereaux]
   AFTER DELETE
AS 
BEGIN

	SET NOCOUNT ON;
	
	/*
	DGE - interdire la suppression des bordereaux avec opération code != IS
	*/
	
	IF EXISTS(
		SELECT	1
		FROM	deleted
		WHERE	bdr_operation_code != 'IS')
	BEGIN
	
		RAISERROR('Cannot delete a bordereau with an operation code != IS', 16, 1)
		ROLLBACK TRANSACTION
		RETURN
		
	END
	

END
GO
ALTER TABLE [dbo].[acc_bordereaux] ADD CONSTRAINT [CK_acc_bordereaux_bdr_option] CHECK (([bdr_option]=(2) OR [bdr_option]=(1) OR [bdr_option]=(0)))
GO
ALTER TABLE [dbo].[acc_bordereaux] ADD CONSTRAINT [PK_acc_bordereaux_1] PRIMARY KEY CLUSTERED  ([bdr_bordereau_id]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[acc_bordereaux] ADD CONSTRAINT [FK_acc_bordereaux_acc_accounts] FOREIGN KEY ([bdr_bordereau_id]) REFERENCES [dbo].[acc_accounts] ([a_account_id])
GO
ALTER TABLE [dbo].[acc_bordereaux] ADD CONSTRAINT [FK_acc_bordereaux_nm_accounts_operations] FOREIGN KEY ([bdr_operation_code]) REFERENCES [dbo].[nm_accounts_operations] ([aco_operation_code])
GO
ALTER TABLE [dbo].[acc_bordereaux] ADD CONSTRAINT [FK_acc_bordereaux_sq_sequences] FOREIGN KEY ([bdr_sequence_code]) REFERENCES [dbo].[sq_sequences] ([seq_sequence_code])
GO
ALTER TABLE [dbo].[acc_bordereaux] ADD CONSTRAINT [FK_acc_bordereaux_usr_users] FOREIGN KEY ([bdr_user_code]) REFERENCES [dbo].[usr_users] ([usr_user_code])
GO
