SQL SERVER – Difference between DML Triggers and DDL Triggers

As we discussed earlier in the previous  article,  DML Triggers will be fired automatically whenever Data Manipulation statements like INSERT,UPDATE and DELETE are executed where as DDL triggers will fire with the Data definition statements like CREATE, ALTER and DROP.       

Now let us see the differences between these two types of triggers. 

DML Triggers	DDL Triggers
1. Operate on INSERT, UPDATE and DELETE	1. Operate on CREATE, DROP and ALTER
2. Applied on Tables and views	2. Applied on Databases and servers
3. Can be used as INSTEAD OF TRIGGERS	3. Cannot be used as INSTEAD OF TRIGGERS
4. Creates INSERTED and DELETED tables	4. Cannot Create INSERTED and DELETED tables
5. DML triggers run either Before or After a T-SQL statement is completed	5. DDL triggers run only after a T-SQL statement is completed

SQL SERVER – DDL Triggers

We are all well known about the DML Triggers which will be fired automatically whenever Data Manipulation statements like INSERT,UPDATE and DELETE are executed.

DDL triggers are same as that of the DML triggers, but they will fire with the Data definition statements like CREATE, ALTER and DROP instead of INSERT, UPDATE and DELETE
statements.

In general DDL triggers can be used to prevent the changes to the tables like 'DROP TABLE' and 'ALTER TABLE'  and
to audit the changes in the database schema. These triggers will fire only after the transact SQL statements are completed.  

In case of DML triggers, they will be applied on either tables or Views. where as DDL triggers will be applied on either Databases or servers.

Database-scoped DDL triggers are stored in the database in which they are created where as the Server-scoped DDL triggers are stored in the Master database.

We can get the Database-scoped and Server-scoped triggers using sys.triggers and sys.server_triggers respectively.

Now Let us have a look at the syntax for creating a DDL trigger. 

CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH ENCRYPTION ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS
{ sql_statement }

Here is an example for creating a DDL trigger while altering the Emp table 

Create Trigger Trig_AviodChangeEmp
on Database
For alter_table
as
Begin
   Print 'You cannot drop the column in the Table Emp'
   Rollback
End 

SQL SERVER – Difference Between SCOPE_IDENTITY(), @@IDENTITY and CURRENT_IDENT()

Scope_identity() will return the identity value generated with current scope in either a trigger or a stored procedure.

@@Identity will return the identity value generated with current session in any table

Ident_Current() will return the identity value generated with a particular table specified

Let us see the difference by using an example :

For this, let us first create two  tables TableA and TableB

Create Table TableA(ID INT IDENTITY(1,1) PRIMARY KEY , NAME VARCHAR(25) NOT NULL)

Create Table TableB(ID INT IDENTITY(100,5) PRIMARY KEY , NAME VARCHAR(25) NOT NULL)

TableA is having an Identity column ID with starting value 1 and increment value 1

TableB is having an Identity column ID with starting value 100 and increment value 5

Now Inserting Records into these tables,

Insert into TableA values('AAA')
Insert into TableA values('BBB')
Insert into TableA values('CCC')

Insert into TableB values('AAA')
Insert into TableB values('BBB')
Insert into TableB values('CCC')

On Retrieving the values from TableA and TableB,

Select * From TableA

Select * From TableB

Now let us create a Trigger on TableA, Which inserts a new record into the Table TableB.

Create Trigger Trigger_A on TableA for Insert as
Declare @Name varchar(25)
Begin
     Select @Name =  Name from Inserted
     Insert into TableB Values(@Name)
End

Now let us insert a new record in TableA,

Insert into TableA values('DDD')

Now let us have a look at our tables, TableA and TableB

Select * From TableA

Select * From TableB

Now On checking the values of Scope_identity() , @@Identity

Select  Scope_identity() ScopeIdentity, @@Identity IdentityValue

Here Scope_identity()  is the identity value that is generated with in the current scope -  the trigger,  which is 4.

@@ Identity is the last identity value that is generated with in the current session, i.e. in this case 115.

Also, Ident_Current() is pertaining to a specific table.

Select Ident_Current('TableA') Last_identity

 
Select Ident_Current('TableB') Last_identity

SQL SERVER – MAGIC Tables – INSERTED and DELETED

Two Special temporary tables will play a vital role while creating a trigger on a table. Those two tables are called MAGIC Tables. they are – INSERTED and  DELETED.

These two tables are used to test the effects of certain data modifications and to set the conditions for trigger actions. these two tables contains the same data structure as that of the original table on  which the trigger will be created.

While inserting a new record, a new row will be inserted in the INSERTED table.

While updating a record, the old details will placed in the DELETED table as a new record and the changes will be updated in the Original table. At the same time a new record will be placed in the INSERTED table.

While deleting a record from the original table, a new record will be placed in the DELETED table and the corresponding record will be deleted from the original trigger table.

SQL SERVER – How To Find Out which Version of Microsoft SQL Server is running

Some times we have to find out the Version of SQL Server that is running.

There are many ways to find out the version of the SQL Server.

In Order to find out  the version of Microsoft SQL Server, First Of all connect to SQL Server by using SQL Server Management Studio, and then run the following Transact-SQL statement.

Select @@version  'Version'

This Results,

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)   Oct 14 2005 00:33:37   Copyright (c) 1988-2005 Microsoft Corporation  Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

OutPut :

The Other Alternative is, using the system stored Procedure Sp_msgetversion

 Exec master..sp_msgetversion

OutPut :

As a third alternative, we can use the Extended stored procedure xp_msver. This Command gives a detailed information like ProductName, ProductVersion, Language etc.,

OutPut :