CREATE TABLE [dbo].[acc_cts_bordereaux]
(
[ctsb_bordereau_id] [int] NOT NULL,
[ctsb_type_code] [dbo].[ais_code_short] NOT NULL,
[ctsb_branch_id] [int] NOT NULL,
[ctsb_user_code] [dbo].[ais_code_medium] NOT NULL,
[ctsb_operation_utc_date] [datetime] NOT NULL,
[ctsb_reference_date] [date] NOT NULL,
[ctsb_obs] [nvarchar] (64) COLLATE French_CI_AS NULL,
[ctsb_currency_code] [dbo].[ais_code_short] NOT NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[t_acc_cts_bordereaux__d]
   ON  [dbo].[acc_cts_bordereaux]
   AFTER DELETE
AS 
BEGIN

	SET NOCOUNT ON;
	
	/*
	DGE
	Autoriser uniquement la suppression des bordereaux de type PN et PR ( cts_acc_types.ctstyp_mark_item = false ).
	La suppression des autres bordereaux est interdite, car des modifications sont faites au niveau des clients/contrats/quittances.
	Voir le contenu de la table cts_acc_types.
	*/
	
	IF EXISTS(
		SELECT	1
		FROM	deleted d
				inner join acc_cts_types act on act.ctstyp_type_code = d.ctsb_type_code and act.ctstyp_mark_item != 0)
	BEGIN
	
		RAISERROR('Cannot delete a bordereau with type <<mark item>>', 16, 1)
		ROLLBACK TRANSACTION
		RETURN
		
	END
	

END
GO

ALTER TABLE [dbo].[acc_cts_bordereaux] ADD 
CONSTRAINT [PK_acc_cts_bordereaux] PRIMARY KEY CLUSTERED  ([ctsb_bordereau_id]) ON [PRIMARY]
ALTER TABLE [dbo].[acc_cts_bordereaux] ADD
CONSTRAINT [FK_acc_cts_bordereaux_acc_currencies] FOREIGN KEY ([ctsb_currency_code]) REFERENCES [dbo].[acc_currencies] ([cur_currency_code])

GO



ALTER TABLE [dbo].[acc_cts_bordereaux] ADD
CONSTRAINT [FK_acc_cts_bordereaux_acc_cts_types] FOREIGN KEY ([ctsb_type_code]) REFERENCES [dbo].[acc_cts_types] ([ctstyp_type_code])
GO

ALTER TABLE [dbo].[acc_cts_bordereaux] ADD CONSTRAINT [FK_acc_cts_bordereaux_act_branches] FOREIGN KEY ([ctsb_branch_id]) REFERENCES [dbo].[act_branches] ([br_actor_id])
GO

ALTER TABLE [dbo].[acc_cts_bordereaux] ADD CONSTRAINT [FK_acc_cts_bordereaux_usr_users] FOREIGN KEY ([ctsb_user_code]) REFERENCES [dbo].[usr_users] ([usr_user_code])
GO
