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.

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.

Answers


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;

Need Your Help

Handling data from intent after orientation change

android image android-intent null orientation

My app is set to only display in portrait mode. The problem is that I need to use a camera and a gallery intent and you can't specify those apps to be the same orientation so some funky stuff happe...

Selection sort in ascending order of array

java arrays sorting selection

I can get my program to sort in descending, but not ascending. I can't get my code to switch between them. Shouldn't I just be able to swicth the -- to ++? I need to use selection sort. In my bubbl...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.