SQL Server Interview Questions and Answers Part 9

1.) What is Trigger ?                                                                                                                        
A trigger is an event that is executed when any condition (statement) is satisfied. 

2.) How to prevent' table creation' in database using concept of trigger  ?             
Create trigger ddl trigger on database 
for Create table
as 
begin
rollback
Print 'table can not be created'
end

3.) How to view all triggers in your database ?                                                                    
Select*from sys.triggers

4.) What are the types of triggers in sql server ?                                                                 

1. DML (Data Manipulation Language) Triggers
2. DDL(Data Definition Language ) Triggers
3. Logon Triggers
4. CLR Triggers

5.) What operations can be performed in sql server ?                                                       

• Insert statement
• Update Statement
• Delete statement
• Stored Procedures 

6.) What are the types of DML Triggers used in sql server ?                                          

1. After Triggers (For/After clause)
2. Instead of Triggers(Instead of Clause)

7.) What is After triggers in sql server ?                                                                                  
  This Triggers basically runs after an insert,delete and update operations on a table. We can perform After Triggers on a table not a View table. We can use  three types of After Triggers in sql server which are given below:-

• After Insert Trigger
• After Delete Trigger
• After Update Trigger 

8.) What is instead of triggers in sql server ?                                                                       
Instead of Triggers are used as an Interceptor.Suppose we define an Instead of Triggers on table or Views for the delete operation. If any conditions\rules written in statements are not satisfied or any error occurs in sql database then this trigger jump to another statements by escaping some statements and displayed some message to the client.
we can further classified this trigger in three types as given below:-

• Instead of Insert Trigger
• Instead of Delete Trigger
• Instead of Update Trigger

9.) What is DDL triggers in sql server ?                                                                                   
 DDL Trigger can be used for Observe ,control action and audit operations on the sql server. DDL Trigger are basically used for administration operations such as creating auditing and controlling the database operations. We can create DDL Triggers on the Tables by following statements as given below:- 

• Create Statement
• Alter Statement
• Drop Statement
• Stored procedures can perform on DDL Trigger

10.) What is Logon triggers in sql server ?                                                                             
Logon Triggers are DDL Triggers that is created at server label. Logon triggers are a spacial type of triggers which are fired when a logon event of sql server occurs.

11.) What are the syntax of Logon triggers in sql server ?                                              

create trigger trigger_name

on {table/view}

[with encryption/Execute as]

[{for/after/instead of}/{insert/delete/update/create/alter/drop}]

[Not for replication]

as

SQL statement(1,2,3......n)

12.) What is CLR triggers in sql server ?

CLR Trigger are special type of triggers that are based on the CLR (Common Language Run time ) in .NET Framework.we can create DDL and DML Triggers using CLR Language (Visual basic ,C#,F#). CLR Trigger Concepts introduced in SQL Server 2008. 

13.) How to implement  After triggers in sql server database ?                                    
More Details...