详解SQL Server中DDL触发器和索引视图

详解SQL Server中DDL触发器和索引视图

索引视图：

一般视图都虚表，即视图本身不存储数据，而且是一个查询，当访问视图时，SQL SERVER会自动根据视图的定义来访问基表数据。具有唯一的聚集索引的视图，

索引视图本身会存储数据，可以加快查询速度，但会增加数据修改的开销。所以索引视图适用的修改少而查询多的表。创建索引视图时，索引视图的第一个索引

必须是CLUSTERED和UNIQUE。

索引视图的创建：

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) 

SQL Server 中的DDL触发器

DDL触发器可以在整数据库范围内对对象的定义、修改、删除而触发执行的触发器。可以数据库级别对数据库对象进行控制和审记。或者服务器级别的触发器，如用户登录的审记。

DDL触发器事件定义：

<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> 

DDL触发器的创建：

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