
/*------ ATD 165495 ------*/

CREATE TABLE LOG_PEDICLICAD_AUD (
    IDENTIFICADOR int identity(1,1),
    NUMPED INT,
    SITVEN CHAR(1),
    DATA DATETIME not null,
    APLICATIVO CHAR(100), 
    USUARIO CHAR(20),
    SCRIPT VARCHAR(4000))
GO

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'PEDICLICAD_AUD_U' AND type = 'TR')
   drop trigger PEDICLICAD_AUD_U
go
create trigger dbo.PEDICLICAD_AUD_U on dbo.PEDICLICAD for update as
begin
    declare @numped int, @Handle binary(20) , @script varchar(4000), @ExecStr varchar(50)
    CREATE TABLE #inputbuffer 
    (  EventType varchar(30), Paramet int, EventInfo varchar(4000) ) 
	SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')' 
	INSERT INTO #inputbuffer 
	EXEC (@ExecStr) 
	SET @script = (SELECT EventInfo FROM #inputbuffer) 
	drop table #inputbuffer
    declare C_UPDATED_PEDICLICAD cursor local for select NUMPED from inserted where numped <> ''
    open C_UPDATED_PEDICLICAD
    fetch C_UPDATED_PEDICLICAD into @numped
    while @@fetch_status <> -1
    begin
		If (select count(1) from deleted where sitven = '2' and numped = @numped) > 0 AND (select count(1) from inserted where sitven <> '2' and numped = @numped ) > 0
		Begin
			INSERT INTO LOG_PEDICLICAD_AUD (NUMPED, SITVEN, DATA, APLICATIVO, USUARIO, SCRIPT) 
             SELECT ins.NUMPED, ins.SITVEN, GETDATE(),(SELECT program_name FROM sys.dm_exec_sessions where session_id = @@spid), (SELECT host_name FROM sys.dm_exec_sessions where session_id = @@spid), @script  FROM inserted ins
		End
        fetch C_UPDATED_PEDICLICAD into @numped
    end
    close C_UPDATED_PEDICLICAD
    deallocate C_UPDATED_PEDICLICAD
    return
end
go

