CREATE TABLE [dbo].[int_retrocessions_years]
(
[irety_retro_code] [dbo].[ais_code_short] NOT NULL,
[irety_year] [smallint] NOT NULL,
[irety_value] [dbo].[ais_decimal2] NULL,
[irety_fixed_value] [bit] NOT NULL CONSTRAINT [DF__int_retrocessions_years__irety_fixed_value] DEFAULT ((0)),
[irety_renew_only] [bit] NOT NULL CONSTRAINT [DF__int_retrocessions_years__irety_renew_only] DEFAULT ((0)),
[irety_broker_cost_value] [dbo].[ais_decimal2] NULL,
[irety_broker_cost_fixed_value] [bit] NOT NULL CONSTRAINT [DF__int_retrocessions_years__irety_broker_cost_fixed_value] DEFAULT ((0)),
[irety_broker_fee_value] [dbo].[ais_decimal2] NULL,
[irety_broker_fee_fixed_value] [bit] NOT NULL CONSTRAINT [DF__int_retrocessions_years__irety_broker_fee_fixed_value] DEFAULT ((0))
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER [dbo].[t_int_retrocessions_years_d]
on [dbo].[int_retrocessions_years] AFTER DELETE
AS 
BEGIN
	SET NOCOUNT ON;

	if exists(	SELECT 1 
				from deleted d
				where EXISTS(
								SELECT 1 
								from int_retrocessions_years t 
								where		d.irety_retro_code = t.irety_retro_code
										and d.irety_year < t.irety_year -- de ex: daca sterg anul 3, trebuie sa nu existe anii 4,5,etc
								)
			)
	BEGIN
		RAISERROR (N'You must delete years in descending order in table %s', 10, 1, 'int_retrocessions_years')
	end  
								

END

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER [dbo].[t_int_retrocessions_years_i_m]
on [dbo].[int_retrocessions_years] AFTER INSERT,UPDATE
AS 
BEGIN
	SET NOCOUNT ON;

	if exists(	SELECT 1 
				from inserted i 
				where		i.irety_year > 2 
						and not EXISTS(
										SELECT 1 
										from int_retrocessions_years t 
										where		i.irety_retro_code = t.irety_retro_code
												and i.irety_year - 1 = t.irety_year -- de ex: daca inserez anul 3, trebuie sa existe anul 2
										)
			)
	BEGIN
		RAISERROR (N'The years must be consecutive in table %s', 10, 1, 'int_retrocessions_years')
	end  
								

END

GO
ALTER TABLE [dbo].[int_retrocessions_years] ADD CONSTRAINT [CK_int_retrocessions_years_irety_year] CHECK (([irety_year]>=(2)))
GO
ALTER TABLE [dbo].[int_retrocessions_years] ADD CONSTRAINT [PK_int_retrocessions_years] PRIMARY KEY CLUSTERED  ([irety_retro_code], [irety_year]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[int_retrocessions_years] ADD CONSTRAINT [FK_int_retrocessions_years_int_retrocessions] FOREIGN KEY ([irety_retro_code]) REFERENCES [dbo].[int_retrocessions] ([iret_retro_code]) ON DELETE CASCADE
GO
