CREATE TABLE pruebas
(
id INT PRIMARY KEY,
nombre VARCHAR(20)
)
INSERT INTO pruebas VALUES (1,'Libras')
INSERT INTO pruebas VALUES (2,'Yo')
INSERT INTO pruebas VALUES (3,'Tu')
INSERT INTO pruebas VALUES (4,'Mama')
INSERT INTO pruebas VALUES (5,'Nos')
ALTER TRIGGER [dbo].[triggerC] ON pruebas FOR INSERT, UPDATE, DELETE
AS
DECLARE @bit INT ,
@FIELD INT ,
@maxfield INT ,
@CHAR INT ,
@fieldname VARCHAR(128) ,
@TableName VARCHAR(128) ,
@PKCols VARCHAR(1000) ,
@SQL VARCHAR(2000),
@UpdateDate VARCHAR(21) ,
@UserName VARCHAR(128) ,
@TYPE CHAR(1) ,
@PKSELECT VARCHAR(1000),
@cambios VARCHAR(MAX),
@fielddCambios VARCHAR(MAX),
@fieldiCambios VARCHAR(MAX)
SELECT @TableName = 'pruebas' --<-- cambiar el nombre de la tabla
-- Fecha y Usuario
SELECT @UserName = system_user ,
@UpdateDate = CONVERT(VARCHAR(8), getdate(), 112) +
' ' +
CONVERT(VARCHAR(12), getdate(), 114)
SET NoCount ON
-- Identificar que evento se está ejecutando (Insert, Update o Delete)
--en base a cursores especiales (inserted y deleted)
IF EXISTS (SELECT * FROM inserted)
IF EXISTS (SELECT * FROM deleted) --Si es un update
SELECT @TYPE = 'U'
ELSE --Si es un insert
SELECT @TYPE = 'I'
ELSE --si es un delete
SELECT @TYPE = 'D'
-- Obtenemos la lista de columnas de los cursores
SELECT * INTO #ins FROM inserted
SELECT * INTO #del FROM deleted
-- Obtener las columnas de llave primaria
SELECT @PKCols = COALESCE(@PKCols + ' and', ' on') +
' i.' +
c.COLUMN_NAME + ' = d.' +
c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
ON c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
WHERE pk.TABLE_NAME = @TableName AND
pk.CONSTRAINT_TYPE = 'PRIMARY KEY'
-- Obtener la llave primaria y columnas para la inserción en la tabla de auditoria
SELECT
@PKSELECT = COALESCE(@PKSelect+'+','') +
'''<' +
COLUMN_NAME +
'=''+convert(varchar(100),coalesce(i.' +
COLUMN_NAME +',d.' +
COLUMN_NAME + '))+''>'''
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
ON c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
WHERE pk.TABLE_NAME = @TableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
IF @PKCols IS NULL --<-- Este trigger solo funciona si la tabla tiene llave primaria
BEGIN
RAISERROR('no PK on table %s', 16, -1, @TableName)
RETURN
END
--Loop para armar el query de inserción en la tabla de log.
--Un registro por cada campo afectado.
SELECT
@FIELD = 0,
@maxfield = MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
while @FIELD < @maxfield
BEGIN
SELECT @FIELD = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION > @FIELD
SELECT @bit = (@FIELD - 1 )% 8 + 1
SELECT @bit = POWER(2,@bit - 1)
SELECT @CHAR = ((@FIELD - 1) / 8) + 1
IF SUBSTRING(COLUMNS_UPDATED(),@CHAR, 1) & @bit > 0 OR @TYPE IN ('I','D')
BEGIN
SELECT @fieldname = COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION = @FIELD
print @fieldname
/*DECLARE
@querycafe1 nvarchar(MAX),
@querycafe2 nvarchar(MAX),
@valorViejo varchar(MAX),
@valorNuevo varchar(MAX)
SET @querycafe1 = 'SELECT ' + @fieldname + ' INTO #temp1 FROM #del d'
SET @querycafe2 = 'SELECT ' + @fieldname + ' INTO #temp2 FROM #ins i'
Exec(@querycafe1)
Exec(@querycafe2)
--NO FUNCIONAN
SET @valorViejo = (SELECT[*] FROM #temp1)
SET @valorNuevo = (SELECT[*] FROM #temp2)]
--Esto es lo que hay que armar, una cadena de texto.*/
DECLARE @query Nvarchar(MAX)
SET @cambios=''
--SELECT @cambios = @cambios + ', ' + @fieldname + ' de ' + ' convert(varchar(1000),d.' + @fieldname + ') a ' + ' convert(varchar(1000),i.' + @fieldname + ')' from #ins as i full outer join
SET @query='
select ' + CHAR(39) + @fieldname + 'cambio de ' + CHAR(39) + ' + convert(varchar(1000),d.' + @fieldname + ') + ' + CHAR(39) + ' a ' + CHAR(39) + ' + convert(varchar(1000),i.' + @fieldname + ')' + ' from #ins i full outer join #del d ' + @PKCols
+ ' where i.' + @fieldname + '<> d.' + @fieldname
--print @query
EXECUTE sp_executesql @query
END
END
UPDATE pruebas
SET nombre='aqui'
WHERE id=5
