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