July 9, 2010 at 5:36 am
Hi,
I have a log table time_log
create table time_log(
logID int identity (1,1) primary key
,taskID int
,userID varchar(16)
,logTable varchar(100)
,logColumn varchar(100)
,oldValue varchar(500)
,newValue varchar(500)
,insertedOn datetime default getdate()
,insertedBy varchar(32) default suser_sname()
,insertApplication varchar(128) default app_name()
)
On every UPDATE to most columns done to a table myTable, it needs to be logged into time_log table from a trigger.
CREATE TABLE [dbo].[myTable](
[user_id] [varchar](16) NOT NULL,
[user_supervisor_id] [varchar](16) NULL,
[user_name] [varchar](60) NOT NULL,
[user_abbr] [varchar](8) NOT NULL,
[rate_id] [int] NOT NULL,
[user_active] [char](1) NOT NULL DEFAULT ('Y')
) ON [PRIMARY]
GO
INSERT myTable
SELECT 1,'ASD','john',null,1,'N'
UNION ALL
SELECT 2,'DEF','miller','MI',2,'Y'
UNION ALL
SELECT 3,'FGH','San','KB',1,'Y'
for this, i was trying like this inside a trigger
if update(col_name)
INSERT INTO time_log(....)values(...)
the code will be repeated for each column,
to avoid I used COALESCE like below
CREATE TRIGGER dbo.myTable_OnUpdate
ON dbo.myTable
AFTER UPDATE AS
--author name
--test stuff
BEGIN
SET NOCOUNT ON
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL=COALESCE(@SQL,'')+CAST('IF UPDATE(' + COLUMN_NAME + ')
INSERT INTO dbo.time_log(taskID,userID,logTable,logColumn,oldValue,newValue)
SELECT null,isnull(i.user_id,d.user_id),''myTable'','''+
COLUMN_NAME+''',CAST(d.'+COLUMN_NAME+' AS VARCHAR),CAST(i.'+COLUMN_NAME+' AS VARCHAR)
FROM inserted i join deleted d on i.user_id=d.user_id;
'+char(10)+char(13) AS VARCHAR(MAX))
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTable'
--PRINT @SQL
EXEC(@SQL)
END
Error:
when i try to update the table
update myTable
set
user_supervisor_id='XYZ'
,rate_id='3'
,user_active='Y'
where user_id='3'
i got the error
Msg 140, Level 15, State 1, Line 1
Can only use IF UPDATE within a CREATE TRIGGER statement.
How Can I log in this way?
Thanks,
KB
Thanks,
Santhosh
July 9, 2010 at 5:43 am
You can't use IF UPDATE(column) in dynamic sql. If you don't want to type the code for each column, generate it and paste it into the trigger.
You could also check for different columns between INSERTED (new values) and DELETED (old values).
-- Gianluca Sartori
July 9, 2010 at 5:43 am
Is scripting and pasting in the trigger the only way to achieve this?
Thanks,
KB
Thanks,
Santhosh
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply