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

Create TRIGGER [Tr_CreateNewTableByHost]
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
declare @hostname varchar(30)
select @hostname = hostname FROM sys.sysprocesses WHERE spid = @@SPID

    Print 'New Table Created' + ' At ' + cast(Getdate() as nvarchar(20))+' Using Hostname '+@hostname
END
GO
ENABLE TRIGGER [Tr_SatyaCreateNewTable] ON DATABASE
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. 

ENABLE TRIGGER [Tr_SatyaCreateNewTable] ON DATABASE

 Location To Find Trigger

 

 

 

 To Check Trigger Operation Create a New Table

Create table trigger1
(
  id int not null,
  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.