Inserted(新資料)與 Deleted(舊資料)兩張虛擬表,結構與基礎表相同,存在記憶體中。-- 建立
CREATE TRIGGER trigger_name
ON {table_name | view_name}
{FOR | AFTER | INSTEAD OF}
[INSERT, UPDATE, DELETE]
AS
sql_statement;
-- 修改
ALTER TRIGGER trigger_name
ON {table_name | view_name}
{FOR | AFTER | INSTEAD OF}
[INSERT, UPDATE, DELETE]
AS
sql_statement;
-- 刪除
DROP TRIGGER trigger_name;
-- 查看所有觸發器
SELECT * FROM sysobjects WHERE xtype = 'TR';
-- 查看單一觸發器定義
EXEC sp_helptext 'trigger_name';
CREATE TRIGGER orderinsert ON orders
AFTER INSERT
AS
IF (SELECT status FROM goods g INNER JOIN inserted i ON g.name = i.goodsname) = 1
BEGIN
PRINT '貨品正在整理中';
PRINT '訂單無法提交';
ROLLBACK TRANSACTION;
END;
CREATE TRIGGER orderinsert1 ON orders
AFTER INSERT
AS
UPDATE g SET storage = storage - i.quantity
FROM goods g INNER JOIN inserted i ON g.name = i.goodsname;
CREATE TRIGGER goodsdelete ON goods
AFTER DELETE
AS
DELETE FROM orders WHERE goodsname IN (SELECT name FROM deleted);
CREATE TRIGGER orderdateupdate ON orders
AFTER UPDATE
AS
IF UPDATE(orderdate)
BEGIN
RAISERROR('orderdate cannot be modified', 10, 1);
ROLLBACK TRANSACTION;
END;
CREATE TRIGGER orderinsert3 ON orders
AFTER INSERT
AS
IF (SELECT COUNT(*) FROM goods g INNER JOIN inserted i ON g.name = i.goodsname) = 0
BEGIN
PRINT '貨品主檔無此商品';
ROLLBACK TRANSACTION;
END;
ALTER TRIGGER addOrder ON Orders
FOR INSERT
AS
INSERT INTO OrderLog (Id, goodName, Number)
SELECT Id, goodName, Number FROM inserted;
使用 sys.dm_tran_locks 與 sys.dm_os_waiting_tasks 監控鎖定與阻塞;善用 PRINT 或 RAISERROR 記錄觸發器執行流程。