asked on

Updating multiple rows in SQL Server and getting error when trigger executes

I need some help.
I have a trigger that needs to execute and update multiple tables based on data entered in the record. Only selected records will be used to update these tables. Also, a change to a record can undo the updates performed in a previous update, so I must use both the inserted and the deleted records as my sources for updating the tables. My trigger works fine when it is one record, but does not work well on multiple records.

Below is the trigger I am using to update the database tables

USE [DEVDB]
GO
/****** Object:  Trigger [tU_UpdateTable]    Script Date: 03/16/2011 10:50:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO


CREATE TRIGGER [tU_UpdateTable] ON [DBO].[DOCTBL]
FOR UPDATE
AS

set nocount on

begin

/***  exit the trigger if document is not a progression item  ***/
If (SELECT class FROM DELETED) <> 'ACT_PROG'
	If (SELECT class FROM INSERTED) <> 'ACT_PROG'
		Begin 
			Return 
		End 

/**** update project status code to project (11) when progression item is updated   ***/

update RMDB.dbo.re_project
set RMDB.dbo.re_project.atv_STATUS = 11 
from DEVDB.DBO.DOCTBL, RMDB.dbo.re_project, inserted
where DEVDB.DBO.DOCTBL.actv_id = RMDB.dbo.re_project.atv_id
and RMDB.dbo.re_project.atv_STATUS in (10, 2)
and DEVDB.DBO.DOCTBL.docnum = inserted.docnum
and DEVDB.DBO.DOCTBL.version = inserted.version
and inserted.class = 'ACT_PROG'
and inserted.subclass <> 'CPREC'
and inserted.subclass in (select subclass from DEVDB.DBO.DOCSUBCLASSES
                 where class = 'ACT_PROG')


/**** update project status date and track order rec date when progression item is added  ***/

declare @taskcnt as int
declare @Deldocdate as datetime
declare @Insdocdate as datetime

select @Deldocdate = coalesce(date1,date2,entrywhen) from deleted
select @Insdocdate = coalesce(date1,date2,entrywhen) from inserted

select @taskcnt = 0

select @taskcnt = count(RMDB.dbo.id_project.atv_id) 
	from DEVDB.DBO.DOCTBL, RMDB.dbo.id_project, inserted
		where DEVDB.DBO.DOCTBL.actv_id = RMDB.dbo.id_project.atv_id 		
		and DEVDB.DBO.DOCTBL.docnum = inserted.docnum
		and DEVDB.DBO.DOCTBL.version = inserted.version

IF @taskcnt = 0
	BEGIN
	insert into RMDB.dbo.id_project  
			(RMDB.dbo.id_project.atv_id )
     select DEVDB.DBO.DOCTBL.actv_id
		from DEVDB.DBO.DOCTBL, RMDB.dbo.re_project, inserted
		where DEVDB.DBO.DOCTBL.actv_id = RMDB.dbo.re_project.atv_id
		and DEVDB.DBO.DOCTBL.docnum = inserted.docnum
		and DEVDB.DBO.DOCTBL.version = inserted.version
  	END;

update RMDB.dbo.id_project
  set RMDB.dbo.ID_project.ID_PROJECT_DT = case when
					RMDB.dbo.ID_project.ID_PROJECT_DT is NULL
                    then @Insdocdate
					else RMDB.dbo.ID_project.ID_PROJECT_DT
					end
  , RMDB.dbo.ID_project.ID_TRACK_ORDER_REC =  case when
					deleted.subclass = 'TOL_REC' 
					and RMDB.dbo.ID_project.ID_TRACK_ORDER_REC = @Deldocdate
					then NULL
					when inserted.subclass = 'TOL_REC' 
						and RMDB.dbo.ID_project.ID_TRACK_ORDER_REC is null
					then @Insdocdate
					else RMDB.dbo.ID_project.ID_TRACK_ORDER_REC
					end
from DEVDB.DBO.DOCTBL, RMDB.dbo.ID_project, inserted, deleted
where DEVDB.DBO.DOCTBL.actv_id = RMDB.dbo.ID_project.atv_id
and DEVDB.DBO.DOCTBL.docnum = inserted.docnum
and DEVDB.DBO.DOCTBL.version = inserted.version
and DEVDB.DBO.DOCTBL.docnum = deleted.docnum
and DEVDB.DBO.DOCTBL.version = deleted.version
and inserted.class = 'ACT_PROG'
and inserted.subclass <> 'CPREC'
and inserted.subclass in (select subclass from DEVDB.DBO.DOCSUBCLASSES
                 where class = 'ACT_PROG')


/**** remove old progression item date if progession document class changes ***/
If (SELECT class FROM DELETED) = 'ACT_PROG'
	Begin 

	update RMDB.dbo.ID_RE_TASKS
		set RMDB.dbo.ID_RE_TASKS.ID_PLAN_REQ =  case when 
					deleted.subclass = 'IDPLANREQ' 
					and RMDB.dbo.ID_RE_TASKS.ID_PLAN_REQ = @Deldocdate
					THEN NULL  
					else RMDB.dbo.ID_RE_TASKS.ID_PLAN_REQ
					end
		 , RMDB.dbo.ID_RE_TASKS.ID_PLAN_REC =  case when 
					deleted.subclass = 'IDPLANREC' 
					and RMDB.dbo.ID_RE_TASKS.ID_PLAN_REC = @Deldocdate
					THEN NULL  
					else RMDB.dbo.ID_RE_TASKS.ID_PLAN_REC
					end
		 , RMDB.dbo.ID_RE_TASKS.ID_PLAN_DRAWING = case when 
					deleted.subclass = 'IDPDF' 
					and RMDB.dbo.ID_RE_TASKS.ID_PLAN_DRAWING = @Deldocdate
					THEN NULL  
					else RMDB.dbo.ID_RE_TASKS.ID_PLAN_DRAWING
					end
		 , RMDB.dbo.ID_RE_TASKS.EP_DRAWING  = case when 
					deleted.subclass = 'DC_PLAN' 
					and RMDB.dbo.ID_RE_TASKS.EP_DRAWING = @Deldocdate
					THEN NULL  
					else RMDB.dbo.ID_RE_TASKS.EP_DRAWING 
					end
		 , RMDB.dbo.ID_RE_TASKS.LAND_LEASE_REQ  = case when 
					deleted.subclass = 'LL_REQ' 
					and RMDB.dbo.ID_RE_TASKS.LAND_LEASE_REQ = @Deldocdate
					THEN NULL  
					else RMDB.dbo.ID_RE_TASKS.LAND_LEASE_REQ 
					end
		 , RMDB.dbo.ID_RE_TASKS.LAND_LEASE_REC  = case when 
					deleted.subclass = 'LL_REC' 
					and RMDB.dbo.ID_RE_TASKS.LAND_LEASE_REC = @Deldocdate
					THEN NULL  
					else RMDB.dbo.ID_RE_TASKS.LAND_LEASE_REC
					end
		 , RMDB.dbo.ID_RE_TASKS.LAND_SALES_REQ  = case when 
					deleted.subclass = 'LS_REQ' 
					and RMDB.dbo.ID_RE_TASKS.LAND_SALES_REQ = @Deldocdate
					THEN NULL  
					else RMDB.dbo.ID_RE_TASKS.LAND_SALES_REQ
					end
		 , RMDB.dbo.ID_RE_TASKS.LAND_SALES_REC  = case when 
					deleted.subclass = 'LS_REC' 
					and RMDB.dbo.ID_RE_TASKS.LAND_SALES_REC = @Deldocdate
					THEN NULL  
					else RMDB.dbo.ID_RE_TASKS.LAND_SALES_REC
					end
		from DEVDB.DBO.DOCTBL, RMDB.dbo.ID_RE_TASKS, deleted
		where DEVDB.DBO.DOCTBL.actv_id = RMDB.dbo.ID_RE_TASKS.atv_id 		
		and DEVDB.DBO.DOCTBL.docnum = deleted.docnum
		and DEVDB.DBO.DOCTBL.version = deleted.version



update RMDB.dbo.RE_TASK_LIST
		set RMDB.dbo.RE_TASK_LIST.TSK_DATE_COMPLETED =  case when 
					deleted.subclass = 'ENGREQ' 
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '1' 
					THEN NULL
				WHEN deleted.subclass = 'ENGREC'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '2' 
					THEN NULL
				WHEN deleted.subclass = 'BURS'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '3' 
					THEN NULL
				WHEN deleted.subclass = '11147REQ'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '4' 
					THEN NULL 
				WHEN deleted.subclass = '11147REC'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '5' 
					THEN NULL 
				WHEN deleted.subclass = 'SUPT' 
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '6' 
					THEN NULL 
				WHEN deleted.subclass = 'GM'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '7' 
					THEN NULL 
				WHEN deleted.subclass = 'TPLPROD'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '8' 
					THEN NULL
				WHEN deleted.subclass = 'TPLRTN'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '9' 
					THEN NULL 
				WHEN deleted.subclass = 'FINREQ'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '10' 
					THEN NULL
				WHEN deleted.subclass = 'FINREC'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '11' 
					THEN NULL
				WHEN deleted.subclass = 'AFE/TOL_REQ'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '12'
					THEN null
				WHEN deleted.subclass = 'AFE_REC'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '13' 
					THEN NULL 
				WHEN deleted.subclass = 'SA/TL_REQ'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '14'
					THEN null
				WHEN deleted.subclass = 'SA/TL_REC'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '15' 
					THEN NULL 
				ELSE  RMDB.dbo.RE_TASK_LIST.TSK_DATE_COMPLETED  
				END
		from DEVDB.DBO.DOCTBL, RMDB.dbo.RE_TASK_LIST, deleted
		where DEVDB.DBO.DOCTBL.actv_id = RMDB.dbo.RE_TASK_LIST.atv_id 
		and RMDB.dbo.RE_TASK_LIST.TSK_DATE_COMPLETED = @Deldocdate
		and DEVDB.DBO.DOCTBL.docnum = deleted.docnum
		and DEVDB.DBO.DOCTBL.version = deleted.version

	END  -- END IF
/**** update the progression item date if not already updated ***/

If (SELECT class FROM INSERTED) = 'ACT_PROG'
	Begin 

	select @taskcnt = 0

    select @taskcnt = count(RMDB.dbo.ID_RE_TASKS.atv_id) 
	from DEVDB.DBO.DOCTBL, RMDB.dbo.ID_RE_TASKS, inserted
		where DEVDB.DBO.DOCTBL.actv_id = RMDB.dbo.ID_RE_TASKS.atv_id 		
		and DEVDB.DBO.DOCTBL.docnum = inserted.docnum
		and DEVDB.DBO.DOCTBL.version = inserted.version

	IF @taskcnt = 0
	BEGIN
	insert into RMDB.dbo.ID_RE_TASKS  
			(RMDB.dbo.ID_RE_TASKS.atv_id )
     select DEVDB.DBO.DOCTBL.actv_id
		from DEVDB.DBO.DOCTBL, RMDB.dbo.re_project, inserted
		where DEVDB.DBO.DOCTBL.actv_id = RMDB.dbo.re_project.atv_id
		and DEVDB.DBO.DOCTBL.docnum = inserted.docnum
		and DEVDB.DBO.DOCTBL.version = inserted.version
  	END;

    update RMDB.dbo.ID_RE_TASKS
		set RMDB.dbo.ID_RE_TASKS.ID_PLAN_REQ =  case when 
					inserted.subclass = 'IDPLANREQ' and RMDB.dbo.ID_RE_TASKS.ID_PLAN_REQ is null
					THEN @Insdocdate 
					else RMDB.dbo.ID_RE_TASKS.ID_PLAN_REQ
					end
		 , RMDB.dbo.ID_RE_TASKS.ID_PLAN_REC =  case when 
					inserted.subclass = 'IDPLANREC' and RMDB.dbo.ID_RE_TASKS.ID_PLAN_REC is null
					THEN @Insdocdate 
					else RMDB.dbo.ID_RE_TASKS.ID_PLAN_REC
					end
		 , RMDB.dbo.ID_RE_TASKS.ID_PLAN_DRAWING = case when 
					inserted.subclass = 'IDPDF' and RMDB.dbo.ID_RE_TASKS.ID_PLAN_DRAWING is null
					THEN @Insdocdate  
					else RMDB.dbo.ID_RE_TASKS.ID_PLAN_DRAWING
					end
		 , RMDB.dbo.ID_RE_TASKS.EP_DRAWING  = case when 
					inserted.subclass = 'DC_PLAN' and RMDB.dbo.ID_RE_TASKS.EP_DRAWING  is null
					THEN @Insdocdate  
					else RMDB.dbo.ID_RE_TASKS.EP_DRAWING 
					end
		 , RMDB.dbo.ID_RE_TASKS.LAND_LEASE_REQ  = case when 
					inserted.subclass = 'LL_REQ' and RMDB.dbo.ID_RE_TASKS.LAND_LEASE_REQ  is null
					THEN @Insdocdate  
					else RMDB.dbo.ID_RE_TASKS.LAND_LEASE_REQ 
					end
		 , RMDB.dbo.ID_RE_TASKS.LAND_LEASE_REC  = case when 
					inserted.subclass = 'LL_REC' and RMDB.dbo.ID_RE_TASKS.LAND_LEASE_REC  is null
					THEN @Insdocdate  
					else RMDB.dbo.ID_RE_TASKS.LAND_LEASE_REC
					end
		 , RMDB.dbo.ID_RE_TASKS.LAND_SALES_REQ  = case when 
					inserted.subclass = 'LS_REQ' and RMDB.dbo.ID_RE_TASKS.LAND_SALES_REQ  is null
					THEN @Insdocdate  
					else RMDB.dbo.ID_RE_TASKS.LAND_SALES_REQ
					end
		 , RMDB.dbo.ID_RE_TASKS.LAND_SALES_REC  = case when 
					inserted.subclass = 'LS_REC' and RMDB.dbo.ID_RE_TASKS.LAND_SALES_REC  is null
					THEN @Insdocdate  
					else RMDB.dbo.ID_RE_TASKS.LAND_SALES_REC
					end
		from DEVDB.DBO.DOCTBL, RMDB.dbo.ID_RE_TASKS, inserted
		where DEVDB.DBO.DOCTBL.actv_id = RMDB.dbo.ID_RE_TASKS.atv_id 		
		and DEVDB.DBO.DOCTBL.docnum = inserted.docnum
		and DEVDB.DBO.DOCTBL.version = inserted.version

	select @taskcnt = 0

    select @taskcnt = count(RMDB.dbo.RE_TASK_LIST.atv_id) 
	from DEVDB.DBO.DOCTBL, RMDB.dbo.RE_TASK_LIST, inserted
		where DEVDB.DBO.DOCTBL.actv_id = RMDB.dbo.RE_TASK_LIST.atv_id 		
		and DEVDB.DBO.DOCTBL.docnum = inserted.docnum
		and DEVDB.DBO.DOCTBL.version = inserted.version

	IF @taskcnt = 0
	BEGIN
	insert into RMDB.dbo.RE_TASK_LIST  
			(RMDB.dbo.RE_TASK_LIST.atv_id )
     select DEVDB.DBO.DOCTBL.actv_id
		from DEVDB.DBO.DOCTBL, RMDB.dbo.re_project, inserted
		where DEVDB.DBO.DOCTBL.actv_id = RMDB.dbo.re_project.atv_id
		and DEVDB.DBO.DOCTBL.docnum = inserted.docnum
		and DEVDB.DBO.DOCTBL.version = inserted.version
  	END;

    update RMDB.dbo.RE_TASK_LIST
		set RMDB.dbo.RE_TASK_LIST.TSK_DATE_COMPLETED =  case when 
					inserted.subclass = 'ENGREQ' 
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '1' 
					THEN @Insdocdate 
				WHEN inserted.subclass = 'ENGREC'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '2' 
					THEN @Insdocdate 
				WHEN inserted.subclass = 'BURS'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '3' 
					THEN @Insdocdate 
				WHEN inserted.subclass = '11147REQ'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '4' 
					THEN @Insdocdate 
				WHEN inserted.subclass = '11147REC'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '5' 
					THEN @Insdocdate 
				WHEN inserted.subclass = 'SUPT' 
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '6' 
					THEN @Insdocdate 
				WHEN inserted.subclass = 'GM'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '7' 
					THEN @Insdocdate 
				WHEN inserted.subclass = 'TPLPROD'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '8' 
					THEN @Insdocdate 
				WHEN inserted.subclass = 'TPLRTN'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '9' 
					THEN @Insdocdate 
				WHEN inserted.subclass = 'FINREQ'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '10' 
					THEN @Insdocdate
				WHEN inserted.subclass = 'FINREC'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '11' 
					THEN @Insdocdate 
				WHEN inserted.subclass = 'AFE/TOL_REQ'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '12'
					THEN @Insdocdate
				WHEN inserted.subclass = 'AFE_REC'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '13' 
					THEN @Insdocdate 
				WHEN inserted.subclass = 'SA/TL_REQ'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '14'
					THEN @Insdocdate
				WHEN inserted.subclass = 'SA/TL_REC'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '15' 
					THEN @Insdocdate 
				ELSE  RMDB.dbo.RE_TASK_LIST.TSK_DATE_COMPLETED  
				END
		from DEVDB.DBO.DOCTBL, RMDB.dbo.RE_TASK_LIST, inserted
		where DEVDB.DBO.DOCTBL.actv_id = RMDB.dbo.RE_TASK_LIST.atv_id 
		and RMDB.dbo.RE_TASK_LIST.TSK_DATE_COMPLETED is null
		and DEVDB.DBO.DOCTBL.docnum = inserted.docnum
		and DEVDB.DBO.DOCTBL.version = inserted.version

	END  -- IF 

end

Open in new window

rkharko

I think the problem with current implementation is in lines

select @Deldocdate = coalesce(date1,date2,entrywhen) from deleted
select @Insdocdate = coalesce(date1,date2,entrywhen) from inserted

Open in new window

because the select will return many records but can assign only one. Either use "TOP 1" param in select statement or rethink the overall logic

You should avoid the duplication of CASE.. WHEN statements by moving this part to sql function so the assignment will be

set RMDB.dbo.RE_TASK_LIST.TSK_DATE_COMPLETED = myfunction(inserted.subclass)

Open in new window

Also the safest way to handle multiple updates is to wrap the logic into explicit transaction and use the FOR ...LOOP clause to iterate the inserted records

DECLARE /* variables */

DECLARE insertDataCursor CURSOR FAST_FORWARD READ_ONLY FOR
SELECT * FROM inserted

OPEN insertDataCursor

FETCH NEXT FROM insertDataCursor INTO /* variables */

WHILE @@FETCH_STATUS = 0
BEGIN

/* cursor logic */

FETCH NEXT FROM insertDataCursor INTO /* variables */

END

CLOSE insertDataCursor
DEALLOCATE insertDataCursor

Open in new window

JoeNuvo

When you perform multiple row updated, DELETE and INSERTED table inside trigger will contain multiple rows.
So you have to code it to function with multiple rows table.

Does table [DOCTBL] have IDENTITY field? what is the field name?

nirmalrampk

Hi ,
Try with following change for multi row update

Instead of
-------------------------
IF @taskcnt = 0
      BEGIN
      insert into RMDB.dbo.id_project
                  (RMDB.dbo.id_project.atv_id )
select DEVDB.DBO.DOCTBL.actv_id
            from DEVDB.DBO.DOCTBL, RMDB.dbo.re_project, inserted
            where DEVDB.DBO.DOCTBL.actv_id = RMDB.dbo.re_project.atv_id
            and DEVDB.DBO.DOCTBL.docnum = inserted.docnum
            and DEVDB.DBO.DOCTBL.version = inserted.version
      END;

--------------

Change to

      insert into RMDB.dbo.id_project
      (RMDB.dbo.id_project.atv_id )

      SELECT RMDB.dbo.id_project.atv_id
      FROM RMDB.dbo.id_project
      LEFT JOIN
            (
            select DEVDB.DBO.DOCTBL.actv_id
            from DEVDB.DBO.DOCTBL
            INNER JOIN RMDB.dbo.re_project
            ON DEVDB.DBO.DOCTBL.actv_id = RMDB.dbo.re_project.atv_id
            INNER JOIN inserted
            ON DEVDB.DBO.DOCTBL.docnum = inserted.docnum
                  and DEVDB.DBO.DOCTBL.version = inserted.version
            ) AS CurrentData
      ON RMDB.dbo.id_project.atv_id = CurrentData.actv_id
      WHERE CurrentData.actv_id IS NULL

ASKER CERTIFIED SOLUTION

JoeNuvo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rajeev

Hi,

I have change the variables @Deldocdate and @Insdocdate with the query.
I hope it should work now. In case of error please provide us the error message(s) you are getting.

USE [DEVDB]
GO
/****** Object:  Trigger [tU_UpdateTable]    Script Date: 03/16/2011 10:50:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO


CREATE TRIGGER [tU_UpdateTable] ON [DBO].[DOCTBL]
FOR UPDATE
AS

set nocount on

begin

/***  exit the trigger if document is not a progression item  ***/
If EXISTS(SELECT class FROM DELETED WHERE class <> 'ACT_PROG') 
	If EXISTS(SELECT class FROM INSERTED WHERE class <> 'ACT_PROG')
		Begin 
			Return 
		End 

/**** update project status code to project (11) when progression item is updated   ***/

update RMDB.dbo.re_project
set RMDB.dbo.re_project.atv_STATUS = 11 
from DEVDB.DBO.DOCTBL, RMDB.dbo.re_project, inserted
where DEVDB.DBO.DOCTBL.actv_id = RMDB.dbo.re_project.atv_id
and RMDB.dbo.re_project.atv_STATUS in (10, 2)
and DEVDB.DBO.DOCTBL.docnum = inserted.docnum
and DEVDB.DBO.DOCTBL.version = inserted.version
and inserted.class = 'ACT_PROG'
and inserted.subclass <> 'CPREC'
and inserted.subclass in (select subclass from DEVDB.DBO.DOCSUBCLASSES
                 where class = 'ACT_PROG')


/**** update project status date and track order rec date when progression item is added  ***/

declare @taskcnt as int
declare @Deldocdate as datetime
declare @Insdocdate as datetime

select @Deldocdate = coalesce(date1,date2,entrywhen) from deleted
select @Insdocdate = coalesce(date1,date2,entrywhen) from inserted

select @taskcnt = 0

select @taskcnt = count(RMDB.dbo.id_project.atv_id) 
	from DEVDB.DBO.DOCTBL, RMDB.dbo.id_project, inserted
		where DEVDB.DBO.DOCTBL.actv_id = RMDB.dbo.id_project.atv_id 		
		and DEVDB.DBO.DOCTBL.docnum = inserted.docnum
		and DEVDB.DBO.DOCTBL.version = inserted.version

IF @taskcnt = 0
	BEGIN
	insert into RMDB.dbo.id_project  
			(RMDB.dbo.id_project.atv_id )
     	select DEVDB.DBO.DOCTBL.actv_id
		from DEVDB.DBO.DOCTBL, RMDB.dbo.re_project, inserted
		where DEVDB.DBO.DOCTBL.actv_id = RMDB.dbo.re_project.atv_id
		and DEVDB.DBO.DOCTBL.docnum = inserted.docnum
		and DEVDB.DBO.DOCTBL.version = inserted.version
  	END;

update RMDB.dbo.id_project
  set RMDB.dbo.ID_project.ID_PROJECT_DT = case when
					RMDB.dbo.ID_project.ID_PROJECT_DT is NULL
                    then coalesce(inserted.date1,inserted.date2,inserted.entrywhen) 
					else RMDB.dbo.ID_project.ID_PROJECT_DT
					end
  , RMDB.dbo.ID_project.ID_TRACK_ORDER_REC =  case when
					deleted.subclass = 'TOL_REC' 
					and RMDB.dbo.ID_project.ID_TRACK_ORDER_REC = coalesce(deleted.date1,deleted.date2,deleted.entrywhen) 
					then NULL
					when inserted.subclass = 'TOL_REC' 
						and RMDB.dbo.ID_project.ID_TRACK_ORDER_REC is null
					then coalesce(inserted.date1,inserted.date2,inserted.entrywhen) 
					else RMDB.dbo.ID_project.ID_TRACK_ORDER_REC
					end
from DEVDB.DBO.DOCTBL, RMDB.dbo.ID_project, inserted, deleted
where DEVDB.DBO.DOCTBL.actv_id = RMDB.dbo.ID_project.atv_id
and DEVDB.DBO.DOCTBL.docnum = inserted.docnum
and DEVDB.DBO.DOCTBL.version = inserted.version
and DEVDB.DBO.DOCTBL.docnum = deleted.docnum
and DEVDB.DBO.DOCTBL.version = deleted.version
and inserted.class = 'ACT_PROG'
and inserted.subclass <> 'CPREC'
and inserted.subclass in (select subclass from DEVDB.DBO.DOCSUBCLASSES
                 where class = 'ACT_PROG')


/**** remove old progression item date if progession document class changes ***/
If (SELECT class FROM DELETED) = 'ACT_PROG'
	Begin 

	update RMDB.dbo.ID_RE_TASKS
		set RMDB.dbo.ID_RE_TASKS.ID_PLAN_REQ =  case when 
					deleted.subclass = 'IDPLANREQ' 
					and RMDB.dbo.ID_RE_TASKS.ID_PLAN_REQ = coalesce(deleted.date1,deleted.date2,deleted.entrywhen)
					THEN NULL  
					else RMDB.dbo.ID_RE_TASKS.ID_PLAN_REQ
					end
		 , RMDB.dbo.ID_RE_TASKS.ID_PLAN_REC =  case when 
					deleted.subclass = 'IDPLANREC' 
					and RMDB.dbo.ID_RE_TASKS.ID_PLAN_REC = coalesce(deleted.date1,deleted.date2,deleted.entrywhen)
					THEN NULL  
					else RMDB.dbo.ID_RE_TASKS.ID_PLAN_REC
					end
		 , RMDB.dbo.ID_RE_TASKS.ID_PLAN_DRAWING = case when 
					deleted.subclass = 'IDPDF' 
					and RMDB.dbo.ID_RE_TASKS.ID_PLAN_DRAWING = coalesce(deleted.date1,deleted.date2,deleted.entrywhen)
					THEN NULL  
					else RMDB.dbo.ID_RE_TASKS.ID_PLAN_DRAWING
					end
		 , RMDB.dbo.ID_RE_TASKS.EP_DRAWING  = case when 
					deleted.subclass = 'DC_PLAN' 
					and RMDB.dbo.ID_RE_TASKS.EP_DRAWING = coalesce(deleted.date1,deleted.date2,deleted.entrywhen)
					THEN NULL  
					else RMDB.dbo.ID_RE_TASKS.EP_DRAWING 
					end
		 , RMDB.dbo.ID_RE_TASKS.LAND_LEASE_REQ  = case when 
					deleted.subclass = 'LL_REQ' 
					and RMDB.dbo.ID_RE_TASKS.LAND_LEASE_REQ = coalesce(deleted.date1,deleted.date2,deleted.entrywhen)
					THEN NULL  
					else RMDB.dbo.ID_RE_TASKS.LAND_LEASE_REQ 
					end
		 , RMDB.dbo.ID_RE_TASKS.LAND_LEASE_REC  = case when 
					deleted.subclass = 'LL_REC' 
					and RMDB.dbo.ID_RE_TASKS.LAND_LEASE_REC = coalesce(deleted.date1,deleted.date2,deleted.entrywhen)
					THEN NULL  
					else RMDB.dbo.ID_RE_TASKS.LAND_LEASE_REC
					end
		 , RMDB.dbo.ID_RE_TASKS.LAND_SALES_REQ  = case when 
					deleted.subclass = 'LS_REQ' 
					and RMDB.dbo.ID_RE_TASKS.LAND_SALES_REQ = coalesce(deleted.date1,deleted.date2,deleted.entrywhen)
					THEN NULL  
					else RMDB.dbo.ID_RE_TASKS.LAND_SALES_REQ
					end
		 , RMDB.dbo.ID_RE_TASKS.LAND_SALES_REC  = case when 
					deleted.subclass = 'LS_REC' 
					and RMDB.dbo.ID_RE_TASKS.LAND_SALES_REC = coalesce(deleted.date1,deleted.date2,deleted.entrywhen)
					THEN NULL  
					else RMDB.dbo.ID_RE_TASKS.LAND_SALES_REC
					end
		from DEVDB.DBO.DOCTBL, RMDB.dbo.ID_RE_TASKS, deleted
		where DEVDB.DBO.DOCTBL.actv_id = RMDB.dbo.ID_RE_TASKS.atv_id 		
		and DEVDB.DBO.DOCTBL.docnum = deleted.docnum
		and DEVDB.DBO.DOCTBL.version = deleted.version



update RMDB.dbo.RE_TASK_LIST
		set RMDB.dbo.RE_TASK_LIST.TSK_DATE_COMPLETED =  case when 
					deleted.subclass = 'ENGREQ' 
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '1' 
					THEN NULL
				WHEN deleted.subclass = 'ENGREC'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '2' 
					THEN NULL
				WHEN deleted.subclass = 'BURS'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '3' 
					THEN NULL
				WHEN deleted.subclass = '11147REQ'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '4' 
					THEN NULL 
				WHEN deleted.subclass = '11147REC'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '5' 
					THEN NULL 
				WHEN deleted.subclass = 'SUPT' 
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '6' 
					THEN NULL 
				WHEN deleted.subclass = 'GM'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '7' 
					THEN NULL 
				WHEN deleted.subclass = 'TPLPROD'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '8' 
					THEN NULL
				WHEN deleted.subclass = 'TPLRTN'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '9' 
					THEN NULL 
				WHEN deleted.subclass = 'FINREQ'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '10' 
					THEN NULL
				WHEN deleted.subclass = 'FINREC'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '11' 
					THEN NULL
				WHEN deleted.subclass = 'AFE/TOL_REQ'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '12'
					THEN null
				WHEN deleted.subclass = 'AFE_REC'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '13' 
					THEN NULL 
				WHEN deleted.subclass = 'SA/TL_REQ'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '14'
					THEN null
				WHEN deleted.subclass = 'SA/TL_REC'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '15' 
					THEN NULL 
				ELSE  RMDB.dbo.RE_TASK_LIST.TSK_DATE_COMPLETED  
				END
		from DEVDB.DBO.DOCTBL, RMDB.dbo.RE_TASK_LIST, deleted
		where DEVDB.DBO.DOCTBL.actv_id = RMDB.dbo.RE_TASK_LIST.atv_id 
		and RMDB.dbo.RE_TASK_LIST.TSK_DATE_COMPLETED = coalesce(deleted.date1,deleted.date2,deleted.entrywhen) 
		and DEVDB.DBO.DOCTBL.docnum = deleted.docnum
		and DEVDB.DBO.DOCTBL.version = deleted.version

	END  -- END IF
/**** update the progression item date if not already updated ***/

If (SELECT class FROM INSERTED) = 'ACT_PROG'
	Begin 

	select @taskcnt = 0

    select @taskcnt = count(RMDB.dbo.ID_RE_TASKS.atv_id) 
	from DEVDB.DBO.DOCTBL, RMDB.dbo.ID_RE_TASKS, inserted
		where DEVDB.DBO.DOCTBL.actv_id = RMDB.dbo.ID_RE_TASKS.atv_id 		
		and DEVDB.DBO.DOCTBL.docnum = inserted.docnum
		and DEVDB.DBO.DOCTBL.version = inserted.version

	IF @taskcnt = 0
	BEGIN
	insert into RMDB.dbo.ID_RE_TASKS  
			(RMDB.dbo.ID_RE_TASKS.atv_id )
     select DEVDB.DBO.DOCTBL.actv_id
		from DEVDB.DBO.DOCTBL, RMDB.dbo.re_project, inserted
		where DEVDB.DBO.DOCTBL.actv_id = RMDB.dbo.re_project.atv_id
		and DEVDB.DBO.DOCTBL.docnum = inserted.docnum
		and DEVDB.DBO.DOCTBL.version = inserted.version
  	END;

    update RMDB.dbo.ID_RE_TASKS
		set RMDB.dbo.ID_RE_TASKS.ID_PLAN_REQ =  case when 
					inserted.subclass = 'IDPLANREQ' and RMDB.dbo.ID_RE_TASKS.ID_PLAN_REQ is null
					THEN coalesce(inserted.date1,inserted.date2,inserted.entrywhen)
					else RMDB.dbo.ID_RE_TASKS.ID_PLAN_REQ
					end
		 , RMDB.dbo.ID_RE_TASKS.ID_PLAN_REC =  case when 
					inserted.subclass = 'IDPLANREC' and RMDB.dbo.ID_RE_TASKS.ID_PLAN_REC is null
					THEN coalesce(inserted.date1,inserted.date2,inserted.entrywhen)
					else RMDB.dbo.ID_RE_TASKS.ID_PLAN_REC
					end
		 , RMDB.dbo.ID_RE_TASKS.ID_PLAN_DRAWING = case when 
					inserted.subclass = 'IDPDF' and RMDB.dbo.ID_RE_TASKS.ID_PLAN_DRAWING is null
					THEN coalesce(inserted.date1,inserted.date2,inserted.entrywhen)
					else RMDB.dbo.ID_RE_TASKS.ID_PLAN_DRAWING
					end
		 , RMDB.dbo.ID_RE_TASKS.EP_DRAWING  = case when 
					inserted.subclass = 'DC_PLAN' and RMDB.dbo.ID_RE_TASKS.EP_DRAWING  is null
					THEN coalesce(inserted.date1,inserted.date2,inserted.entrywhen)
					else RMDB.dbo.ID_RE_TASKS.EP_DRAWING 
					end
		 , RMDB.dbo.ID_RE_TASKS.LAND_LEASE_REQ  = case when 
					inserted.subclass = 'LL_REQ' and RMDB.dbo.ID_RE_TASKS.LAND_LEASE_REQ  is null
					THEN coalesce(inserted.date1,inserted.date2,inserted.entrywhen)
					else RMDB.dbo.ID_RE_TASKS.LAND_LEASE_REQ 
					end
		 , RMDB.dbo.ID_RE_TASKS.LAND_LEASE_REC  = case when 
					inserted.subclass = 'LL_REC' and RMDB.dbo.ID_RE_TASKS.LAND_LEASE_REC  is null
					THEN coalesce(inserted.date1,inserted.date2,inserted.entrywhen)
					else RMDB.dbo.ID_RE_TASKS.LAND_LEASE_REC
					end
		 , RMDB.dbo.ID_RE_TASKS.LAND_SALES_REQ  = case when 
					inserted.subclass = 'LS_REQ' and RMDB.dbo.ID_RE_TASKS.LAND_SALES_REQ  is null
					THEN coalesce(inserted.date1,inserted.date2,inserted.entrywhen)
					else RMDB.dbo.ID_RE_TASKS.LAND_SALES_REQ
					end
		 , RMDB.dbo.ID_RE_TASKS.LAND_SALES_REC  = case when 
					inserted.subclass = 'LS_REC' and RMDB.dbo.ID_RE_TASKS.LAND_SALES_REC  is null
					THEN coalesce(inserted.date1,inserted.date2,inserted.entrywhen)
					else RMDB.dbo.ID_RE_TASKS.LAND_SALES_REC
					end
		from DEVDB.DBO.DOCTBL, RMDB.dbo.ID_RE_TASKS, inserted
		where DEVDB.DBO.DOCTBL.actv_id = RMDB.dbo.ID_RE_TASKS.atv_id 		
		and DEVDB.DBO.DOCTBL.docnum = inserted.docnum
		and DEVDB.DBO.DOCTBL.version = inserted.version

	select @taskcnt = 0

    select @taskcnt = count(RMDB.dbo.RE_TASK_LIST.atv_id) 
	from DEVDB.DBO.DOCTBL, RMDB.dbo.RE_TASK_LIST, inserted
		where DEVDB.DBO.DOCTBL.actv_id = RMDB.dbo.RE_TASK_LIST.atv_id 		
		and DEVDB.DBO.DOCTBL.docnum = inserted.docnum
		and DEVDB.DBO.DOCTBL.version = inserted.version

	IF @taskcnt = 0
	BEGIN
	insert into RMDB.dbo.RE_TASK_LIST  
			(RMDB.dbo.RE_TASK_LIST.atv_id )
     select DEVDB.DBO.DOCTBL.actv_id
		from DEVDB.DBO.DOCTBL, RMDB.dbo.re_project, inserted
		where DEVDB.DBO.DOCTBL.actv_id = RMDB.dbo.re_project.atv_id
		and DEVDB.DBO.DOCTBL.docnum = inserted.docnum
		and DEVDB.DBO.DOCTBL.version = inserted.version
  	END;

    update RMDB.dbo.RE_TASK_LIST
		set RMDB.dbo.RE_TASK_LIST.TSK_DATE_COMPLETED =  case when 
					inserted.subclass = 'ENGREQ' 
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '1' 
					THEN coalesce(inserted.date1,inserted.date2,inserted.entrywhen)
				WHEN inserted.subclass = 'ENGREC'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '2' 
					THEN coalesce(inserted.date1,inserted.date2,inserted.entrywhen)
				WHEN inserted.subclass = 'BURS'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '3' 
					THEN coalesce(inserted.date1,inserted.date2,inserted.entrywhen)
				WHEN inserted.subclass = '11147REQ'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '4' 
					THEN coalesce(inserted.date1,inserted.date2,inserted.entrywhen)
				WHEN inserted.subclass = '11147REC'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '5' 
					THEN coalesce(inserted.date1,inserted.date2,inserted.entrywhen)
				WHEN inserted.subclass = 'SUPT' 
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '6' 
					THEN coalesce(inserted.date1,inserted.date2,inserted.entrywhen)
				WHEN inserted.subclass = 'GM'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '7' 
					THEN coalesce(inserted.date1,inserted.date2,inserted.entrywhen)
				WHEN inserted.subclass = 'TPLPROD'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '8' 
					THEN coalesce(inserted.date1,inserted.date2,inserted.entrywhen)
				WHEN inserted.subclass = 'TPLRTN'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '9' 
					THEN coalesce(inserted.date1,inserted.date2,inserted.entrywhen)
				WHEN inserted.subclass = 'FINREQ'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '10' 
					THEN coalesce(inserted.date1,inserted.date2,inserted.entrywhen)
				WHEN inserted.subclass = 'FINREC'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '11' 
					THEN coalesce(inserted.date1,inserted.date2,inserted.entrywhen)
				WHEN inserted.subclass = 'AFE/TOL_REQ'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '12'
					THEN coalesce(inserted.date1,inserted.date2,inserted.entrywhen)
				WHEN inserted.subclass = 'AFE_REC'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '13' 
					THEN coalesce(inserted.date1,inserted.date2,inserted.entrywhen)
				WHEN inserted.subclass = 'SA/TL_REQ'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '14'
					THEN coalesce(inserted.date1,inserted.date2,inserted.entrywhen)
				WHEN inserted.subclass = 'SA/TL_REC'
						and RMDB.dbo.RE_TASK_LIST.TSK_SEQ = '15' 
					THEN coalesce(inserted.date1,inserted.date2,inserted.entrywhen)
				ELSE  RMDB.dbo.RE_TASK_LIST.TSK_DATE_COMPLETED  
				END
		from DEVDB.DBO.DOCTBL, RMDB.dbo.RE_TASK_LIST, inserted
		where DEVDB.DBO.DOCTBL.actv_id = RMDB.dbo.RE_TASK_LIST.atv_id 
		and RMDB.dbo.RE_TASK_LIST.TSK_DATE_COMPLETED is null
		and DEVDB.DBO.DOCTBL.docnum = inserted.docnum
		and DEVDB.DBO.DOCTBL.version = inserted.version

	END  -- IF 

end

Open in new window

evpadgett

ASKER

Thank you everyone for the responses. I will try using JoeNuvo's suggestion to use temp tables because that's what I am most familiar with and more comfortable using. I will let you know how it all turns out.

evpadgett

ASKER

This solution worked great. I was able to resolve the multiple row issue with only minor modifications to the code. Thank you so much JoeNuvo.