Create Trigger on Temp Tables and System Tables in SQL Server

SQL Server does not allow to create triggers on Temporary tables that included local and global temporary tables.

If you try to create trigger , this is the error you will get.

USE [Database]
GO 
--Create SalePerson Table 
CREATE TABLE #TempSalePerson(
  SalePersonID INT PRIMARY KEY,
  FirstName VARCHAR(25),
  LastName VARCHAR(25),
) 
GO 
--Create DML( Insert) Trigger 
 CREATE TRIGGER dbo.Trg_InsertSalePerson 
ON #TempSalePerson 
AFTER INSERT AS
BEGIN
SELECT * FROM INSERTED
 END 
GO


Msg 167, LEVEL 15, State 1, PROCEDURE Trg_InsertSalePerson, Line 2
Cannot CREATE TRIGGER ON a TEMPORARY object.

This is true for local or global temp tables.

The second part of the post is , if You can create Trigger on System tables in SQL Server. That is kind of true. You can create the trigger on system tables in SQL Server. I was able to create trigger on 147 system tables in MSDB and also I created the triggers in Master databases on System Tables.  For 5 tables as they have columns data type text, ntext etc. in MSDB , The query threw me error. I might be able to create trigger on those tables as well if I exclude those columns from Trigger Update clause. There could be system tables which do not provide permission to create trigger as well. Further testing is required.

Error I got for 5 Tables in MSDB as my create trigger is more generic as I did not exclude columns of data type such as text, ntext.

Msg 311, LEVEL 16, State 1, PROCEDURE Trg_sysreplicationalerts, Line 7
Cannot USE TEXT, NTEXT, OR IMAGE columns IN the 'inserted' AND 'deleted' tables.

As we learn that we can create the trigger on system table but I am not sure if the trigger fire. There could be some internal code that will not allow the trigger to fire. So If you decided to create a trigger on system table , test that out. My advise will be , don't create the trigger on system tables as they are used by system and we don't have much information about internals.

Script to Create Trigger on All system tables for test purpose

USE [Database]

GO 
DECLARE @TableName AS VARCHAR(500)
 DECLARE @SchemaName AS VARCHAR(100) 
--Create Trigger on System Tables, Trigger will print the statement 
--and show the record which got updated. 
DECLARE CreateTrigger CURSOR FOR
  SELECT name                   AS TableName,
         Schema_name(schema_id) AS SchemaName
  FROM   sys.tables
  WHERE  is_ms_shipped = 1 
OPEN CreateTrigger 
FETCH Next FROM CreateTrigger INTO @TableName, @SchemaName
 WHILE @@FETCH_STATUS = 0
  BEGIN
      DECLARE @SQL VARCHAR(MAX)=NULL

      SET @SQL='CREATE TRIGGER [' + @SchemaName + '].[TrgTest_'
               + @TableName + ']
ON [' + @SchemaName + '].['
               + @TableName + ']
AFTER Update AS
BEGIN

Print ''Trigger Ran Successfully''
SELECT * FROM INSERTED
END'

      EXEC (@SQL)

      PRINT @SQL

      FETCH Next FROM CreateTrigger INTO @TableName, @SchemaName
  END

CLOSE CreateTrigger 
DEALLOCATE CreateTrigger



Drop triggers on all tables in Database those we created by using above script.

USE [Database]

GO
 DECLARE @TriggerName AS VARCHAR(500) 
-->Drop or Delete All Triggers in a Database in SQL Server
 DECLARE DropTrigger CURSOR FOR
  SELECT TRG.name AS TriggerName
  FROM   sys.triggers TRG
         INNER JOIN sys.tables TBL
                 ON TBL.OBJECT_ID = TRG.parent_id
                    AND TRG.name LIKE 'TRGTest_%' 
OPEN DropTrigger
 FETCH Next FROM DropTrigger INTO @TriggerName 
WHILE @@FETCH_STATUS = 0
  BEGIN
      DECLARE @SQL VARCHAR(MAX)=NULL

      SET @SQL='Drop Trigger ' + @TriggerName

      PRINT 'Trigger ::' + @TriggerName
            + ' Droped Successfully'

      EXEC (@SQL)

      PRINT @SQL

      FETCH Next FROM DropTrigger INTO @TriggerName
  END

CLOSE DropTrigger 
DEALLOCATE DropTrigger