Total Page Preview:   000000001466

What is a Trigger and type of trigger in SQL server?

  • It is a precompiled set of TSQL statements that are automatically executed on a particular DDL, DML or log-on event.
  • Triggers do not have any parameters or return statement.
  • Triggers are the only way to access to the INSERTED and DELETED tables (aka. Magic Tables). ? You can DISABLE/ENABLE 
Triggers instead of DROPPING them:
  • DISABLE TRIGGER <name> ON <table/view name>/DATABASE/ALL SERVER
  • ENABLE TRIGGER <name> ON <table/view name>/DATABASE/ALL SERVER
Types of Triggers:

1. DML Trigger

DML Triggers are invoked when a DML statement such as INSERT, UPDATE, or DELETE occur which modify data in a specified TABLE or VIEW.

A DML trigger can query other tables and can include complex TSQL statements. They can cascade changes through related tables in the database.

They provide security against malicious or incorrect DML operations and enforce restrictions that are more complex than those defined with constraints.


2. DDL Trigger

Pretty much the same as DML Triggers but DDL Triggers are for DDL operations. DDL Triggers are at the database or server level (or scope).

DDL Trigger only has AFTER. It does not have INSTEAD OF.


3. Logon Trigger

Logon triggers fire in response to a logon event.

This event is raised when a user session is established with an instance of SQL server. Logon TRIGGER has server scope.

inserted and deleted tables (aka. magic tables):
  • They are tables that you can communicate with between the external code and trigger body.
  • The structure of inserted and deleted magic tables depends upon the structure of the table in a DML statement.
  • UPDATE is a combination of INSERT and DELETE, so its old record will be in the deleted table and its new record will be stored in the inserted table.




Thank You

About Author

Brijesh Kumar

Database Developer

I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall and Database Migration On-Premise to Azure Cloud.
LinkedIn :





Recent Posts

How to get the alternate rows or records from table in sql server
Data Warehouse Three Tier Architecture
SQL Server - Fragmentation on azure sql database
Limitations of Azure SQL Database
PIVOT and UNPIVOT in SQL Server with Example
SQL Server - Find unused indexes on azure SQL database scripts
SQL Server - Find all index with key columns on Azure SQL Database
Find Resource usage in Azure SQL Database using script or T-SQL
How to Set client IP Address using the firewall security in Azure SQL Using Portal
What are the types of User Define Function and difference between a nested UDF and recursive UDF in SQL server?
Top 16 performance query optimization techniques in SQL Database?
Top 60 SQL Server Interview Questions Answers for freshers and experienced
What is a Trigger and type of trigger in SQL server?
How many types of dimension tables available in data warehouse?
What are the differences between OLTP and OLAP?
Data warehouse Interview Questions Answers for freshers and experienced
What is Table Variable? Explain its advantages and disadvantages?
What are the different types of Error Handling in SQL Server?
Cloud Data Engineer SQL Server Interview Questions Answers for freshers and experienced
What are characteristics and advantages of stored procedure?

Facebook User: