SQL Server 2012 ALL SERVER Trigger issue

Question

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.

Code:

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.

Aaron Bertrand · Accepted Answer · 2013-12-09T15:30:04.083

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:

DDL Events (MSDN)

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;

I am using sql server 2012 express and am constrained to using this. Is it possible to do without auditing. I have looked into auditing but it is not possible to do audit on express, and also i have tried to use profilers, yet the non pay ones are not very useful to me. Is there anything else I can do to capture DDL events? — , Dec 09 '13 at 14:50
I would create a trigger on each table you want to audit. There's no way to capture DML events (inserts, updates and deletes are not DDL events) at the server level. — Aaron Bertrand, Dec 09 '13 at 15:02

SQL Server 2012 ALL SERVER Trigger issue

1 Answers1