SQL Trigger 範例

--新增Trigger
CREATE TRIGGER trTable1_ADD
ON Table1
AFTER INSERT
AS
IF (Select Count(*) From INSERTED) > 0 and (Select Count(*) From DELETED) = 0
BEGIN

 SET NOCOUNT ON;

INSERT INTO Table1_ADDLOG ([ID])
SELECT ID FROM INSERTED
END

--更新Trigger
CREATE TRIGGER trTable1_UPD
ON Table1
AFTER UPDATE
AS
IF (Select Count(*) From INSERTED) > 0 and (Select Count(*) From DELETED) > 0
BEGIN
 IF UPDATE(WEB_PD)
 BEGIN
 SET NOCOUNT ON;
INSERT INTO Table1_UPDLOG ([ID], [FLD1_BF], [FLD1_AF])
SELECT I.ID, D.FLD1, I.FLD1
FROM INSERTED I
INNER JOIN DELETED D ON I.ID=D.ID
 END
END

--刪除Trigger
CREATE TRIGGER trTable1_DEL
ON Table1
AFTER DELETE
AS
IF (Select Count(*) From INSERTED) = 0 and (Select Count(*) From DELETED) > 0
BEGIN
 SET NOCOUNT ON;
INSERT INTO Table1_DELLOG ([ID])
SELECT ID FROM DELETED
END

--暫停 Trigger
DISABLE TRIGGER trTable1_UPD ON Table1;