SQL Server Trigger觸發程序(一)

嚴格來說Trigger是Stroed Procedure的一種
不過是在特殊情況才會執行的程序

DML的Trigger大部份是發生在 AFTER
觸發程序可以多個重疊，但若重疊過多
會造成效能大量的耗損

TRIGGER是跟TABLE，針對TABLE
不像STORED PROCEDURE 及 FUNCTION是對全資料庫
不過…命名是同樣的不得重複
例如： A表有個叫 "哭哭" TRIGGER
同資料庫中，其他的表都不得再命名"哭哭" TRIGGER

--所有異動事件都會觸發------------------------------
CREATE TRIGGER 巨巨觸發 ON 巨巨
AFTER INSERT,UPDATE,DELETE
AS 
 PRINT '巨巨資料表有資料異動囉！'
GO
-------------------------------------------------
-------------------------------------------------

INSERT INTO 巨巨 VALUES(29,'茶葉蛋',8)
UPDATE 巨巨 SET 價錢=18 WHERE 產品編號=29
DELETE FROM 巨巨 WHERE 產品編號=29
GO
--針對新增-----------------------------------------
CREATE TRIGGER 巨巨新增觸發 ON 巨巨
AFTER INSERT
AS 
 PRINT '巨巨資料表有資料新增！'
GO
--針對更新-----------------------------------------
CREATE TRIGGER 巨巨更新觸發 ON 巨巨
AFTER UPDATE
AS 
 PRINT '巨巨資料表有資料更新！'
GO
--針對刪除-----------------------------------------
CREATE TRIGGER 巨巨刪除觸發 ON 巨巨
AFTER DELETE
AS 
 PRINT '巨巨資料表有資料刪除！'
GO
-------------------------------------------------
-------------------------------------------------

有關查詢觸發的函數：

EXEC sp_helptrigger 巨巨 
--查巨巨表有那些觸發程序

SELECT * FROM sys.triggers
--查所有觸發程序

SELECT OBJECT_NAME(parent_id) AS 所屬物件,* FROM sys.triggers
--查所有觸發程序及對應的表
GO

觸發可以像是CHECK，實現檢查功能
CHECK是在輸入到表中 "前" 執行檢查
而TRIGGER是在輸入表 "後" 做檢查
有時資料輸入有錯就可以CHECK掉
但有時CHECK做不到的檢查，需要TRIGGER執行
例如：
輸入A表的欄位的值，會累加
但上限在B表內
這時CHECK做不到，要靠TRIGGER

--當巨巨有售價異動，每次異動不得超過百分之20--------
CREATE TRIGGER 巨巨產品異動觸發 ON 巨巨
AFTER UPDATE
AS 
 BEGIN
  DECLARE @oldPrice MONEY
  DECLARE @newPrice MONEY
  SELECT @newPrice=價錢 FROM inserted
  SELECT @oldPrice=價錢 FROM deleted
  IF(@newPrice > @oldPrice*1.2 OR  @newPrice < @oldPrice*1.2)
   BEGIN
   PRINT '本公司產品異動不得大於或小於原價的20%'
   ROLLBACK TRANSACTION
   --返回上一TRANSACTION，退回上一動啦
   END
 END
GO 

再來追蹤表，這個可以完成在巨巨表內
有任何的產品異動，INSERT、UPDATE、DELETE
都可以做到紀錄

據老師課堂的說法是：
官方沒有做出查LOG檔的套件
只能用第三方軟體查詢LOG

LOG檔會把對資料庫的所有動作都紀錄
有時使用者只想查特定在用的TABLE
但LOG檔卻列出所有對SQL Server的操作紀錄
查詢起來也挺麻煩的，不如用TRIGGER來實現

缺點就是會佔用資料庫的空間，而且異動都會INSERT
耗掉些效能

CREATE TABLE 巨巨追蹤
(
    產品編號 INT,
 品名 NCHAR(5),
 價錢 MONEY,
 異動狀態 NCHAR(8),
 帳號 VARCHAR(200) DEFAULT SUSER_SNAME(),
 異動時間 DATETIME DEFAULT GETDATE()
)
GO

CREATE TRIGGER 追蹤巨巨新增觸發 ON 巨巨 
AFTER INSERT
AS
  INSERT INTO 巨巨追蹤(產品編號,品名,價錢,異動狀態)
    SELECT 產品編號,品名,價錢,'新增產品' FROM inserted
GO
CREATE TRIGGER 追蹤巨巨刪除觸發 ON 巨巨 
AFTER DELETE
AS
  INSERT INTO 巨巨追蹤(產品編號,品名,價錢,異動狀態)
    SELECT 產品編號,品名,價錢,'刪除產品' FROM deleted
GO
CREATE TRIGGER 追蹤巨巨更新觸發 ON 巨巨 
AFTER UPDATE
AS
  IF UPDATE(品名)
    BEGIN
   INSERT INTO 巨巨追蹤(產品編號,品名,異動狀態)
  SELECT 產品編號,品名,'產品品名修改前' FROM deleted
   INSERT INTO 巨巨追蹤(產品編號,品名,異動狀態)
  SELECT 產品編號,品名,'產品品名修改後' FROM inserted
 END
  IF UPDATE(價錢)
    BEGIN
   INSERT INTO 巨巨追蹤(產品編號,價錢,異動狀態)
        SELECT 產品編號,價錢,'產品價錢修改前' FROM deleted
   INSERT INTO 巨巨追蹤(產品編號,價錢,異動狀態)
        SELECT 產品編號,價錢,'產品價錢修改後' FROM inserted
 END
GO


SELECT * FROM 巨巨

INSERT INTO 巨巨 VALUES(30,'茶葉蛋',8)
UPDATE 巨巨 SET 價錢=18 WHERE 產品編號=30
UPDATE 巨巨 SET 品名='金金沙',價錢=280 WHERE 產品編號=27
DELETE FROM 巨巨 WHERE 產品編號=30

SELECT * FROM 巨巨追蹤