Category Archives: Sql Server Tutorial

Logon Triggers in Sql Server

What is Logon Trigger?

Logon Triggers are special kind of Stored Procedure or an operation that gets executed automatically in response to a LOGON event. They get executed only after the successful authentication but before the user session is established. If authentication fails the logon triggers will not be fired.

Caution! : Logon Triggers are very useful feature, but a small mistake in the Logon Trigger may cause database server un-accessible to any user including SA user. For example in a Logon trigger you are referring to a non existent table or database will allow you to create it. But once it is created then no one will be able to connect to the server as Logon trigger errors out. So it is best advised to check whether you are able to establish a Dedicated Administrator Connection (DAC) with SQL Server Management Studio. Because in case you have created a wrong trigger and not able to to connect, then DAC connection comes for your rescue. As the DAC connection will not trigger the LOGON trigger execution and you will be able to connect to Sql Server and after connecting disable the incorrect trigger.

Uses of Logon Triggers

Below are the some of the usecase scenarios where LOGON triggers will be usefull:

  • To audit and control server sessions
  • Restricting logins to Sql Server
  • Restricting the number of sessions for a specific login
  • Restricting user from logging-in outside permitted hours

Understanding Logon Triggers with extensive list of examples

Create a login TestUser by executing the following script:

-- Create a login 
CREATE LOGIN TestUser WITH PASSWORD = 'TestPass1'

[ALSO READ] Data Manipulation Language (DML) Triggers

EXAMPLE 1: This example Logon trigger blocks the user TestUser from connecting to Sql Server after office hours.

Execute the following script to create a Logon trigger which blocks the TestUser user from connecting to Sql Server after office hours

CREATE TRIGGER LimitConnectionAfterOfficeHours
ON ALL SERVER FOR LOGON 
AS
BEGIN
 IF ORIGINAL_LOGIN() = 'TestUser' AND
  (DATEPART(HOUR, GETDATE()) < 9 OR 
                  DATEPART (HOUR, GETDATE()) > 18)
 BEGIN
  PRINT 'You are not authorized to login after office hours'
  ROLLBACK
 END
END

Now try to connect to Sql Server using the TestUser user after office hours

LogonTriggerExampleAfterHours

From the above result we can see that the Logon Trigger LimitConnectionAfterOfficeHours is blocking the TestUser from connecting to Sql Server after office hours

In case of Logon Triggers the PRINT statement result in the trigger will be logged to the Sql Server Error Log. Let us login as different user and check whether PRINT message in the Logon trigger is logged into the Error Log during the above failure attempt:

LogonTriggerErrorLog

Drop the above Logon Trigger by executing the following statement:

DROP TRIGGER LimitConnectionAfterOfficeHours ON ALL SERVER

[ALSO READ] Data Definition Language (DDL) Triggers

EXAMPLE 2: This example Logon Trigger blocks a user from establishing more than two user sessions at any given point of time

Execute the following script to create a Logon Trigger which blocks the TestUser from establishing more than two user sessions at any given point of time

CREATE TRIGGER LimitMultipleConcurrentConnection
ON ALL SERVER WITH EXECUTE AS 'sa' 
FOR LOGON
AS
BEGIN
 IF ORIGINAL_LOGIN() = 'TestUser' AND
  (SELECT COUNT(*) FROM   sys.dm_exec_sessions
   WHERE  Is_User_Process = 1 
    AND Original_Login_Name = 'TestUser') > 2
 BEGIN
  PRINT 'You are not authorized to login, as you already have two active user sessions'
  ROLLBACK
 END
END

Now try to establish more than two concurrent sessions for the TestUser:

LogonTriggerBlockMultipleConcurrentSessions

From the above image we can see that the TestUser was able to have two concurrent new query sessions, but the attempt to open the third session is blocked by the trigger.

Below is the error logged in the Sql Server Error Log:

LogonTriggerErrorLogMultipleSessions

In the above example trigger you can observe that, during trigger creation I have added the clause EXECUTE AS ‘sa’. The reason for this is only the users with VIEW SERVER STATE permission on the server can see all the executing session on the instance of Sql Server. And the users who don’t have this permission can see only the current session. Because of this I am executing the above trigger as ‘sa’. An alternative option for this is grant TestUser the VIEW SERVER STATE permission using GRANT VIEW SERVER STATE TO TestUser statement.

Drop the above Logon Trigger by executing the following statement:

DROP TRIGGER LimitMultipleConcurrentConnection ON ALL SERVER

[ALSO READ]
Introduction to Triggers
Data Manipulation Language (DML) Triggers
Data Definition Language (DDL) Triggers
INSERTED and DELETED Logical Tables

Data Definition Language (DDL) Triggers in Sql Server

What is DDL Trigger?

Data Definition Language (DDL) Triggers are special kind of Stored Procedure or an operation that gets executed automatically when a DDL Statements like CREATE, ALTER, DROP, GRANT, DENY, REVOKE, and UPDATE STATISTICS statements are executed. Certain system stored procedures that perform DDL like operations can also fire DDL triggers.

Scope of DDL Triggers

DDL triggers can be DATABASE scoped or SERVER scoped. The DDL triggers with Server level scope gets fired in response to a DDL statement with server scope like CREATE DATABASE, CREATE LOGIN, GRANT_SERVER, ALTER DATABASE, ALTER LOGIN etc. Where as DATABASE scoped DDL triggers fire in response to DDL statement with DATABASE SCOPE like CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, ALTER TABLE, ALTER PROCEDURE, ALTER FUNCTION etc.

[ALSO READ] Data Manipulation Language (DML) Triggers in Sql Server

Uses of DDL Triggers

DDL triggers will be very handy to audit and control the DDL changes. Below are such example scenarios:

  • Track the DLL changes
    • Track the DDL T-Sql statement which is fired
    • Who has fired the DDL statement. For example we may be interested in identifying who has dropped the table.
    • When the DDL statement is fired etc
  • Block user from doing some DDL changes like DROP TABLE, DROP PROCEDURE etc
  • Allow DDL changes only during specified window (i.e. only during particular hours of the day)

Understand DDL Triggers with extensive list of examples

To understand DDL triggers with extensive list of examples, let us create a demo database with a Table and a Stored Procedure by executing the following Script:

--Create Demo Database
CREATE DATABASE SqlHintsDDLTriggersDemo
GO
USE SqlHintsDDLTriggersDemo
GO
--Create Customer Table
CREATE TABLE Customer 
( CustomerId INT IDENTITY (1, 1) NOT NULL ,
  FirstName NVARCHAR(50), LastName NVARCHAR(50))
GO
--Create a Stored Procedure
CREATE PROCEDURE GetCustomers
AS
BEGIN
	SELECT * FROM Customer
END
GO

EXAMPLE 1: This example DDL trigger blocks user FROM modifying or dropping any Table in the SqlHintsDDLTriggersDemo database

Execute the following statement to create a Trigger which fires in response to ALTER TABLE and DROP TABLE statements

USE SqlHintsDDLTriggersDemo
GO
CREATE TRIGGER DDLTriggerToBlockTableDDL
ON DATABASE
FOR DROP_TABLE,ALTER_TABLE
AS
BEGIN
 PRINT 'Disable trigger DDLTriggerToBlockTableDDL to drop or alter tables'
 ROLLBACK
END

Now try to drop the Customer table by executing the following statement

DROP TABLE Customer 

RESULT:

Disable trigger DDLTriggerToBlockTableDDL to drop or alter tables
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

From the above result we can see that the DDL trigger DDLTriggerToBlockTableDDL is blocking the user from dropping the Customer table

Let us try to execute the following statement to add DOB column to the Customer Table

ALTER TABLE dbo.Customer ADD DOB DATETIME

RESULT:

Disable trigger DDLTriggerToBlockTableDDL to drop or alter tables
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

From the above result we can see that the DDL trigger DDLTriggerToBlockTableDDL is blocking the user from adding DOB column to the Customer table

In Sql Server we have an option to disable the trigger without dropping it. Execute the below script to disable the trigger DDLTriggerToBlockTableDDL:

DISABLE TRIGGER DDLTriggerToBlockTableDDL ON DATABASE

Now try to re-execute the following statement to add the DOB column to the Customer Table

ALTER TABLE dbo.Customer ADD DOB DATETIME
GO
SELECT * FROM dbo.Customer

RESULT
ALTER TABLE After Disabling Trigger 1

Execute the following statement to enable back the Disabled trigger DDLTriggerToBlockTableDDL

ENABLE TRIGGER DDLTriggerToBlockTableDDL ON DATABASE

Now try to execute the following statement drop the newly added column DOB from the Customer table by executing the following statement

ALTER TABLE dbo.Customer DROP COLUMN DOB

RESULT:

Disable trigger DDLTriggerToBlockTableDDL to drop or alter tables
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

We can drop the DDLTriggerToBlockTableDDL trigger by executing the following statement

DROP TRIGGER DDLTriggerToBlockTableDDL ON DATABASE

[ALSO READ] Logon Triggers in Sql Server

EXAMPLE 2: This example DDL trigger blocks user from creating, modifying or dropping any Stored Procedures in the SqlHintsDDLTriggersDemo database

USE SqlHintsDDLTriggersDemo
GO
CREATE TRIGGER DDLTriggerToBlockProcedureDDL
ON DATABASE
FOR DDL_PROCEDURE_EVENTS
AS
BEGIN
 PRINT 'Disable trigger DDLTriggerToBlockProcedureDDL to create, alter or drop procedures'
 ROLLBACK
END

After creating the above trigger, try to drop the stored procedure GetCustomers by executing the following statement

DROP PROCEDURE GetCustomers

RESULT:

Disable trigger DDLTriggerToBlockProcedureDDL to create, alter or drop procedures
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

In the above example trigger, the trigger action DDL_PROCEDURE_EVENTS is an aggregate event name for the individual trigger event names: CREATE_PROCEDURE, ALTER_PROCEDURE and DROP_PROCEDURE. The system stored procedure SYS.TRIGGER_EVENT_TYPES lists out all the trigger events.

If you want to just block dropping of the stored procedure then in the FOR clause just mention the event DROP_PROCEDURE instead of DDL_PROCEDURE_EVENTS

The below query explains that the DDL_PROCEDURE_EVENTS is an aggregate event name for the individual trigger event names: CREATE_PROCEDURE, ALTER_PROCEDURE and DROP_PROCEDURE.

SELECT * FROM SYS.TRIGGER_EVENT_TYPES 
WHERE parent_type = 10024 or type = 10024
RESULT: TriggerEventTypes

We can drop this stored procedure DDL trigger by executing the following statement:

DROP TRIGGER DDLTriggerToBlockProcedureDDL ON DATABASE

EXAMPLE 3: This example server level trigger blocks user from creating, altering or dropping database

USE MASTER
GO
CREATE TRIGGER DDLTriggerToBlockDatabaseDDL
ON ALL SERVER
FOR DDL_DATABASE_EVENTS
AS
BEGIN
 PRINT 'Disable trigger DDLTriggerToBlockDatabaseDDL to create, alter or drop database'
 ROLLBACK
END

Note: Server scoped triggers need to be created in the MASTER database

After creating the above trigger, try to create the database TestDemoDB

CREATE DATABASE TestDemoDB

RESULT:

Disable trigger DDLTriggerToBlockDatabaseDDL to create, alter or drop database
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

From the above result we can see that the DDL trigger DDLTriggerToBlockDatabaseDDL is blocking the user from Creating the database

In the above example trigger, the trigger action DDL_DATABASE_EVENTS is an aggregate event name for the individual trigger event names: CREATE_DATABASE, ALTER_DATABASE and DROP_DATABASE. The system stored procedure SYS.TRIGGER_EVENT_TYPES lists out all the trigger events.

If you want to just block creating of the new database then in the FOR clause JUST mention the event CREATE_DATABASE instead of DDL_DATABASE_EVENTS

The below query explains that the DDL_DATABASE_EVENTS is an aggregate event name for the individual trigger event names: CREATE_DATABASE, ALTER_DATABASE and DROP_DATABASE.

SELECT * FROM SYS.TRIGGER_EVENT_TYPES
WHERE parent_type = 10004 or type = 10004

RESULT:
DDLTriggerEventTypes

We can drop this DDL trigger by executing the following statement

DROP TRIGGER DDLTriggerToBlockDatabaseDDL ON ALL SERVER

[ALSO READ] INSERTED and DELETED Logical Tables in Sql Server

EXAMPLE 4: This example explains how we can use DDL triggers to log the stored procedure DDL statement, statement execution time, the user who has fired the statement etc

Let us execute the following statement to create a Audit log table: LogDDLEvents, to capture the Stored Procedure DDL statement

CREATE TABLE dbo.LogDDLEvents
( 	
 EventTime DATETIME,
 LoginName VARCHAR(50),
 TSQLCommand NVARCHAR(MAX) 
)

Now execute the following statement to create a SP DDL actions. This SP is using the EVENTDATA() function to capture the triggering SP DDL statement into the audit log table LogDDLEvents

USE SqlHintsDDLTriggersDemo
GO
CREATE TRIGGER DDLTriggerForSPDDL
ON DATABASE
FOR DDL_PROCEDURE_EVENTS
AS
BEGIN
 SET NOCOUNT ON
 DECLARE @EventData XML = EVENTDATA()
 INSERT INTO dbo.LogDDLEvents(EventTime,LoginName, TSQLCommand)	
 SELECT @EventData.value('(/EVENT_INSTANCE/PostTime)[1]'
                                  , 'DATETIME'),
  @EventData.value('(/EVENT_INSTANCE/LoginName)[1]'
                                  , 'VARCHAR(50)'),
 @EventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]'
                                  , 'NVARCHAR(MAX)')
END

Now try to create a Stored Procedure by executing the following statement

CREATE PROCEDURE GetAllCustomers
AS
BEGIN
    SELECT * FROM Customers
END

After executing the above statement let us verify the audit table: LogDDLEvents data by executing the following statement

SELECT * FROM dbo.LogDDLEvents

RESULT
DDL Trigger Audit Log Table
Let us check whether apart from adding to the audit log table: LogDDLEvents, the Stored Procedure is created or not by executing the following statement?

SP_HELPTEXT GetAllCustomers

StoredProcedure

We can drop this DDL trigger by executing the following statement

DROP TRIGGER DDLTriggerForSPDDL ON DATABASE

[ALSO READ]
Introduction to Triggers
Data Manipulation Language (DML) Triggers
INSERTED and DELETED Logical Tables
Logon Triggers

Data Manipulation Language (DML) Triggers in Sql Server

What is DML Trigger?

Data Manipulation Language (DML) Triggers are special kind of Stored Procedure or an operation that gets executed automatically when a DML operation like INSERT, UPDATE OR DELETE is fired on a Table or View.

To understand DML triggers with extensive list of examples let us create a Demo database and a Table by executing the following script:

--Create Demo Database
CREATE DATABASE SqlHintsDMLTriggerDemo
GO
USE SqlHintsDMLTriggerDemo
GO
--Create Customer Table
CREATE TABLE Customer 
( CustomerId INT IDENTITY (1, 1) NOT NULL ,
  FirstName NVARCHAR(50), LastName NVARCHAR(50))
GO

[ALSO READ]Data Definition Language (DDL) Triggers in Sql Server

Types of DML Triggers

Following are the two types of DML Triggers

1. AFTER Triggers

AFTER Triggers are executed after the DML statement completes but before it is committed to the database. AFTER Triggers if required can rollback it’s actions and source DML statement which invoked it.

Example 1: Create an AFTER Trigger AfterTriggerExample1 for the DML INSERT, UPDATE and DELETE OPERATION on the Customer table by executing the following script

CREATE TRIGGER AfterTriggerExample1
ON Customer
FOR INSERT, UPDATE, DELETE
AS 
BEGIN
     PRINT 'AFTER Trigger AfterTriggerExample1 executed!'
END

Now try inserting a record into the Customer table by executing the following query

INSERT INTO dbo.Customer ( FirstName, LastName )
VALUES('Basavaraj','Biradar')

RESULT:
AFTER Trigger Example 1

From the above result we can see that the INSERT statement has resulted in the automatic execution of the AFTER trigger AfterTriggerExample1

Let us verify by executing the following query, whether the above INSERT statement has inserted a record into the Customer table

SELECT * FROM dbo.Customer

RESULT:
INSERT RESULT ON A TABLE WITH Trigger

From the above results we can see that on a Table with a AFTER trigger, the trigger will be executed once the triggering DML action completes and before it is committed

Note in the above example just to make the understanding simple, in the Trigger just written a PRINT statement. In real world we don’t write triggers to return some message or results. They are useful for various aspects like: i) Auditing the data changes (i.e. log the changes to the table data in an audit table) ii) Enforce business rules and data integrity etc.

A table can multiple AFTER triggers and for the same trigger action too we can have multiple triggers.

[ALSO READ] Logon Triggers in Sql Server
Example 2: Multiple AFTER Triggers

Let us execute the following script to create a one more trigger on the Customer table and this time for the triggering action INSERT only

CREATE TRIGGER AfterTriggerExample2
ON Customer
FOR INSERT
AS 
BEGIN
     PRINT 'AFTER Trigger AfterTriggerExample2 executed!'
END

Now try inserting a record into the Customer table by executing the following query

INSERT INTO dbo.Customer ( FirstName, LastName )
VALUES('Kalpana','Patil')

RESULT:
Multiple AFTER Trigger Example

From the above result we can see that the AFTER trigger which is created in Example 1 (i.e. AfterTriggerExample1) and another AFTER trigger which we created in this example (i.e. AfterTriggerExample2) are executed when we try to insert a record in the Customer table. As both these triggers are configured to execute for the trigger action INSERT on the Customer table.

Let us verify by executing the following query, whether the above INSERT statement has inserted a record into the Customer table

SELECT * FROM dbo.Customer

RESULT:
INSERT RESULT ON A TABLE WITH Multiple Trigger

If a table has multiple AFTER triggers, then we can specify which trigger should be executed first and which trigger should be executed last using the stored procedure sp_settriggerorder. All the other triggers are executed in an undefined order which can’t be controlled.

Drop the two AFTER triggers created in the above examples by executing the following script

DROP TRIGGER AfterTriggerExample1
DROP TRIGGER AfterTriggerExample2

INSERTED and DELETED Logical Tables

DML Triggers have access to the two special logical Tables named INSERTED and DELETED, these are the temporary tables whcih Sql Server manages. These tables structure will be same as that of the table on which the DML action is fired and holds the old and new values of the rows which are modified by the DML statement.

DELETED logical table will hold the rows which are delted from the trigger table (i.e. the table on which the trigger is defined) by the DELETE or UPDATE operation. An UPDATE DML operation is internally executed as first the deletion of the existing record and then insertion of the new record.

INSERTED logical table will hold the rows which are inserted by the INSERT and UPDATE statement.

Below table depicts the contents of the INSERTED and DELETED logical tables for three DML operations

[ALSO READ] INSERTED and DELETED logical tables with extensive list of examples

2. INSTEAD OF Triggers

INSTEAD OF Triggers are the triggers which gets executed automatically in place of triggering DML (i.e. INSERT, UPDATE and DELETE) action. It means if we are inserting a record and we have a INSTEAD OF trigger for INSERT then instead of INSERT whatever action is defined in the trigger that gets executed.

Example 1: Create an INSTEAD OF Trigger on the Customer Table for the INSERT DML operation by executing the following script

 
CREATE TRIGGER INSTEADOFTriggerExample 
ON Customer
INSTEAD OF INSERT
AS
BEGIN
	PRINT('Instead of trigger is Executed!')
END 

Execute the following INSERT statement to check the behavior of the INSTEAD OF trigger

 
INSERT INTO dbo.Customer ( FirstName, LastName )
VALUES('Monty','Biradar') 

RESULT:
INSTEAD OF Trigger Example

Let us verify whether the above INSERT statement has inserted a record in the Customer table by executing the following SELECT statement

 
SELECT * FROM dbo.Customer  

RESULT:
Post INSERT1

From the result we can see that INSERT statement triggered the execution of the INSTEAD OF trigger instead of inserting the record into the Customer table.

Example 2: Try to add one more INSTEAD of Trigger on the Customer table for the INSERT DML operation by executing the following script

CREATE TRIGGER AnotherINSTEADOFTrigger 
ON Customer
INSTEAD OF INSERT
AS
BEGIN
    PRINT 'Instead of trigger is Executed!'
END

RESULT:

Msg 2111, Level 16, State 1, Procedure AnotherINSTEADOFTrigger, Line 1
Cannot create trigger ‘AnotherINSTEADOFTrigger’ on table ‘Customer’ because an INSTEAD OF INSERT trigger already exists on this object.

From the result we can see that Sql Server doesn’t allow multiple INSTEAD OF triggers on one table for the same DML triggering action

Example 3: This example demonstrates that a Table can have one INSTEAD of Trigger for each Triggering DML actions INSERT, UPDATE and DELETE

Let us execute the following statement to add an INSTEAD OF Trigger on the Customer table for the UPDATE DML triggering action

CREATE TRIGGER OneMoreINSTEADOFTrigger 
ON Customer
INSTEAD OF UPDATE
AS
BEGIN
    PRINT 'Instead of trigger is Executed!'
END

RESULT:
INSTEAD OF Trigger for Update

From the result we can confirm that: At max we can have one INSTEAD OF Trigger on a Table for each DML triggering actions

[ALSO READ]
Introduction to Triggers
Data Definition Language (DDL) Triggers in Sql Server
INSERTED and DELETED Logical Tables
Logon Triggers

Triggers in Sql Server

This article gives introduction to Triggers in Sql Server. This is the first article in the series of articles on Triggers in Sql Server. Below is the complete list of articles in this series. Articles in this series of articles on Trigger explain the various types of triggers with extensive list of easy to understand simple examples

What is Trigger?

Trigger is a Special kind of Stored Procedure or an operation that gets executed automatically when an event occurs in the database.

Types of Triggers

Following are different types of triggers in Sql Server

  • Data Manipulation Language (DML) Triggers

    DML triggers are executed when a DML operation like INSERT, UPDATE OR DELETE is fired on a Table or View.

    DML Triggers are of the following two types
    AFTER Triggers

    AFTER Triggers are executed after the DML statement completes but before it is committed to the database. AFTER Triggers if required can rollback it’s actions and source DML statement which invoked it.

    INSTEAD OF Triggers

    INSTEAD OF Triggers are the triggers which gets executed automatically in place of triggering DML (i.e. INSERT, UPDATE and DELETE) action. It means if we are inserting a record and we have a INSTEAD OF trigger for INSERT then instead of INSERT whatever action is defined in the trigger that gets executed.

  • Data Definition Language (DDL) Triggers

    DDL Triggers are executed when a DDL Statements like CREATE, ALTER, DROP, GRANT, DENY, REVOKE, and UPDATE STATISTICS statements are executed. Certain system stored procedures that perform DDL like operations can also fire DDL triggers.

    DDL triggers can be DATABASE scoped or SERVER scoped. The DDL triggers with Server level scope gets fired in response to a DDL statement with server scope like CREATE DATABASE, CREATE LOGIN, GRANT_SERVER, ALTER DATABASE, ALTER LOGIN etc. Where as DATABASE scoped DDL triggers fire in response to DDL statement with DATABASE SCOPE like CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, ALTER TABLE, ALTER PROCEDURE, ALTER FUNCTION etc.

  • LOGON Triggers

    Logon Triggers gets executed automatically in response to a LOGON event. They get executed only after the successful authentication but before the user session is established. If authentication fails the logon triggers will not be fired.

  • CLR Triggers

    CLR Triggers are based on the Sql CLR. We can write DML and DDL triggers by using the Supported .NET CLR languages like C#, VB.NET etc. CLR triggers will useful if require heavy computation in the trigger or require reference to object outside SQL.

[ALSO READ]
Data Manipulation Language (DML) Triggers
Data Definition Language (DDL) Triggers
INSERTED and DELETED Logical Tables
LOGON Triggers

INSERTED and DELETED Logical Tables in Sql Server

Data Manipulation Language (DML) Triggers have access to the two special logical Tables named INSERTED and DELETED. These are the temporary tables managed by Sql Server. Structure of these tables will be same as that of the table on which the DML action is fired and holds the old and new values of the rows which are modified by the DML statement.

DELETED logical table will hold the rows which are deleted from the trigger table (i.e. the table on which the trigger is defined) by the DELETE or UPDATE operation. An UPDATE DML operation is internally executed as first the deletion of the existing record and then insertion of the new record.

INSERTED logical table will hold the rows which are inserted by the INSERT and UPDATE statement.

Below table depicts the contents of the INSERTED and DELETED logical tables for three DML operations

INSERTED and DELETED Logical Tables

To understand INSERTED and DELETED logical tables with extensive list of examples let us create a Customer Table as shown in the following image by the following script:

Customer Table
SCRIPT:

--Create Demo Database
CREATE DATABASE SqlHintsLogicalTablesDemo
GO
USE SqlHintsLogicalTablesDemo
GO
--Create Customer Table
CREATE TABLE Customer 
( CustomerId INT IDENTITY (1, 1) NOT NULL ,
  FirstName NVARCHAR(50), LastName NVARCHAR(50))
GO
--Add sample records
INSERT INTO dbo.Customer ( FirstName, LastName )
VALUES('Basavaraj','Biradar'),
      ('Kalpana','Patil')

Let us create a AFTER trigger INSERTEDAndDELETEDTableExample on the Customer table for the triggering DML actions INSERT, UPDATE and DELETE by executing the following statement. This AFTER trigger is displaying the content of the INSERTED and DELETED logical tables

CREATE TRIGGER INSERTEDAndDELETEDTableExample
ON Customer
FOR INSERT, UPDATE, DELETE
AS
BEGIN
     PRINT '**********DELETED Table***************'
     SELECT * FROM DELETED
     PRINT '*********INSERTED Table***************'
     SELECT * FROM INSERTED
END

INSERTED and DELETED Logical Tables behavior in-case of INSERT DML OPERATION

Execute the following INSERT statement to insert the record into the Customer table.

INSERT INTO dbo.Customer ( FirstName, LastName )
VALUES('Shreeganesh','Biradar')

RESULT:
INSERTED DELETED TABLE ENTRIES IN CASE OF INSERT

From the result we can see that for the INSERT DML operation on the Customer table, no record is added to the DELETED logical table but the inserted record is available in the INSERTED logical table

Let us check the records in the Customer table post the execution of the above INSERT statement

AFTER INSERT Trigger Example

INSERTED and DELETED Logical Tables behavior in-case of DELETE DML OPERATION

Execute the following DELETE statement to DELETE a record from the Customer table.

DELETE FROM Customer WHERE CustomerId = 2

RESULT:
AFTER DELETE Trigger Example

From the result we can see that the record which is deleted from the Customer table from DELETE DML operation is available in the DELETED logical table and the INSERTED logical table is empty

Let us check the records in the Customer table post the execution of the above DELETE statement

Post DELETE

INSERTED and DELETED Logical Tables behavior in-case of UPDATE DML OPERATION

Execute the following UPDATE statement to update all the records from the Customer table.

UPDATE Customer
SET FirstName = 'Mr. ' + FirstName

RESULT:
AFTER UPDATE Trigger Example

From the above result we can see that INSERTED table will have the records with values post update and the DELETED table will have the the records with a state prior to the update

Let us check the records in the Customer table post the execution of the above UPDATE statement

Post Update

Drop the trigger INSERTEDAndDELETEDTableExample by executing the following statement

DROP TRIGGER INSERTEDAndDELETEDTableExample