CREATE UNIQUE NONCLUSTERED INDEX [NonClusteredIndex-20180404-215712] ON [dbo].[RetProvActN]
(
	[ActN] ASC,
	[NYear] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
go

create TRIGGER [dbo].[RetProvActN_FOR_INSERT]
   ON  [dbo].[RetProvActN]
   FOR INSERT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @ActN int, @Y int
	set @Y = Year(GetDate())
	Select @ActN=isnull(Max(ActN),0) from RetProvActN where Year(D_Reg)=@Y  

    update RetProvActN SET D_Reg = GetDate(), ActN=i.N, NYear = @Y
    from RetProvActN d join
	(
	select i.ID, i.idVer, @ActN + Row_Number() OVER (ORDER BY i.ID asc) N  
	from RetProvActN d join	
        INSERTED i on d.ID = i.ID
	) i
	
    on d.ID = i.ID

END