CREATE TABLE [dbo].[acc_trn_bordereaux]
(
[trnb_bordereau_id] [int] NOT NULL,
[trnb_payment_code] [dbo].[ais_code_short] NOT NULL,
[trnb_branch_id] [int] NOT NULL,
[trnb_currency_code] [dbo].[ais_code_short] NOT NULL,
[trnb_date_from] [date] NOT NULL,
[trnb_date_to] [date] NOT NULL,
[trnb_user_code] [dbo].[ais_code_medium] NOT NULL,
[trnb_operation_utc_date] [datetime] NOT NULL,
[trnb_bank_date] [date] NULL,
[trnb_obs] [nvarchar] (64) COLLATE French_CI_AS NULL,
[trnb_tmp] [bit] NOT NULL CONSTRAINT [DF__acc_trn_bordereaux__trnb_tmp] DEFAULT ((0)),
[trnb_reference] [varchar] (50) COLLATE French_CI_AS NULL,
[trnb_amount_db] [dbo].[ais_decimal2] NULL,
[trnb_amount_cr] [dbo].[ais_decimal2] NULL,
[trnb_db] [bit] NOT NULL CONSTRAINT [DF__acc_trn_bordereaux__trnb_db] DEFAULT ((0)),
[trnb_cr] [bit] NOT NULL CONSTRAINT [DF__acc_trn_bordereaux__trnb_cr] DEFAULT ((0)),
[trnb_sequence_code] [dbo].[ais_code_short] NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[t_acc_trn_bordereaux__d]
   ON  [dbo].[acc_trn_bordereaux]
   AFTER DELETE
AS 
BEGIN

	SET NOCOUNT ON;
	
	/*
	DGE
	Autoriser uniquement la suppression des bordereaux temporaires (trnb_tmp !=0)
	*/
	
	IF EXISTS(
		SELECT	1
		FROM	deleted d
		where	d.trnb_tmp = 0 )
	BEGIN
	
		RAISERROR('Cannot delete a permanent bordereau (trnb_tmp)', 16, 1)
		ROLLBACK TRANSACTION
		RETURN
		
	END
	

END
GO
ALTER TABLE [dbo].[acc_trn_bordereaux] ADD CONSTRAINT [PK_acc_trn_bordereaux] PRIMARY KEY CLUSTERED  ([trnb_bordereau_id]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[acc_trn_bordereaux] ADD CONSTRAINT [FK_acc_trn_bordereaux_acc_currencies] FOREIGN KEY ([trnb_currency_code]) REFERENCES [dbo].[acc_currencies] ([cur_currency_code])
GO
ALTER TABLE [dbo].[acc_trn_bordereaux] ADD CONSTRAINT [FK_acc_trn_bordereaux_act_branches] FOREIGN KEY ([trnb_branch_id]) REFERENCES [dbo].[act_branches] ([br_actor_id])
GO
ALTER TABLE [dbo].[acc_trn_bordereaux] ADD CONSTRAINT [FK_acc_trn_bordereaux_nm_payment_modes] FOREIGN KEY ([trnb_payment_code]) REFERENCES [dbo].[nm_payment_modes] ([pm_payment_code])
GO
ALTER TABLE [dbo].[acc_trn_bordereaux] ADD CONSTRAINT [FK_acc_trn_bordereaux_sq_sequences] FOREIGN KEY ([trnb_sequence_code]) REFERENCES [dbo].[sq_sequences] ([seq_sequence_code])
GO
ALTER TABLE [dbo].[acc_trn_bordereaux] ADD CONSTRAINT [FK_acc_trn_bordereaux_usr_users] FOREIGN KEY ([trnb_user_code]) REFERENCES [dbo].[usr_users] ([usr_user_code])
GO
