SQL Server 2012 ALL SERVER Trigger issue
I am having an issue with setting an ALL SERVER trigger. I am using the Adventureworks2012 database to try and get an auditing trigger working. I would like to ideally have the trigger write to a database that I will make called audit2012 if anyone does an update, insert or delete at the DB level.
I have made a simple trigger which writes to a table, when I update etc. My issue is when I try to change it to ALL server. Here is the SQL. If I change the target to ALL SERVER I get the error:
Msg 1098, Level 15, State 1, Procedure EmpTrig, Line 4 The specified event type(s) is/are not valid on the specified target object.
USE [AdventureWorks2012] GO /****** Object: Trigger [HumanResources].[EmpTrig] SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [HumanResources].[EmpTrig] ON ALL SERVER FOR UPDATE, DELETE, INSERT AS BEGIN -- Insert statements for trigger here update [HumanResources].[Employee] set JobTitle = 'UPDATE TRIGGER' where BusinessEntityID = 290 END
Your help is appreciated.
You are trying to create an ALL SERVER trigger that captures table-level actions. This is not possible - see the list of events that are possible here:
It sounds like you should be looking into auditing instead, if you want to globally capture all inserts, updates and deletes. There are plenty of articles and tutorials out there that can help you with this.
If you can't use auditing due to edition limitations, then you could create a trigger on each table. You can automate the creation of these triggers, e.g.
DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql += N' CREATE TRIGGER ' + QUOTENAME(s.name) + '.Audit_' + s.name + '_' + t.name + ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' FOR INSERT, UPDATE, DELETE AS BEGIN INSERT dbo.AuditTable([table], [action], ... other auditing columns ...) SELECT ''' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ''', CASE WHEN EXISTS (SELECT 1 FROM inserted) THEN CASE WHEN EXISTS (SELECT 1 FROM deleted) THEN ''update'' ELSE ''insert'' END ELSE ''delete'' END, ... other auditing information ...; END GO' FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id]; PRINT @sql; -- EXEC sp_executesql @sql;