I have a DDL trigger for monitoring the changes made to specific tables in SQL Server database. But it is not working. Surprising thing is that when I remove table filter (i.e. the IF statement) then it works. Even I tried by putting condition like 1 = 1, it also doesn't work. So my observation is IF condition is not working here for some reasons. Please anybody help, what is going wrong here. Following is DDL trigger.
CREATE TRIGGER [SQLObjectMonitoring] ON DATABASE FOR create_table, alter_table, drop_table AS BEGIN SET NOCOUNT ON
DECLARE @data XML = EVENTDATA()
DECLARE @DatabaseName [varchar](256) = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')
DECLARE @EventType [varchar](50) = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)') -- value is case-sensitive
DECLARE @ObjectName [varchar](256) = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)')
DECLARE @ObjectType [varchar](25) = @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)')
DECLARE @SQLCommOR [varchar](max) = @data.value('(/EVENT_INSTANCE/TSQLCommOR)[1]', 'varchar(max)')
DECLARE @LoginName [varchar](256) = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
IF(@ObjectName = 'TABLE1' OR @ObjectName = 'TABLE2' OR @ObjectName = 'TABLE3' OR @ObjectName = 'TABLE4' OR @ObjectName = 'TABLE5')
BEGIN
INSERT INTO dbo.SQLObjectLog(databasename, eventtype,objectname, objecttype, sqlcommOR, loginname, eventdate)
VALUES(@DatabaseName, @EventType, @ObjectName, @ObjectType, @SQLCommOR, @LoginName, GETDATE())
END
END
Aucun commentaire:
Enregistrer un commentaire