SQL Trigger to get updated cell
-
I just got some SQL Query to get updated cell like below:
CREATE TRIGGER LaurenQuantrell
ON Lauren AFTER UPDATE
AS
IF UPDATE(KeyCol)
BEGIN
RAISERROR ('Dont change the key column!!', 16, 1)
ROLLBACK TRANSACTION
RETURN
ENDINSERT Quantrell (KeyCol, ColName, NewData)
SELECT inserted.KeyCol, 'DataCol1', inserted.DataCol1
FROM inserted
INNER JOIN deleted ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol1 <> deleted.DataCol1INSERT Quantrell (KeyCol, ColName, NewData)
SELECT inserted.KeyCol, 'DataCol2', inserted.DataCol1
FROM inserted
INNER JOIN deleted ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol2 <> deleted.DataCol2INSERT Quantrell (KeyCol, ColName, NewData)
SELECT inserted.KeyCol, 'DataCol3', inserted.DataCol3
FROM inserted
INNER JOIN deleted ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol3 <> deleted.DataCol3And, I want to change it into dynamic SQL since it is too much to type the column one by one:
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
ENDAnd it got an error message saying Invalid object name 'Inserted' Can anyone help me? Thanks
- No Signature Available -
-
I just got some SQL Query to get updated cell like below:
CREATE TRIGGER LaurenQuantrell
ON Lauren AFTER UPDATE
AS
IF UPDATE(KeyCol)
BEGIN
RAISERROR ('Dont change the key column!!', 16, 1)
ROLLBACK TRANSACTION
RETURN
ENDINSERT Quantrell (KeyCol, ColName, NewData)
SELECT inserted.KeyCol, 'DataCol1', inserted.DataCol1
FROM inserted
INNER JOIN deleted ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol1 <> deleted.DataCol1INSERT Quantrell (KeyCol, ColName, NewData)
SELECT inserted.KeyCol, 'DataCol2', inserted.DataCol1
FROM inserted
INNER JOIN deleted ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol2 <> deleted.DataCol2INSERT Quantrell (KeyCol, ColName, NewData)
SELECT inserted.KeyCol, 'DataCol3', inserted.DataCol3
FROM inserted
INNER JOIN deleted ON deleted.KeyCol = inserted.KeyCol
WHERE inserted.DataCol3 <> deleted.DataCol3And, I want to change it into dynamic SQL since it is too much to type the column one by one:
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
ENDAnd it got an error message saying Invalid object name 'Inserted' Can anyone help me? Thanks
- No Signature Available -