Hola, finalmente resolví el Tigre hace un par de días, el resultado es este: (se q hay cosas en definicion q se pueden arreglar para q el codigo sea mas optimo)
Sin embargo ahi lo dejo para quien le pueda interesar.
Gracias...
--------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER [INSERTA]
ON [dbo].[CA_DOCUMENT_ACTORS]
INSTEAD OF insert
AS
DECLARE @document_id AS INT
declare @user_id as varchar(max)
declare @c_user_id as varchar(max)
declare @leauser_id as varchar(max)
declare @last_actor_id as varchar(max)
declare @c_last_actor_id as varchar(max)
declare @lealast_actor_id as varchar(max)
declare @emitter_id as varchar(max)
declare @c_emitter_id as varchar(max)
declare @leaemitter_id as varchar(max)
declare @processId as INT
declare @c_processId as varchar(max)
declare @leaprocessId as INT
--DECLARE @ret_msg AS VARCHAR(100);
BEGIN
set @c_user_id = ''
set @c_last_actor_id = ''
set @c_emitter_id = ''
set @c_processId = ''
SET NOCOUNT ON; ---
--SET @document_id = SELECT [document_id]FROM inserted
DECLARE cursor2 CURSOR
FOR SELECT [document_id], [user_id], [actors], [emiterId], [process_id] FROM inserted
OPEN cursor2
FETCH NEXT FROM cursor2 INTO @document_id, @user_id, @last_actor_id, @emitter_id, @processId
WHILE (@@FETCH_STATUS=0)
begin
fetch next from CURSOR2
into @document_id, @user_id, @last_actor_id, @emitter_id, @processId
end
close cursor2
deallocate cursor2
IF EXISTS (select document_id
from CA_DOCUMENT_ACTORS
where document_id = @document_id)
BEGIN
Declare CURSOR1 cursor for
SELECT user_id, actors, emiterId, process_id FROM CA_DOCUMENT_ACTORS WHERE DOCUMENT_ID=@document_id
open CURSOR1
fetch next from CURSOR1
into @leauser_id, @lealast_actor_id, @leaemitter_id, @leaprocessId
while @@fetch_status = 0
begin
set @c_user_id = @c_user_id + @leauser_id + ', ' +@user_id --+ ', '
set @c_last_actor_id = @c_last_actor_id + @lealast_actor_id + ', ' + @last_actor_id --+ ', '
set @c_emitter_id = @c_emitter_id + @leaemitter_id + ', ' + @emitter_id --+ ', '
set @c_processId = @processId
fetch next from CURSOR1
into @user_id, @last_actor_id, @emitter_id, @processId
end
close CURSOR1
deallocate CURSOR1
select @c_user_id, @c_last_actor_id, @c_emitter_id, @c_processId
UPDATE CA_DOCUMENT_ACTORS SET USER_ID= @c_user_id,
ACTORS = @c_last_actor_id,
EMITERID = @c_emitter_id,
PROCESS_ID= @c_processId
WHERE "document_id" = @document_id
END
ELSE
BEGIN
Insert into CA_DOCUMENT_ACTORS values(@document_id, @user_id, @last_actor_id, @emitter_id, @processId)
END
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
END