This is a follow up to the posts about audit triggers. I wrote about them some time ago, on my previous blogging platform. The first post discussed COLUMS_UPDATED() function that is very useful in triggers if you want to find out which columns are affected by DML operation. Second post showed how you can create an audit framework that subsequently is used to log what happens to data when users modify it.
Now, there are situations, when you want to make changes to a table, but you don’t want to have these operations logged. And I am not talking about criminal activities – but for example, a table is populated daily by ETL process which updates or inserts thousands of rows. Sometimes it doesn’t make sense to log all these operations – after all triggers incur some performance degradation on the database.
There are several ways to disable a trigger. First, you can use disable trigger statement This approach has one drawback – when you disable trigger, it is disabled for every user who modifies the data. So you may loose audit entries while your ETL is running. In some scenarios it’s not an option.
Alternatively you can implement trigger to check for certain condition and decide whether DML operation should be audited or not. There are several ways of doing this. Two are described by Itzik Ben-Gan in his excellent Inside SQL Server 2005 - T-SQL Programming. Triggers can check if a table with specific name exists in the temp database or use session context. The disadvantage of these options is that you have to modify code external to triggers, for example your stored procedures or batches.
You can check if there is certain entry in a configuration table. For example you can have table Audit.tIgnoreUsers which will contain user names for which you don't want to audit. Then, there’s a simple query to decide if the trigger should proceed or not:
1: if(exists (select 1 from Audit.tIgnoreUsers where UserName = suser_name()))
2: return;
Alternatively, you can check if particular user belongs to particular role. To do this, you have to create role first and add user that you want to exclude from auditing.
1: if not exists (select 1 from sys.database_principals where name = 'ExcludeFromAudit' and type='R')
2: exec sp_executesql N'create role ExcludeFromAudit authorization dbo'
3: 4: go
5: 6: exec sp_addrolemember 'ExcludeFromAudit', 'ETLLogin'
7: 8: go
9: 10: raiserror('Logins skipped in auditing: ', 10, 1) with nowait;
11: 12: exec sp_helprolemember 'ExcludeFromAudit'
Then, in the audit trigger you just add code somewhere on the top of the trigger:
1: if(is_rolemember('ExcludeFromAudit') = 1)
2: begin
3: --print 'Audit skipped for user ' + suser_name()
4: return
5: endAnd that’s it. I find this way easier to manage, as typically users do not have rights to modify roles. The good practice is to have separate login for ETL from other logins, so you can audit every action that for example front end users perform, but the ETL stays almost unaffected.
Please note that if you happen to be sysadmin and want to test the is_rolemember on a user defined role, it will always return 0. Quite confusing, I know. There is a Connect item for IS_MEMBER function which behaves in the similar way, by design.
