dimanche 7 octobre 2018

If Condition Not Supported In DDL Triggers

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