I just finished a trigger to detect if they performed an update to a record in a table, but my table contains more than 30 fields and it takes about 8 seconds to perform the update, being that without the trigger it takes a long time and a half. Maybe it's not much for me, but for the final client it will be too much, but if updating is a repetitive activity during the performance of your activity. Someone has some idea of how to improve the performance of my trigger. Thanks.
/****************************************************************************/
create TRIGGER bit_MNT36_update
ON MNT36
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON
-- ===============================================================================================================================
-- [0.0].- Declaración e inicialización de variables
-- ===============================================================================================================================
DECLARE
@campo_pk VARCHAR(100),
@campo_pk2 VARCHAR(100),
@campo_pk3 VARCHAR(100),
@fecha DATE,
@hora VARCHAR(30),
@cont INT,
@top INT,
@tabla VARCHAR(100),
@campo VARCHAR(100),
@total_pk NVARCHAR(100)
SET @campo_pk = ''
SET @campo_pk2 = ''
SET @campo_pk3 = ''
SET @fecha = NULL
SET @hora = ''
SET @tabla = ''
SET @campo = ''
SET @cont = 0
SET @top = 0
SET @total_pk = 0
-- ===============================================================================================================================
-- Obtener los campos que conforman la llave primaria
-- ===============================================================================================================================
IF OBJECT_ID (N'bit13', N'U') IS NOT NULL
DROP TABLE bit13
CREATE TABLE bit13
(
ID INT IDENTITY(1, 1) PRIMARY KEY,
campo_pk VARCHAR(100)
)
INSERT INTO bit13
SELECT
column_name
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND
TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME
AND
KU.table_name='mnt36'
SET @total_pk = (select count(*) from bit13)
IF not exists(SELECT campo_pk FROM BIT13 WHERE ID = 1)
BEGIN
SET @campo_pk = 'NULL'
END
ELSE
BEGIN
SET @campo_pk = (SELECT campo_pk FROM bit13 WHERE ID = 1)
END
IF not exists(SELECT campo_pk FROM BIT13 WHERE ID = 2)
BEGIN
SET @campo_pk2 = 'NULL'
END
ELSE
BEGIN
SET @campo_pk2 = (SELECT campo_pk FROM bit13 WHERE ID = 2)
END
IF not exists(SELECT campo_pk FROM BIT13 WHERE ID = 3)
BEGIN
SET @campo_pk3 = 'NULL'
END
ELSE
BEGIN
SET @campo_pk3 = (SELECT campo_pk FROM bit13 WHERE ID = 3)
END
-- ===============================================================================================================================
--[1.1].- Almacenar en la tabla temporal los campos a recorrer
-- ===============================================================================================================================
-- [1.1.1].- Crear tabla temporal para almancenar la relación Tabla-Campos
IF OBJECT_ID (N'bit11', N'U') IS NOT NULL
DROP TABLE [bit11]
CREATE TABLE [bit11]
(
ID INT IDENTITY(1, 1) PRIMARY KEY,
Tabla VARCHAR(100),
Campo VARCHAR(100)
)
-- [1.1.2].- Limpiar la tabla temporal
TRUNCATE TABLE [bit11]
-- [1.1.3].- Almacenar el listado de campos en la tabla temporal
INSERT INTO [bit11]
SELECT
obj.name AS [Tabla],
col.name AS [Campo]--,
--tipoDato.name AS [TipoDato]
FROM sysObjects obj
INNER JOIN sysColumns col ON obj.id = col.id
INNER JOIN sys.types tipoDato ON col.xtype = tipoDato.system_type_id
WHERE
obj.name = 'MNT36'
AND
obj.xType = 'U'
ORDER BY
col.colorder
-- ===============================================================================================================================
--[1.2].- Loop para revisar si existio una modificación
-- ===============================================================================================================================
-- [1.2.0].- Inicializar variables
SET @cont = 1
SET @top = (SELECT COUNT(*) FROM [bit11])
SET @fecha = CONVERT(DATE ,GETDATE())
SET @hora = LTRIM(RIGHT(CONVERT(VARCHAR(25), GETDATE(), 100), 7))
WHILE(@cont <= @top) -- LOOP - A
BEGIN
-- [1.2.1].- Obtener campo x campo del listado
SELECT
@tabla = Tabla,
@campo = Campo
FROM [bit11]
WHERE
ID = @cont
-- [1.2.2].- Eliminar tablas temporales en caso de existir
IF OBJECT_ID (N'DELETED_MNT36', N'U') IS NOT NULL
DROP TABLE [DELETED_MNT36]
IF OBJECT_ID (N'INSERTED_MNT36', N'U') IS NOT NULL
DROP TABLE [INSERTED_MNT36]
-- [1.2.3].- Crear tablas temporales de las tablas que contienen
-- los datos antes y despues de ser actualizados
SELECT * INTO [DELETED_MNT36] FROM DELETED
SELECT * INTO [INSERTED_MNT36] FROM INSERTED
-- [1.2.4].- Registrar en bitacora los cambios de la tabla
IF (@total_pk = 1)
EXEC('
DECLARE
@valor_pk VARCHAR(100),
@valor_ant VARCHAR(3000),
@valor_new VARCHAR(3000)
IF (SELECT COUNT(*) FROM bit13 WHERE ID = 1) > 0
SELECT @valor_pk = '+@campo_pk+' FROM DELETED_MNT36
IF EXISTS(SELECT *
FROM INSERTED_MNT36 I
INNER JOIN DELETED_MNT36 D ON I.'+@campo_pk+' = D.'+@campo_pk+'
AND I.'+@campo+' <> D.'+ @campo +'
)
BEGIN
SELECT
@valor_pk = '+@campo_pk+',
@valor_ant = '+@campo+'
FROM DELETED_MNT36
SELECT @valor_new = '+@campo+' FROM INSERTED_MNT36
INSERT INTO [bit12](tipo,
tabla,
campo_pk,
valor_pk,
campo,
valor_anterior,
valor_nuevo,
fecha,
hora)
SELECT ''U'',
'''+@tabla+''',
'''+@campo_pk+''',
@valor_pk,
'''+@campo+''',
@valor_ant,
@valor_new,
'''+@fecha+''',
'''+@hora+'''
END
')
IF (@total_pk = 2)
EXEC('
DECLARE
@valor_pk VARCHAR(100),
@valor_pk2 VARCHAR(100),
@valor_ant VARCHAR(3000),
@valor_new VARCHAR(3000)
IF (SELECT COUNT(*) FROM bit13 WHERE ID = 1) > 0
SELECT @valor_pk = '+@campo_pk+' FROM DELETED_MNT36
IF (SELECT COUNT(*) FROM bit13 WHERE ID = 2) > 0
SELECT @valor_pk2 = '+@campo_pk2+' FROM DELETED_MNT36
IF EXISTS(SELECT *
FROM INSERTED_MNT36 I
INNER JOIN DELETED_MNT36 D ON I.'+@campo_pk+' = D.'+@campo_pk+'
AND I.'+@campo_pk2+' = D.'+@campo_pk2+'
AND I.'+@campo+' <> D.'+ @campo +'
)
BEGIN
SELECT
@valor_pk = '+@campo_pk+',
@valor_ant = '+@campo+'
FROM DELETED_MNT36
SELECT @valor_new = '+@campo+' FROM INSERTED_MNT36
INSERT INTO [bit12](tipo,
tabla,
campo_pk,
valor_pk,
campo_pk2,
valor_pk2,
campo,
valor_anterior,
valor_nuevo,
fecha,
hora)
SELECT ''U'',
'''+@tabla+''',
'''+@campo_pk+''',
@valor_pk,
'''+@campo_pk2+''',
@valor_pk2,
'''+@campo+''',
@valor_ant,
@valor_new,
'''+@fecha+''',
'''+@hora+'''
END
')
IF (@total_pk = 3)
EXEC('
DECLARE
@valor_pk VARCHAR(100),
@valor_pk2 VARCHAR(100),
@valor_pk3 VARCHAR(100),
@valor_ant VARCHAR(3000),
@valor_new VARCHAR(3000)
IF (SELECT COUNT(*) FROM bit13 WHERE ID = 1) > 0
SELECT @valor_pk = '+@campo_pk+' FROM DELETED_imp02
IF (SELECT COUNT(*) FROM bit13 WHERE ID = 2) > 0
SELECT @valor_pk2 = '+@campo_pk2+' FROM DELETED_imp02
IF (SELECT COUNT(*) FROM bit13 WHERE ID = 3) > 0
SELECT @valor_pk3 = '+@campo_pk3+' FROM DELETED_imp02
IF EXISTS(SELECT *
FROM INSERTED_imp02 I
INNER JOIN DELETED_imp02 D ON I.'+@campo_pk+' = D.'+@campo_pk+'
AND I.'+@campo+' <> D.'+ @campo+'
)
BEGIN
SELECT
@valor_pk = '+@campo_pk+',
@valor_ant = '+@campo+'
FROM DELETED_imp02
SELECT @valor_new = '+@campo+' FROM INSERTED_imp02
INSERT INTO [bit12](tipo,
tabla,
campo_pk,
valor_pk,
campo_pk2,
valor_pk2,
campo_pk3,
valor_pk3,
campo,
valor_anterior,
valor_nuevo,
fecha,
hora)
SELECT ''U'',
'''+@tabla+''',
'''+@campo_pk+''',
@valor_pk,
'''+@campo_pk2+''',
@valor_pk2,
'''+@campo_pk3+''',
@valor_pk3,
'''+@campo+''',
@valor_ant,
@valor_new,
'''+@fecha+''',
'''+@hora+'''
END
')
-- [1.2.5].- Incrementar el contador del Loop
SET @cont = @cont + 1
END -- LOOP - A
-- ===============================================================================================================================
--[1.3].- Eliminar tablas temporales
-- ===============================================================================================================================
IF OBJECT_ID (N'bit11', N'U') IS NOT NULL
DROP TABLE [bit11]
IF OBJECT_ID (N'DELETED_MNT36', N'U') IS NOT NULL
DROP TABLE [DELETED_MNT36]
IF OBJECT_ID (N'INSERTED_MNT36', N'U') IS NOT NULL
DROP TABLE [INSERTED_MNT36]
SET NOCOUNT OFF
END