CREATE TABLE [dbo].[acc_advances]
(
[adv_advance_id] [int] NOT NULL,
[adv_policy_id] [int] NULL,
[adv_obs] [nvarchar] (64) COLLATE French_CI_AS NULL,
[adv_lettering] [int] NOT NULL,
[adv_taken] [bit] NOT NULL CONSTRAINT [DF__acc_advances__adv_taken] DEFAULT ((0))
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[t_acc_advances__d]
   ON  [dbo].[acc_advances]
   AFTER DELETE
AS 
BEGIN

	SET NOCOUNT ON;
	
	/*
	DGE - interdire la suppression des acomptes repris (adv_taken != 0)

	Explications:
	L'annulation des transactions nous oblige parfois de supprimer des acomptes. 
	On interdit la suppression des acomptes repris pour ne pas créer des incohérences.

	Exemple:	1	: création acompte			: T0 100 EUR, A 100 EUR.
				2	; remboursement acompte		: T1 - 100 EUR, A.taken = 1
				
	On bloque la suppression de la transaction T0. 
	Il faut d'abord supprmer T1 ce qui va remettre A.taken = 0 et ensuite supprimer T0. 

	*/
	
	IF EXISTS(
		SELECT	1
		FROM	deleted
		WHERE	adv_taken != 0)
	BEGIN
	
		RAISERROR('Cannot delete a taken advance', 16, 1)
		ROLLBACK TRANSACTION
		RETURN
		
	END
	

END
GO
ALTER TABLE [dbo].[acc_advances] ADD CONSTRAINT [PK_acc_advances] PRIMARY KEY CLUSTERED  ([adv_advance_id]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[acc_advances] ADD CONSTRAINT [FK_acc_advances_acc_accounts] FOREIGN KEY ([adv_advance_id]) REFERENCES [dbo].[acc_accounts] ([a_account_id])
GO
