Hola ya tengo el proccedimiento q busca y concatena..... abajo lo pongo... ahora solo me falta el tigre q lo dispara, ya q el esta en espera del document_ID, pero no se como hacer q el tigre se lo pase antes de que se inserte... hice esto:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [INSERTA]
ON [dbo].[CA_DOCUMENT_ACTORS]
INSTEAD OF insert
AS
BEGIN
DECLARE @document_id AS INT
DECLARE @process_id AS INT
DECLARE @user_id AS VARCHAR(MAX)
DECLARE @actors AS VARCHAR(MAX)
DECLARE @emiterId AS VARCHAR(MAX)
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, @actors, @emiterId, @process_id
WHILE (@@FETCH_STATUS=0)
close cursor2
deallocate cursor2
PRINT @document_id;
END
El print se lo puse para verificar que este funcionando, pero antes no tenia esto:
close cursor2
deallocate cursor2
y se quedaba mostrandome indefinidamente el Document_ID q insertaba, luego le puse y no funcionó mas...
al final necesito incurstarle este procedimiento q es el q concatena segun el parametro de docid q se ingrese.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[conc_busca]
@docId int
AS
declare @user_id as varchar(max)
declare @c_user_id as varchar(max)
declare @last_actor_id as varchar(max)
declare @c_last_actor_id as varchar(max)
declare @emitter_id as varchar(max)
declare @c_emitter_id as varchar(max)
declare @processId as INT
declare @c_processId as varchar(max)
BEGIN
set @c_user_id = ''
set @c_last_actor_id = ''
set @c_emitter_id = ''
set @c_processId = ''
SET NOCOUNT ON;
--select * from CA_DOCUMENT_ACTORS
IF EXISTS (select document_id
from CA_DOCUMENT_ACTORS
where document_id = @docId)
BEGIN
Declare CURSOR1 cursor for
SELECT user_id, actors, emiterId, process_id FROM CA_DOCUMENT_ACTORS WHERE DOCUMENT_ID=@docId
open CURSOR1
fetch next from CURSOR1
into @user_id, @last_actor_id, @emitter_id, @processId
while @@fetch_status = 0
begin
set @c_user_id = @c_user_id + @user_id + ', '
set @c_last_actor_id = @c_last_actor_id + @last_actor_id + ', '
set @c_emitter_id = @c_emitter_id + @emitter_id + ', '
set @c_processId = @processId--cast(@processId as varchar(max))
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=SUBSTRING(@c_user_id,0,LEN(@c_user_id)),
ACTORS =SUBSTRING(@last_actor_id,0,LEN(@last_actor_id)),
EMITERID = SUBSTRING(@c_emitter_id,0,LEN(@c_emitter_id)),
PROCESS_ID=SUBSTRING(@c_processId,0,LEN(@c_process Id))
WHERE "document_id" = @docId
END
ELSE
BEGIN
Insert into CA_DOCUMENT_ACTORS values(@docId, @user_id, @last_actor_id, @emitter_id, @processId)
END
end
DE NUEVO MUCHAS GRACIAS