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