Solved

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

Posted on 2011-03-16
7
261 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:evpadgett
7 Comments
 
LVL 3

Expert Comment

by:rkharko
ID: 35154346
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

0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35154656
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?
0
 

Expert Comment

by:nirmalrampk
ID: 35154790
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

0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 11

Accepted Solution

by:
JoeNuvo earned 500 total points
ID: 35155109
I wish this one can comply with your logic.

concept : get required information from INSERTED/DELETED into temp table
and utilized data from within

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

set nocount on

BEGIN -- main begin

	Declare @Temp TABLE (docnum int, version int, actv_id int,
		iclass varchar(20), isubclass varchar(20), Insdocdate datetime,
		dclass varchar(20), dsubclass varchar(20), Deldocdate datetime, 
		taskcnt int)

	INSERT INTO @Temp
	SELECT I.docnum, I.version, I.actv_id,
		I.class, I.subclass, coalesce(I.date1, I.date2, I.entrywhen),
		D.class, D.subclass, coalesce(D.date1, D.date2, D.entrywhen),
		0
	FROM DELETED D INNER JOIN INSERTED I
		ON D.pkey = I.pkey
	WHERE
		D.class = 'ACT_PROG' OR I.class = 'ACT_PROG'

	IF @@ROWCOUNT = 0 RETURN


/**** 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, @Temp T
	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 = T.docnum
	and DEVDB.DBO.DOCTBL.version = T.version
	and T.iclass = 'ACT_PROG'
	and T.isubclass <> 'CPREC'
	and T.isubclass 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  ***/
	UPDATE @Temp
	SET taskcnt = IsNull(count(RMDB.dbo.id_project.atv_id) ,0)
	from RMDB.dbo.id_project, @Temp T
		where T.actv_id = RMDB.dbo.id_project.atv_id 		
	GROUP BY
		T.docnum, T.version

	insert into RMDB.dbo.id_project (RMDB.dbo.id_project.atv_id )
		select actv_id
		from @Temp
		-- maybe need to join with RMDB.dbo.re_project ??
		where TaskCount = 0

	update RMDB.dbo.id_project
	SET ID_PROJECT_DT = ISNULL(ID_PROJECT_DT, T.Insdocdate) , 
		ID_TRACK_ORDER_REC = case
			when T.dsubclass = 'TOL_REC' and ID_TRACK_ORDER_REC = T.Deldocdate then NULL
			when T.isubclass = 'TOL_REC' and ID_TRACK_ORDER_REC is null then T.Insdocdate
			else ID_TRACK_ORDER_REC	end
	from DEVDB.DBO.DOCTBL, RMDB.dbo.ID_project, @Temp T
	where DEVDB.DBO.DOCTBL.actv_id = RMDB.dbo.ID_project.atv_id
	and DEVDB.DBO.DOCTBL.docnum = T.docnum
	and DEVDB.DBO.DOCTBL.version = T.version
	and T.iclass = 'ACT_PROG'
	and T.isubclass <> 'CPREC'
	and T.isubclass 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 ID_PLAN_REQ = case
				when T.dsubclass = 'IDPLANREQ' and ID_PLAN_REQ = T.Deldocdate THEN NULL  
					else ID_PLAN_REQ
					end
		 , ID_PLAN_REC =  case
				when T.dsubclass = 'IDPLANREC' and ID_PLAN_REC = T.Deldocdate THEN NULL  
					else ID_PLAN_REC
					end
		 , ID_PLAN_DRAWING = case
				when T.dsubclass = 'IDPDF' and ID_PLAN_DRAWING = T.Deldocdate THEN NULL  
					else ID_PLAN_DRAWING
					end
		 , EP_DRAWING  = case
				when T.dsubclass = 'DC_PLAN' and EP_DRAWING = T.Deldocdate THEN NULL  
					else EP_DRAWING 
					end
		 , LAND_LEASE_REQ  = case
				when T.dsubclass = 'LL_REQ' and LAND_LEASE_REQ = T.Deldocdate THEN NULL  
					else LAND_LEASE_REQ 
					end
		 , LAND_LEASE_REC  = case
				when T.dsubclass = 'LL_REC' and LAND_LEASE_REC = T.Deldocdate THEN NULL  
					else LAND_LEASE_REC
					end
		 , LAND_SALES_REQ  = case
				when T.dsubclass = 'LS_REQ' and LAND_SALES_REQ = T.Deldocdate THEN NULL  
					else LAND_SALES_REQ
					end
		 , LAND_SALES_REC  = case
				when T.dsubclass = 'LS_REC' and LAND_SALES_REC = T.Deldocdate THEN NULL  
					else LAND_SALES_REC
					end
		from RMDB.dbo.ID_RE_TASKS, @Temp T
		where T.actv_id = RMDB.dbo.ID_RE_TASKS.atv_id 		
		and T.dclass = 'ACT_PROG'


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

	END  -- END IF

/**** update the progression item date if not already updated ***/

--If (SELECT class FROM INSERTED) = 'ACT_PROG'
	BEGIN

	UPDATE @Temp
	SET taskcnt = IsNull(count(RMDB.dbo.ID_RE_TASKS.atv_id) ,0)
	from RMDB.dbo.ID_RE_TASKS, @Temp T
		where T.actv_id = RMDB.dbo.ID_RE_TASKS.atv_id
		AND T.iclass = 'ACT_PROG'
	GROUP BY
		T.docnum, T.version

	insert into RMDB.dbo.ID_RE_TASKS (RMDB.dbo.ID_RE_TASKS.atv_id )
		select actv_id
		from @Temp
		where taskcnt = 0

    update RMDB.dbo.ID_RE_TASKS
		set ID_PLAN_REQ =  case
				when T.isubclass = 'IDPLANREQ' and ID_PLAN_REQ is null THEN T.Insdocdate 
					else ID_PLAN_REQ
					end
		 , ID_PLAN_REC =  case
				when T.isubclass = 'IDPLANREC' and ID_PLAN_REC is null THEN T.Insdocdate 
					else ID_PLAN_REC
					end
		 , ID_PLAN_DRAWING = case
				when T.isubclass = 'IDPDF' and ID_PLAN_DRAWING is null THEN T.Insdocdate  
					else ID_PLAN_DRAWING
					end
		 , EP_DRAWING  = case
				when T.isubclass = 'DC_PLAN' and EP_DRAWING  is null THEN T.Insdocdate  
					else EP_DRAWING 
					end
		 , LAND_LEASE_REQ  = case
				when T.isubclass = 'LL_REQ' and LAND_LEASE_REQ  is null THEN T.Insdocdate  
					else LAND_LEASE_REQ 
					end
		 , LAND_LEASE_REC  = case
				when T.isubclass = 'LL_REC' and LAND_LEASE_REC  is null THEN T.Insdocdate  
					else LAND_LEASE_REC
					end
		 , LAND_SALES_REQ  = case
				when T.isubclass = 'LS_REQ' and LAND_SALES_REQ  is null THEN T.Insdocdate  
					else LAND_SALES_REQ
					end
		 , LAND_SALES_REC  = case
				when T.isubclass = 'LS_REC' and LAND_SALES_REC  is null THEN T.Insdocdate  
					else LAND_SALES_REC
					end
		from RMDB.dbo.ID_RE_TASKS, @Temp T
		where T.actv_id = RMDB.dbo.ID_RE_TASKS.atv_id 		
		and T.iclass = 'ACT_PROG'


	UPDATE @Temp
	SET taskcnt = IsNull(count(RMDB.dbo.RE_TASK_LIST.atv_id) ,0)
	from RMDB.dbo.RE_TASK_LIST, @Temp T
		where T.actv_id = RMDB.dbo.RE_TASK_LIST.atv_id
		AND T.iclass = 'ACT_PROG'	
	GROUP BY
		T.docnum, T.version

	insert into RMDB.dbo.RE_TASK_LIST ( atv_id )
		select actv_id
		from @Temp
		-- maybe need to join with RMDB.dbo.re_project ??
		where taskcnt = 0

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

	END  -- IF 

END -- main end

Open in new window

0
 
LVL 9

Expert Comment

by:rajeevnandanmishra
ID: 35156147
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

0
 

Author Comment

by:evpadgett
ID: 35168877
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.
0
 

Author Closing Comment

by:evpadgett
ID: 35202252
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.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now