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