Trigger For Creating New Table In SQL Server

Introduction

Triggers are database operations that are automatically fired when an action such as Insert/Update/Delete is performed on a table or a View in a database.

Description

Triggers are associated with the table or View directly; i.e., each table has its own triggers. The triggers are executed in place of any of Create operations.
 
Create trigger SQL Script
  1. Create TRIGGER [Tr_CreateNewTableByHost]  
  2. ON DATABASE  
  3. FOR CREATE_TABLE  
  4. AS  
  5. BEGIN  
  6. declare @hostname varchar(30)    
  7. select @hostname = hostname FROM sys.sysprocesses WHERE spid = @@SPID    
  8.   
  9.     Print 'New Table Created' + ' At ' + cast(Getdate() as nvarchar(20))+' Using Hostname '+@hostname  
  10. END  
  11. GO  
  12. ENABLE TRIGGER [Tr_SatyaCreateNewTable] ON DATABASE  
  13. GO   
Create trigger SQL description

Here, the name of the trigger for a Create Table operation is named Tr_CreateNewTableByHost on DATABASE. That means when any table is created, the trigger will be fired.

@hostname gets the value of system hostname / SQL Server hostname from the system pre-defined object name sys.sysprocesses.cast(Getdate() as nvarchar(20)) , this part will show you the date time the table created.

To enable a DDL trigger with database scope (ON DATABASE), a user must have ALTER ANY DATABASE DDL TRIGGER permission in the current database. 
  1. ENABLE TRIGGER [Tr_SatyaCreateNewTable] ON DATABASE 
 Location To Find Trigger
 
 
 
 To Check Trigger Operation Create a New Table
  1. Create table trigger1  
  2. (   
  3.   id int not null,  
  4.   describe varchar(40)  

 

Here, you can get the Trigger alert message showing date time and host name based on new table database object creation.
 
Summary

We learned the following in this blog.
  • What is Create trigger.
  • How to enable a DDL trigger with database scope.
  • Find out the user who created the table on which date time.
  • Location to find Trigger using Sql server managaement studio.