SQL Server

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)  
  5. ) 
 

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.
Membership not found