Database

CREATE TRIGGER LaurenQuantrell
ON Lauren AFTER UPDATE
AS
IF UPDATE(KeyCol)
BEGIN
RAISERROR ('Dont change the key column!!', 16, 1)
ROLLBACK TRANSACTION
RETURN
END

INSERT Quantrell (KeyCol, ColName, NewData)
SELECT inserted.KeyCol, 'DataCol1', inserted.DataCol1
FROM inserted
INNER JOIN deleted ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol1 <> deleted.DataCol1

INSERT Quantrell (KeyCol, ColName, NewData)
SELECT inserted.KeyCol, 'DataCol2', inserted.DataCol1
FROM inserted
INNER JOIN deleted ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol2 <> deleted.DataCol2

INSERT Quantrell (KeyCol, ColName, NewData)
SELECT inserted.KeyCol, 'DataCol3', inserted.DataCol3
FROM inserted
INNER JOIN deleted ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol3 <> deleted.DataCol3

DECLARE @COLUMN_NUMBER AS integer
DECLARE @COLUMN_TOTAL AS integer
DECLARE @COLUMN_NAME AS nvarchar(100)
DECLARE @SQL AS nvarchar(1024)

SET @COLUMN_NUMBER = 1
SET @COLUMN_TOTAL = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'transaksi_request')

WHILE @COLUMN_NUMBER <= @COLUMN_TOTAL
BEGIN
SET @COLUMN_NAME = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'transaksi_request' AND ORDINAL_POSITION = @COLUMN_NUMBER)
SET @SQL =
'INSERT transaksi_requestlog(log_date, log_user, log_id, log_column)
SELECT ''' + CAST(GETDATE() AS nvarchar(20)) + ''', Inserted.request_modifiedby, Inserted.request_id, ''' + @COLUMN_NAME + '''
FROM Inserted
INNER JOIN Deleted ON Deleted.request_id' + ' = Inserted.request_id' + '
WHERE Inserted.' + @COLUMN_NAME + ' <> Deleted.' + @COLUMN_NAME
EXEC(@SQL)

SET @COLUMN_NUMBER = @COLUMN_NUMBER + 1
END