Question and Answers in Sql Server - Part 7

In This post Let we see some important question and answers in Sql Server, which is a continuation of previous post.


28. What is the difference between the Clustered and Non-Clustered index ?
       Clustered index is physically re-orders the rows, but the Non-Clustered refers the separate index table which have the pointer of the records. Table can have only one clustered index , but a table can have many non-clustered index.


29. How to create a trigger on update a value in a table ?
      Click here to read about update trigger


30. How to create a trigger on database and drop a trigger ?
      Now Let we see how to create a trigger on database , in this example we are going to create a trigger on database level to track the changes takesplace in db. so we are enabling the trigger for create , drop and drop a stored procedure in database. Make a entry in table. Information are getting from a EVENTDATA() function which returns a xml file and also fetching the information of client who is executing this statement .

CREATE TABLE EVENTSTABLE

(

      EventDate  DATETIME,

      eventtype  NVARCHAR(100),

      EventDDL   NVARCHAR(MAX),

      EVENTDATA  XML,

      dbname     VARCHAR(40),

      schemaname VARCHAR(20),

      OBJECTname VARCHAR(100),

      hostname   NVARCHAR(50),

      ipaddress NVARCHAR(60),

      program   NVARCHAR(300),

      loginuser NVARCHAR(100)

)

-- DDL trigger

CREATE TRIGGER trig_db

ON DATABASE

FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE

AS

BEGIN

SET NOCOUNT ON

DECLARE @xml XML = EVENTDATA()

DECLARE @ipaddress VARCHAR(32)

/* Fetching the ipaddress of the client who is executing the code */

SELECT @ipaddress = client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID

INSERT INTO EVENTSTABLE

(

      EventDate ,

      eventtype ,

      EventDDL  ,

      EVENTDATA ,

      dbname        ,   

      schemaname,

      OBJECTname,

      hostname  ,

      ipaddress ,

      program   ,

      loginuser

)

SELECT

      CURRENT_TIMESTAMP

      ,@xml.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)')

      ,@xml.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(100)')

      ,@xml

      ,DB_NAME()

      ,@xml.value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(100)')

      ,@xml.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)')

      ,HOST_NAME()

      ,@ipaddress

      ,PROGRAM_NAME()

      ,SUSER_SNAME()

SET NOCOUNT OFF

END

SELECT * FROM EVENTSTABLE

-- Create a Stored procedure in db , information is automatically track in table.

CREATE PROCEDURE sp_proc

AS

BEGIN

SELECT '1'

END

SELECT * FROM EVENTSTABLE

For drop a trigger.

DROP TRIGGER trig_db