修改DML觸發(fā)器
在Management Studio中修改觸發(fā)器之前，必須要先查看觸發(fā)器的內容，通過(guò)
ALTER TRIGGER 觸發(fā)器名
ON 數據表名或視圖名
AFTER INSERT或DELETE或UPDATE
AS
BEGIN
--這里是要運行的SQL語(yǔ)句
END
GO
如果只要修改觸發(fā)器的名稱(chēng)的話(huà)，也可以使用存儲過(guò)程“sp_rename”。其語(yǔ)法如下：
sp_rename ‘舊觸發(fā)器名’,’新觸發(fā)器名’
值得一提的是修改觸發(fā)器名稱(chēng)有可能會(huì )使某些腳本或存儲過(guò)程運行出錯。
刪除DML觸發(fā)器
在Management Studio中刪除觸發(fā)器，必須要先查到觸發(fā)器列表，通過(guò)
Drop Trigger 觸發(fā)器名
注意：如果一個(gè)數據表被刪除，那么SQL Server會(huì )自動(dòng)將與該表相關(guān)的觸發(fā)器刪除。
禁用與啟用DML觸發(fā)器
禁用觸發(fā)器與刪除觸發(fā)器不同，禁用觸發(fā)器時(shí)，仍會(huì )為數據表定義該觸發(fā)器，只是在執行Insert、Update或Delete語(yǔ)句時(shí)，除非重新啟用觸發(fā)器，否則不會(huì )執行觸發(fā)器中的操作。
在Management Studio中禁用或啟用觸發(fā)器，也必須要先查到觸發(fā)器列表，觸發(fā)器列表里，右擊其中一個(gè)觸發(fā)器，在彈出快捷菜單中選擇【禁用】選項，即可禁用該觸發(fā)器。啟用觸發(fā)器與上類(lèi)似，只是在彈出快捷菜單中選擇【啟用】選項即可。
用以下Alter Table語(yǔ)句也禁用或啟用觸發(fā)器，其語(yǔ)法如下：
Alter table 數據表名
Disable或Enable trigger 觸發(fā)器名或ALL
用Disable可以禁用觸發(fā)器，用Enable可以啟用觸發(fā)器；如果要禁用或啟用所有觸發(fā)器，用“ALL”來(lái)代替觸發(fā)器名。
2005新增功能：DDL觸發(fā)器
DDL觸發(fā)器是SQL Server 2005新增的一個(gè)觸發(fā)器類(lèi)型，是一種特殊的觸發(fā)器，它在響應數據定義語(yǔ)言（DDL）語(yǔ)句時(shí)觸發(fā)。一般用于數據庫中執行管理任務(wù)。
與DML觸發(fā)器一樣，DDL觸發(fā)器也是通過(guò)事件來(lái)激活，并執行其中的SQL語(yǔ)句的。但與DML觸發(fā)器不同，DML觸發(fā)器是響應Insert、Update或Delete語(yǔ)句而激活的，DDL觸發(fā)器是響應Create、Alter或Drop開(kāi)頭的語(yǔ)句而激活的。一般來(lái)說(shuō)，在以下幾種情況下可以使用DDL觸發(fā)器：
l 數據庫里的庫架構或數據表架構很重要，不允許被修改。
l 防止數據庫或數據表被誤操作刪除。
l 在修改某個(gè)數據表結構的同時(shí)修改另一個(gè)數據表的相應的結構。
l 要記錄對數據庫結構操作的事件。
2005新增功能：設計DDL觸發(fā)器
只要注意到DDL觸發(fā)器和DML觸發(fā)器的區別，設計DDL觸發(fā)器與設計DML觸發(fā)器也很類(lèi)似，下面詳細講述一下要怎么去設計一個(gè)DDL觸發(fā)器。
建立DDL觸發(fā)器的語(yǔ)句
建立DDL觸發(fā)器的語(yǔ)法代碼如下：
CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ...n ] | EXTERNAL NAME < method specifier > [ ; ] }
用中文取代一下英文可以看得更明白：
CREATE TRIGGER 觸發(fā)器名
ON ALL SERVER或DATABASE
FOR 或AFTER
激活DDL觸發(fā)器的事件
AS
要執行的SQL語(yǔ)句
其中：
l ON后面的All Server是將DDL觸發(fā)器作用到整個(gè)當前的服務(wù)器上。如果指定了這個(gè)參數，在當前服務(wù)器上的任何一個(gè)數據庫都能激活該觸發(fā)器。
l ON后面的Database是將DDL觸發(fā)器作用到當前數據庫，只能在這個(gè)數據庫上激活該觸發(fā)器。
l For或After是同一個(gè)意思，指定的是After觸發(fā)器，DDL觸發(fā)器不能指定的Stead Of觸發(fā)器。
l 激活DDL觸發(fā)器的事件包括兩種，在DDL觸發(fā)器作用在當前數據庫情況下可以使用以下事件：
| CREATE_APPLICATION_ROLE | ALTER_APPLICATION_ROLE | DROP_APPLICATION_ROLE |
| CREATE_ASSEMBLY | ALTER_ASSEMBLY | DROP_ASSEMBLY |
| ALTER_AUTHORIZATION _DATABASE | | |
| CREATE_CERTIFICATE | ALTER_CERTIFICATE | DROP_CERTIFICATE |
| CREATE_CONTRACT | DROP_CONTRACT | |
| GRANT_DATABASE | DENY_DATABASE | REVOKE_DATABASE |
| CREATE_EVENT_NOTIFICATION | DROP_EVENT_NOTIFICATION | |
| CREATE_FUNCTION | ALTER_FUNCTION | DROP_FUNCTION |
| CREATE_INDEX | ALTER_INDEX | DROP_INDEX |
| CREATE_MESSAGE_TYPE | ALTER_MESSAGE_TYPE | DROP_MESSAGE_TYPE |
| CREATE_PARTITION_FUNCTION | ALTER_PARTITION_FUNCTION | DROP_PARTITION_FUNCTION |
| CREATE_PARTITION_SCHEME | ALTER_PARTITION_SCHEME | DROP_PARTITION_SCHEME |
| CREATE_PROCEDURE | ALTER_PROCEDURE | DROP_PROCEDURE |
| CREATE_QUEUE | ALTER_QUEUE | DROP_QUEUE |
| CREATE_REMOTE_SERVICE _BINDING | ALTER_REMOTE_SERVICE _BINDING | DROP_REMOTE_SERVICE _BINDING |
| CREATE_ROLE | ALTER_ROLE | DROP_ROLE |
| CREATE_ROUTE | ALTER_ROUTE | DROP_ROUTE |
| CREATE_SCHEMA | ALTER_SCHEMA | DROP_SCHEMA |
| CREATE_SERVICE | ALTER_SERVICE | DROP_SERVICE |
| CREATE_STATISTICS | DROP_STATISTICS | UPDATE_STATISTICS |
| CREATE_SYNONYM | DROP_SYNONYM | CREATE_TABLE |
| ALTER_TABLE | DROP_TABLE | |
| CREATE_TRIGGER | ALTER_TRIGGER | DROP_TRIGGER |
| CREATE_TYPE | DROP_TYPE | |
| CREATE_USER | ALTER_USER | DROP_USER |
| CREATE_VIEW | ALTER_VIEW | DROP_VIEW |
| CREATE_XML_SCHEMA _COLLECTION | ALTER_XML_SCHEMA _COLLECTION | DROP_XML_SCHEMA _COLLECTION |
在DDL觸發(fā)器作用在當前服務(wù)器情況下，可以使用以下事件：
| ALTER_AUTHORIZATION_SERVER | | |
| CREATE_DATABASE | ALTER_DATABASE | DROP_DATABASE |
| CREATE_ENDPOINT | DROP_ENDPOINT | |
| CREATE_LOGIN | ALTER_LOGIN | DROP_LOGIN |
| GRANT_SERVER | DENY_SERVER | REVOKE_SERVER |
例三，建立一個(gè)DDL觸發(fā)器，用于保護數據庫中的數據表不被修改，不被刪除。具體操作步驟如下：
（1）啟動(dòng)Management Studio，登錄到指定的服務(wù)器上。
（2）在如圖11.1所示界面的【對象資源管理器】下選擇【數據庫】，定位到【Northwind】數據庫上。
（3）單擊【新建查詢(xún)】按鈕，在彈出的【查詢(xún)編輯器】的編輯區里輸入以下代碼：
CREATE TRIGGER 禁止對數據表操作
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT '對不起，您不能對數據表進(jìn)行操作'
ROLLBACK ;
（4）單擊【執行】按鈕，生成觸發(fā)器。
例四，建立一個(gè)DDL觸發(fā)器，用于保護當前SQL Server服務(wù)器里所有數據庫不能被刪除。具體代碼如下：
CREATE TRIGGER 不允許刪除數據庫
ON all server
FOR DROP_DATABASE
AS
PRINT '對不起，您不能刪除數據庫'
ROLLBACK ;
GO
例五，建立一個(gè)DDL觸發(fā)器，用來(lái)記錄數據庫修改狀態(tài)。具體操作步驟如下：
（1）建立一個(gè)用于記錄數據庫修改狀態(tài)的表：
CREATE TABLE 日志記錄表(
編號 int IDENTITY(1,1) NOT NULL,
事件 varchar(5000) NULL,
所用語(yǔ)句 varchar(5000) NULL,
操作者 varchar(50) NULL,
發(fā)生時(shí)間 datetime NULL,
CONSTRAINT PK_日志記錄表 PRIMARY KEY CLUSTERED
(
編號 ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
（2）建立DDL觸發(fā)器：
CREATE TRIGGER 記錄日志
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @log XML
SET @log = EVENTDATA()
INSERT 日志記錄表
(事件, 所用語(yǔ)句,操作者, 發(fā)生時(shí)間)
VALUES
(
@log.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@log.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),
CONVERT(nvarchar(100), CURRENT_USER),
GETDATE()
) ;
GO
其中Eventdata是個(gè)數據庫函數，它的作用是以XML格式返回有關(guān)服務(wù)器或數據庫事件的信息。@log.value是返回log這個(gè)XML結點(diǎn)的值，結點(diǎn)的位置是括號里的第一個(gè)參數。
測試觸發(fā)器功能
現在測試一下在上一章節中建立好的三個(gè)觸發(fā)器的功能。下面所有的測試都是在【查詢(xún)編輯器】對話(huà)框里進(jìn)行的，要打開(kāi)【查詢(xún)編輯器】對話(huà)框，只要單擊Management Studio里【新建查詢(xún)】按鈕即可。
測試例三：例三是保證【Northwind】數據庫里不能刪除表和修改表，在【查詢(xún)編輯器】對話(huà)框里輸入一個(gè)刪除表的SQL語(yǔ)句：
Drop table 操作記錄表
運行結果如圖11.17所示：
圖11.17 不允許刪除表格
測試例四：例四是保證當前服務(wù)器里的所有數據庫不能被刪除，在【查詢(xún)編輯器】對話(huà)框里輸入一個(gè)刪除數據庫的SQL語(yǔ)句：
Drop DataBase test
運行結果如圖11.18所示：
圖11.18 不允許刪除數據庫
測試例五：例五是記錄對【Northwind】所進(jìn)行的操作，在【查詢(xún)編輯器】對話(huà)框里輸入一條添加數據表和一條刪除數據表的SQL語(yǔ)句，然后再用Select語(yǔ)句查看【目志記錄表】數據表里所有的記錄：
CREATE TABLE 測試表(
編號int IDENTITY(1,1) NOT NULL,
測試內容varchar(50) NOT NULL)
GO
Drop table 測試表
GO
select * from 日志記錄表
GO
運行時(shí)不要忘了，前面曾經(jīng)建立過(guò)一個(gè)不能刪除數據表的觸發(fā)器，要先把它禁用或刪除。運行結果如圖11.19所示：
圖11.19 記錄對數據庫的操作
聯(lián)系客服
