利用 instead of Trigger 禁止一筆資料以上的更新或刪除，下面為 Script
USE [AdventureWorks2012]
GO
IF OBJECT_ID('Avoid') IS NOT NULL
DROP TABLE Avoid
IF OBJECT_ID('triAvoidUpdate') IS NOT NULL
DROP TRIGGER triAvoidUpdate
GO
IF OBJECT_ID('triAvoidDelete') IS NOT NULL
DROP TRIGGER triAvoidDelete
GO
CREATE TABLE Avoid (ID int identity(1,1) , Name nchar(10) , PWD char(10))
INSERT INTO Avoid (Name , PWD) VALUES
(N'張三','123') ,
(N'李四','456') ,
(N'王五','789')
GO
CREATE TRIGGER dbo.triAvoudUpdate
ON dbo.Avoid
INSTEAD OF UPDATE -- 針對 UPDATE
AS
BEGIN
--假如更新筆數一次超過一筆，就不允許
IF (SELECT COUNT(1) FROM deleted) > 1
ROLLBACK
ELSE
BEGIN
UPDATE A
SET A.Name = I.Name ,
A.PWD = I.PWD
FROM Avoid AS A
JOIN inserted AS I ON A.ID = I.ID
END
END
GO
CREATE TRIGGER dbo.triAvoudDelete
ON dbo.Avoid
INSTEAD OF DELETE -- 針對 Delete
AS
BEGIN
--假如刪除筆數一次超過一筆，就不允許
IF (SELECT COUNT(1) FROM deleted) > 1
ROLLBACK
ELSE
BEGIN
DELETE A
FROM Avoid AS A
JOIN deleted AS D ON A.ID = D.ID
END
END
GO
-- 故意省略 WHERE 來引發錯誤，避免大量更新或大量刪除
UPDATE Avoid SET PWD = ''
![[SQL] Trigger - 避免大量更新或大量刪除](https://farm6.staticflickr.com/5507/11527112074_1bf1499144_z.jpg)
使用 instead of Trigger 而非 After Trigger 的優點在於，T-SQL 對 Table 進行 DML 前就擋下來，After Trigger 是 DML 寫進 Table 後才 ROLLBACK，但 instead of Trigger 會覆蓋原本 T-SQL 語法，所以 Trigger 中還要再寫一次
而實務上真的發生，也不要直接在線上 DB 進行還原，可以把備份檔案還原至
- 相同 instance 不同 DB，直接利用 UPDATE 把資料更新回去
- 不同 instance，建立 Linked Server 後，再下 UPDATE 更新資料
沒有留言:
張貼留言