CREATE TRIGGER tg_add_TR ON dbo.COMMUN_TACHESREC
FOR INSERT 
AS

--idappli,horaire,IDCriticite,Titre,detail,code_duree,IDType_Niv1, IDType_Niv2,ActionPB, URL_Procedure
-- Variables
SET DATEFORMAT MDY
declare @Id_TR varchar(10)
declare @Grp_Interv as varchar(10)
declare @count integer
declare @jour as varchar(10)
declare @idappli as integer
declare @horaire as varchar(10)
declare @idcriticite as integer
declare @titre as nvarchar(125)
declare @detail nvarchar(16)
declare @code_duree as integer
declare @IDTYPE_NIV1 as integer
declare @IDTYPE_NIV2 as integer
declare @ActionPB nvarchar(16)
declare @URL_PROCEDURE as nvarchar(300)


--Variable inserées
set @Id_TR=(select id from inserted)
set @Grp_Interv=(select GRPINTERV from inserted)
set @idappli=(select idappli from inserted)
set @horaire=(select horaire from inserted)
set @idcriticite=(select idcriticite from inserted)
set @titre=(select titre from inserted)
set @detail=(select detail from inserted)
set @code_duree=(select code_duree from inserted)
set @IDTYPE_NIV1=(select IDTYPE_NIV1 from inserted)
set @IDTYPE_NIV2=(select IDTYPE_NIV2 from inserted)
set @ActionPB=(select ActionPB from inserted)
set @URL_PROCEDURE=(select URL_PROCEDURE from inserted)

-- Verif TR hebdo
set @count = 1
while (@count <6)
begin
  set @jour=DatePart(dw,getdate()+@count)
  -- Lundi
  if (select lundi from COMMUN_TACHESREC where id=@Id_TR)=1
    if @jour=1
    insert into HISTO_TAC (IDTACHESREC,DT_CRE,GRPINTERV,RAS,PB,DPL,idappli,horaire,idcriticite,titre,detail,code_duree,IDType_Niv1, IDType_Niv2,ActionPB, URL_Procedure) VALUES (@Id_TR,convert(varchar(10),getdate()+@count,120),@Grp_Interv,0,0,0,@idappli,@horaire,@idcriticite,@titre,@detail,@code_duree,@IDTYPE_NIV1,@IDTYPE_NIV2,@ActionPB,@URL_PROCEDURE)
  -- Mardi
  if (select mardi from COMMUN_TACHESREC where id=@Id_TR)=1
    if @jour=2
    insert into HISTO_TAC (IDTACHESREC,DT_CRE,GRPINTERV,RAS,PB,DPL,idappli,horaire,idcriticite,titre,detail,code_duree,IDType_Niv1, IDType_Niv2,ActionPB, URL_Procedure) VALUES (@Id_TR,convert(varchar(10),getdate()+@count,120),@Grp_Interv,0,0,0,@idappli,@horaire,@idcriticite,@titre,@detail,@code_duree,@IDTYPE_NIV1,@IDTYPE_NIV2,@ActionPB,@URL_PROCEDURE)
  -- Mercredi
  if (select mercredi from COMMUN_TACHESREC where id=@Id_TR)=1
    if @jour=3
    insert into HISTO_TAC (IDTACHESREC,DT_CRE,GRPINTERV,RAS,PB,DPL,idappli,horaire,idcriticite,titre,detail,code_duree,IDType_Niv1, IDType_Niv2,ActionPB, URL_Procedure) VALUES (@Id_TR,convert(varchar(10),getdate()+@count,120),@Grp_Interv,0,0,0,@idappli,@horaire,@idcriticite,@titre,@detail,@code_duree,@IDTYPE_NIV1,@IDTYPE_NIV2,@ActionPB,@URL_PROCEDURE)
  -- Jeudi
  if (select jeudi from COMMUN_TACHESREC where id=@Id_TR)=1
    if @jour=4
    insert into HISTO_TAC (IDTACHESREC,DT_CRE,GRPINTERV,RAS,PB,DPL,idappli,horaire,idcriticite,titre,detail,code_duree,IDType_Niv1, IDType_Niv2,ActionPB, URL_Procedure) VALUES (@Id_TR,convert(varchar(10),getdate()+@count,120),@Grp_Interv,0,0,0,@idappli,@horaire,@idcriticite,@titre,@detail,@code_duree,@IDTYPE_NIV1,@IDTYPE_NIV2,@ActionPB,@URL_PROCEDURE)
  -- Vendredi
  if (select vendredi from COMMUN_TACHESREC where id=@Id_TR)=1
    if @jour=5
    insert into HISTO_TAC (IDTACHESREC,DT_CRE,GRPINTERV,RAS,PB,DPL,idappli,horaire,idcriticite,titre,detail,code_duree,IDType_Niv1, IDType_Niv2,ActionPB, URL_Procedure) VALUES (@Id_TR,convert(varchar(10),getdate()+@count,120),@Grp_Interv,0,0,0,@idappli,@horaire,@idcriticite,@titre,@detail,@code_duree,@IDTYPE_NIV1,@IDTYPE_NIV2,@ActionPB,@URL_PROCEDURE)
  -- Samedi
  if (select samedi from COMMUN_TACHESREC where id=@Id_TR)=1
    if @jour=6
    insert into HISTO_TAC (IDTACHESREC,DT_CRE,GRPINTERV,RAS,PB,DPL,idappli,horaire,idcriticite,titre,detail,code_duree,IDType_Niv1, IDType_Niv2,ActionPB, URL_Procedure) VALUES (@Id_TR,convert(varchar(10),getdate()+@count,120),@Grp_Interv,0,0,0,@idappli,@horaire,@idcriticite,@titre,@detail,@code_duree,@IDTYPE_NIV1,@IDTYPE_NIV2,@ActionPB,@URL_PROCEDURE)
  -- Dimanche
  if (select dimanche from COMMUN_TACHESREC where id=@Id_TR)=1
    if @jour=7
    insert into HISTO_TAC (IDTACHESREC,DT_CRE,GRPINTERV,RAS,PB,DPL,idappli,horaire,idcriticite,titre,detail,code_duree,IDType_Niv1, IDType_Niv2,ActionPB, URL_Procedure) VALUES (@Id_TR,convert(varchar(10),getdate()+@count,120),@Grp_Interv,0,0,0,@idappli,@horaire,@idcriticite,@titre,@detail,@code_duree,@IDTYPE_NIV1,@IDTYPE_NIV2,@ActionPB,@URL_PROCEDURE)
  set @count = @count + 1
 END

-- Verif TR mensuelle
set @count = 1
while (@count < 6)
begin
  set @jour=DatePart(d,getdate()+@count)
  if (select count(id) from COMMUN_TACHESREC where id=@Id_TR AND (date_mensuelle like @jour+',%' or date_mensuelle like '%,'+@jour+',%' or date_mensuelle like '%,'+@jour)) != 0
    insert into HISTO_TAC (IDTACHESREC,DT_CRE,GRPINTERV,RAS,PB,DPL,idappli,horaire,idcriticite,titre,detail,code_duree,IDType_Niv1, IDType_Niv2,ActionPB, URL_Procedure) VALUES (@Id_TR,convert(varchar(10),getdate()+@count,120),@Grp_Interv,0,0,0,@idappli,@horaire,@idcriticite,@titre,@detail,@code_duree,@IDTYPE_NIV1,@IDTYPE_NIV2,@ActionPB,@URL_PROCEDURE)
  set @count = @count + 1
END