CREATE TABLE "PROJECT_STRUCTURE"
  ("MAIN_PROJECT" VARCHAR(18) Not Null,
   "SUB_PROJECT" VARCHAR(18) Not Null
);

GO

ALTER TABLE "PROJECT_STRUCTURE" ADD PRIMARY KEY ("MAIN_PROJECT", "SUB_PROJECT");

GO

CREATE UNIQUE INDEX IDX_DT861_DI023278 ON DT861 (DI023119, DI023062, DI023278);

GO

create PROCEDURE P_INSERT_PROJECT_STRUC
	@p_project_id nvarchar(100)
AS
BEGIN
      if @p_project_id is not Null
		  with n(di001001, di001003) AS
			(select di001001, di001003
			 from dt461
			 where di001003=@p_project_id
				union all
			 select parent.di001001, parent.di001003
			 from dt461 as parent, n
			 where n.di001001 = parent.di001003)
		  insert into project_structure
			 select @p_project_id, di001001 from n
			   union all
			 select @p_project_id, @p_project_id
END;

GO

create TRIGGER PLANTA_ON_DELETE_DT461
ON DT461
AFTER DELETE AS
DECLARE @pr_id as nvarchar(100)
BEGIN
    BEGIN TRANSACTION
	select @pr_id=DI001001 from DELETED;
    delete from project_structure
        where sub_project = @pr_id
        or main_project = @pr_id;
	COMMIT TRANSACTION
END;

GO

CREATE TRIGGER PLANTA_ON_INSERT_DT461
ON DT461
AFTER INSERT AS
DECLARE @pr_id as nvarchar(100)
BEGIN
    select @pr_id=DI001001 from INSERTED;
	exec p_insert_project_struc @p_project_id = @pr_id;
END;

GO

CREATE TRIGGER PLANTA_ON_UPDATE_DT461
ON DT461
AFTER UPDATE AS
DECLARE
@new_main_pr_id as nvarchar(100),
@pr_id as nvarchar(100),
@sub_pr_id as nvarchar(100),
@get_pr_children CURSOR
BEGIN
	select @new_main_pr_id=DI001003 from INSERTED;
	select @pr_id=DI001001 from INSERTED;
    
	BEGIN TRANSACTION
		-- reset project structure if parent changes
		delete from project_structure
			where sub_project in (select DI001001 from DT461 where DI001052 = @new_main_pr_id)
			or main_project in (select DI001001 from DT461 where DI001052 = @new_main_pr_id);
    COMMIT TRANSACTION
	BEGIN TRANSACTION
		-- loop through children of new main project and create new structure records for all of them
		SET @get_pr_children = CURSOR FOR
		SELECT di001001
		FROM   dt461
		WHERE di001052=@new_main_pr_id

		OPEN @get_pr_children
		FETCH NEXT
		FROM @get_pr_children INTO @sub_pr_id
		WHILE @@FETCH_STATUS = 0
		BEGIN
			exec p_insert_project_struc @p_project_id = @sub_pr_id;
			FETCH NEXT
			FROM @get_pr_children INTO @sub_pr_id
		END
		CLOSE @get_pr_children
		DEALLOCATE @get_pr_children
	COMMIT TRANSACTION
END;

GO
