Set NoCount On
Declare @Tabla As Varchar(100) = ‘Descuentos’
Declare @Operacion As Varchar(100) = ‘ FOR UPDATE ‘
Declare @Cabecera As Varchar(Max) = ”
Declare @Detalle As Varchar(Max) = ”
Declare @printDetalle As Varchar(Max) = ”
Set @Cabecera = ‘
— =============================================
— Author: ‘ + SUSER_SNAME() + ‘
— Create date: ‘ + Cast(year(GetDate()) As Varchar) + RIGHT(‘0’ + Cast(month(GetDate()) As Varchar), 2) + RIGHT(‘0’ + Cast(day(GetDate()) As Varchar), 2) + ‘
— Description: UPDATE TRIGGER for Table: [dbo].[‘ + @Tabla + ‘]
— History:
— Date Author Description
— ———————————————
—
— =============================================
CREATE TRIGGER [dbo].[tr_u_AUDIT_’ + @Tabla + ‘]
ON [dbo].[‘ + @Tabla + ‘]
‘ + @Operacion + ‘
NOT FOR REPLICATION
As
BEGIN
DECLARE
@IDENTITY_SAVE varchar(50),
@AUDIT_LOG_TRANSACTION_ID Int,
@PRIM_KEY nvarchar(4000),
@Inserted bit,
–@TABLE_NAME nvarchar(4000),
@ROWS_COUNT int
SET NOCOUNT ON
Select @ROWS_COUNT=count(1) from inserted
SET @IDENTITY_SAVE = CAST(IsNull(@@IDENTITY,1) AS varchar(50))
INSERT
INTO dbo.AUDIT_LOG_TRANSACTIONS
(
TABLE_NAME,
TABLE_SCHEMA,
AUDIT_ACTION_ID,
HOST_NAME,
APP_NAME,
MODIFIED_BY,
MODIFIED_DATE,
AFFECTED_ROWS,
[DATABASE]
)
values(
”’ + @Tabla + ”’,
”dbo”,
1, — ACTION ID For UPDATE
CASE
WHEN LEN(HOST_NAME()) < 1 THEN ” ”
ELSE HOST_NAME()
END,
CASE
WHEN LEN(APP_NAME()) < 1 THEN ” ”
ELSE APP_NAME()
END,
SUSER_SNAME(),
GETDATE(),
@ROWS_COUNT,
”’ + DB_NAME() + ”’
)
Set @AUDIT_LOG_TRANSACTION_ID = SCOPE_IDENTITY()
SET @Inserted = 0′
print @Cabecera
DECLARE @Columnas TABLE (
ID INT IDENTITY(1, 1)
,ColumnName Varchar(100) Default Null
,PRIMARY KEY(
ID
)
)
Declare @Exclusion As Varchar(200) = ‘Usuario_Alta,Fecha_Alta,Usuario_Cambio,Fecha_Cambio,rowguid,msrepl_tran_version,UsuarioAlta,FechaAlta,UsuarioCambio,FechaCambio’
Insert Into @Columnas (ColumnName)
SELECT
Col.Column_Name
FROM
INFORMATION_SCHEMA.COLUMNS Col
WHERE
Col.TABLE_NAME = @Tabla
And CharIndex(Col.Column_Name, @Exclusion) = 0
AND Col.DATA_TYPE <> ‘text’ — Excluir los tipos TEXT porque no se pueden manipular
Declare @RecordCount As Int = @@RowCount
Declare @Index As Int = 1
Declare @laColumna As Varchar(100)
–Select ‘TSP’,* From @Columnas
DECLARE @LlavePrimaria TABLE (
ID INT IDENTITY(1, 1)
,ColumnName Varchar(100) Default Null
,PRIMARY KEY(
ID
)
)
Insert Into @LlavePrimaria (ColumnName)
Select D.Data From dbo.Split(dbo.fsdba_PrimaryKeyTable(@Tabla),’,’) as D
Declare @RecordCountPK As Int = @@RowCount
Declare @IndexPK As Int = 1
Declare @laColumnaPK As Varchar(100) = ”
Declare @SelectPK As Varchar(1500) = ”
Declare @FromPK As Varchar(1500) = ”
Declare @BandPK As Bit = 0
Declare @Key1 As Varchar(300) = ”
Declare @Key2 As Varchar(300) = ”
Declare @Key3 As Varchar(300) = ”
Declare @Key4 As Varchar(300) = ”
–select ‘teo’, * From @LlavePrimaria PK
— Llave Primaria
While (@IndexPK <= @RecordCountPK) Begin
Select
@LaColumnaPK = ColumnName
From
@LlavePrimaria PK
Where
PK.ID = @IndexPK
If @BandPK = 1 Begin
Set @SelectPK = @SelectPK + ‘ + ” And ” + ‘
Set @FromPK = @FromPK + ‘ And ‘
End
Set @SelectPK = @SelectPK + ‘IsNull(”[‘ + @LaColumnaPK + ‘]=”+ CONVERT(nvarchar(4000), IsNull(OLD.[‘ + @LaColumnaPK + ‘], NEW.[‘ + @LaColumnaPK + ‘]), 0), ”[‘ + @LaColumnaPK + ‘] Is Null”)’
Set @FromPK = @FromPK + ‘(CONVERT(nvarchar(4000), NEW.[‘ + @LaColumnaPK + ‘], 0) = CONVERT(nvarchar(4000), OLD.[‘ + @LaColumnaPK + ‘], 0) or (NEW.[‘ + @LaColumnaPK + ‘] Is Null and OLD.[‘ + @LaColumnaPK + ‘] Is Null))’
— Llena los campos llave independientes
If @IndexPk = 1
SET @Key1 = ‘IsNULL( CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[‘ + @LaColumnaPK + ‘], 0)), CONVERT(nvarchar(500), CONVERT(nvarchar(4000), NEW.[‘ + @LaColumnaPK + ‘], 0)))’
If @IndexPk = 2
SET @Key2 = ‘IsNULL( CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[‘ + @LaColumnaPK + ‘], 0)), CONVERT(nvarchar(500), CONVERT(nvarchar(4000), NEW.[‘ + @LaColumnaPK + ‘], 0)))’
If @IndexPk = 3
SET @Key3 = ‘IsNULL( CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[‘ + @LaColumnaPK + ‘], 0)), CONVERT(nvarchar(500), CONVERT(nvarchar(4000), NEW.[‘ + @LaColumnaPK + ‘], 0)))’
If @IndexPk = 4
SET @Key4 = ‘IsNULL( CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[‘ + @LaColumnaPK + ‘], 0)), CONVERT(nvarchar(500), CONVERT(nvarchar(4000), NEW.[‘ + @LaColumnaPK + ‘], 0)))’
Set @BandPK = 1
Set @IndexPK += 1
End
If @BandPK = 1 Begin
Set @SelectPK = ‘convert(nvarchar(1500), ‘ + @SelectPK + ‘)’
End
–Select @SelectPk, @FromPK
–Return
— Cada campo
WHILE (@Index <= @RecordCount) BEGIN
— select dbo.fsdba_PrimaryKeyTable(‘hoteles_textos’)
Set @laColumna = (Select ColumnName From @Columnas C Where C.Id = @Index)
Set @printDetalle = ‘
— No. Columna: ”’ + cast(@Index as varchar) + ‘
IF UPDATE ([‘ + @laColumna + ‘]) BEGIN
INSERT INTO dbo.AUDIT_LOG_DATA (
AUDIT_LOG_TRANSACTION_ID
,PRIMARY_KEY_DATA
,COL_NAME
,OLD_VALUE_LONG
,NEW_VALUE_LONG
,KEY1
,KEY2
,KEY3
,KEY4
)
SELECT
@AUDIT_LOG_TRANSACTION_ID,
‘ + @SelectPK + ‘
,”’ + @laColumna + ”’
,CONVERT(NVARCHAR(4000), OLD.[‘ + @laColumna + ‘], 0)
,CONVERT(NVARCHAR(4000), NEW.[‘ + @laColumna + ‘], 0)
,’ + Case When IsNull(@Key1, ”) = ” Then ‘NULL’ Else @Key1 End + ‘
,’ + Case When IsNull(@Key2, ”) = ” Then ‘NULL’ Else @Key2 End + ‘
,’ + Case When IsNull(@Key3, ”) = ” Then ‘NULL’ Else @Key3 End + ‘
,’ + Case When IsNull(@Key4, ”) = ” Then ‘NULL’ Else @Key4 End + ‘
FROM deleted OLD
INNER JOIN inserted NEW ON ‘ + @FromPK + ‘
WHERE (
(NEW.[‘ + @laColumna + ‘] <> OLD.[‘ + @laColumna + ‘])
OR (
NEW.[‘ + @laColumna + ‘] IS NULL
AND OLD.[‘ + @laColumna + ‘] IS NOT NULL
)
OR (
NEW.[‘ + @laColumna + ‘] IS NOT NULL
AND OLD.[‘ + @laColumna + ‘] IS NULL
)
)
SET @Inserted = CASE
WHEN @@ROWCOUNT > 0
THEN 1
ELSE @Inserted
END
END
‘ + char(10) + char(13)
print @printDetalle
Set @Detalle = @Detalle + @printDetalle
Set @Index += 1
END
Declare @Pie As Varchar(Max) = ”
Set @Pie = ‘
IF @Inserted = 0
BEGIN
DELETE FROM dbo.AUDIT_LOG_TRANSACTIONS WHERE AUDIT_LOG_TRANSACTION_ID = @AUDIT_LOG_TRANSACTION_ID
END
— Restore @@IDENTITY Value
DECLARE @maxprec AS varchar(2)
SET @maxprec=CAST(@@MAX_PRECISION as varchar(2))
EXEC(”SELECT IDENTITY(decimal(”+@maxprec+”,0),”+@IDENTITY_SAVE+”,1) id INTO #tmp”)
End
GO
EXEC sp_settriggerorder @triggername=N”[dbo].[tr_u_AUDIT_’ + @Tabla + ‘]”, @order=N”Last”, @stmttype=N”UPDATE”
‘
–select ‘@Detalle: ‘ + @Detalle
print @Pie
/*
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AUDIT_LOG_TRANSACTIONS](
[AUDIT_LOG_TRANSACTION_ID] [int] IDENTITY(1,1) NOT NULL,
[DATABASE] [nvarchar](128) NOT NULL,
[TABLE_NAME] [nvarchar](261) NOT NULL,
[TABLE_SCHEMA] [nvarchar](261) NOT NULL,
[AUDIT_ACTION_ID] [tinyint] NOT NULL,
[HOST_NAME] [varchar](128) NOT NULL,
[APP_NAME] [varchar](128) NOT NULL,
[MODIFIED_BY] [varchar](128) NOT NULL,
[MODIFIED_DATE] [datetime] NOT NULL,
[AFFECTED_ROWS] [int] NOT NULL,
[SYSOBJ_ID] AS (object_id([TABLE_NAME])),
PRIMARY KEY CLUSTERED
(
[AUDIT_LOG_TRANSACTION_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[AUDIT_LOG_TRANSACTIONS] ADD DEFAULT (db_name()) FOR [DATABASE]
GO
ALTER TABLE [dbo].[AUDIT_LOG_TRANSACTIONS] ADD CONSTRAINT [DF_AUDIT_LOG_TRANSACTIONS_MODIFIED_BY] DEFAULT (‘System’) FOR [MODIFIED_BY]
GO
****************************
CREATE TABLE [dbo].[AUDIT_LOG_DATA](
[AUDIT_LOG_DATA_ID] [int] IDENTITY(1,1) NOT NULL,
[AUDIT_LOG_TRANSACTION_ID] [int] NOT NULL,
[PRIMARY_KEY_DATA] [nvarchar](1500) NOT NULL,
[COL_NAME] [nvarchar](128) NOT NULL,
[OLD_VALUE_LONG] [ntext] NULL,
[NEW_VALUE_LONG] [ntext] NULL,
[NEW_VALUE_BLOB] [image] NULL,
[NEW_VALUE] AS (isnull(CONVERT([varchar](8000), [NEW_VALUE_LONG],0),CONVERT([varchar](8000),CONVERT([varbinary](8000),substring([NEW_VALUE_BLOB],(1),(8000)),0),0))),
[OLD_VALUE] AS (CONVERT([varchar](8000),[OLD_VALUE_LONG],0)),
[PRIMARY_KEY] AS ([PRIMARY_KEY_DATA]),
[DATA_TYPE] [char](1) NOT NULL,
[KEY1] [nvarchar](500) NULL,
[KEY2] [nvarchar](500) NULL,
[KEY3] [nvarchar](500) NULL,
[KEY4] [nvarchar](500) NULL,
PRIMARY KEY CLUSTERED
(
[AUDIT_LOG_DATA_ID] ASC
)
)
***************************************************
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author: <Author,,Name oscar ramirez>
— Create date: <Create Date, ,>
— Description: <Description, ,>
— =============================================
ALTER FUNCTION [dbo].[fsdba_PrimaryKeyTable]
(
@Nombretabla as varchar(150)
)
RETURNS varchar(100)
AS
BEGIN
Declare @ParamCOLUMN_NAME varchar(100)
DECLARE @Lista varchar(MAX)
set @Lista = ”
SELECT @Lista = @Lista + COLUMN_NAME +’,’
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + ‘.’ + QUOTENAME(CONSTRAINT_NAME)), ‘IsPrimaryKey’) = 1
AND TABLE_NAME = @Nombretabla –AND TABLE_SCHEMA = ‘Schema’
select @ParamCOLUMN_NAME = LEFT( cast(@Lista as varchar(MAX)),LEN( cast(@Lista as varchar(MAX)))-1)
return @ParamCOLUMN_NAME
END
–https://stackoverflow.com/questions/349524/sql-server-history-table-populate-through-sp-or-trigger
*/
ocar48 