Detailed description of DDL triggers and index views in SQL Server

Index View:

Generally, all views are virtual tables, that is, the view itself does not store data and is a query. when accessing a view, SQL SERVER automatically accesses the base table data according to the view definition. Views with unique clustered Indexes,

The index view itself stores data, which can accelerate the query speed, but increases the overhead of data modification. Therefore, the index view applies to tables with fewer modifications and more queries. When creating an index view, the first index of the index View

It must be CLUSTERED and UNIQUE.

Create an index View:

 
 

  
  
CREATE TABLE dbo.t1  
  
  
(  
  
  
USERID VARCHAR(50),   
  
  
USERNAME VARCHAR(256)  
  
  
);  
  
  
go  
  
  
CREATE TABLE dbo.t2  
  
  
(  
  
  
USERID VARCHAR(50),   
  
  
DepartID VARCHAR(50)  
  
  
);  
  
  
GO  
  
  
CREATE TABLE dbo.t3  
  
  
(  
  
  
DepartID VARCHAR(50),   
  
  
DepartName VARCHAR(256)  
  
  
);  
  
  
GO  
  
  
CREATE VIEW dbo.USERINFO  
  
  
WITH SCHEMABINDING  
  
  
AS 
  
  
SELECT a.USERID, a.USERNAME, c.DEPARTID, c.DEPARTNAME   
  
  
FROM dbo.t1 a, dbo.t2 b, dbo.t3 c   
  
  
WHERE a.USERID = b.USERID   
  
  
AND b.DEPARTID = C.DEPARTID  
  
  
GO  
  
  
CREATE UNIQUE CLUSTERED INDEX IX_USERINFO_USERIDDEPARTID ON dbo.USERINFO(USERID, DEPARTID) 
 
 

DDL triggers in SQL Server

DDL triggers can be executed when objects are defined, modified, or deleted within the whole database. You can control and review database objects at the database level. Or server-level triggers, such as user logon review.

DDL trigger event definition:

 
 

  
  
<EVENT_INSTANCE> 
  
  
    <EventType>type</EventType> 
  
  
    <PostTime>date-time</PostTime> 
  
  
    <SPID>spid</SPID> 
  
  
    <ServerName>name</ServerName> 
  
  
    <LoginName>name</LoginName> 
  
  
    <UserName>name</UserName> 
  
  
    <DatabaseName>name</DatabaseName> 
  
  
    <SchemaName>name</SchemaName> 
  
  
    <ObjectName>name</ObjectName> 
  
  
    <ObjectType>type</ObjectType> 
  
  
    <TSQLCommand>command</TSQLCommand> 
  
  
</EVENT_INSTANCE> 
 
 

Create a DDL trigger:

 
 

  
  
CREATE TABLE dbo.t4  
  
  
(  
  
  
USERNAME VARCHAR(256),   
  
  
TSQL VARCHAR(MAX),  
  
  
CDATE DATETIME  
  
  
);  
  
  
GO  
  
  
CREATE TRIGGER tr_dbDDL  
  
  
ON DATABASE   
  
  
FOR   
  
  
DROP_TABLE, ALTER_TABLE, CREATE_TABLE,   
  
  
CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE  
  
  
AS 
  
  
DECLARE @xdata XML;  
  
  
SELECT @xdata  = EVENTDATA();  
  
  
ROLLBACK;  
  
  
INSERT INTO dbo.t4(USERNAME, TSQL, CDATE)  
  
  
SELECT @xdata.value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(max)') AS dbUserName,   
  
  
@xdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)') AS T_SQL,   
  
  
GETDATE() AS CDATE;  
  
  
 
  
  
GO  
  
  
use master  
  
  
go  
  
  
 
  
  
CREATE TABLE dbo.t5  
  
  
(  
  
  
USERNAME VARCHAR(256),   
  
  
TSQL VARCHAR(MAX),  
  
  
CDATE DATETIME  
  
  
);  
  
  
GO  
  
  
ALTER TRIGGER tr_svrddl  
  
  
ON ALL SERVER  
  
  
FOR   
  
  
CREATE_DATABASE, ALTER_DATABASE, DROP_DATABASE,   
  
  
DDL_LOGIN_EVENTS   
  
  
AS 
  
  
DECLARE @xdata XML;  
  
  
SELECT @xdata  = EVENTDATA();  
  
  
 
  
  
 
  
  
INSERT INTO dbo.t4(USERNAME, TSQL, CDATE)  
  
  
SELECT @xdata.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(max)') AS dbUserName,   
  
  
@xdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)') AS T_SQL,   
  
  
GETDATE() AS CDATE;  
  
  
GO