Saturday, 11 June 2016

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


No comments:

Post a Comment