該文章為原創,日後可能會根據實際開發經驗和網友評論,進行相應地方修改,為獲得最新博客動態,望在轉發博客的時候注明出處。
觸發器要實現的功能:
(1)獲取對表Table1數據操作操作類型(insert、delete或update)。
(2)將表修改後的數據保存到表Table2(該表結構與Table1表結構類似)。
例如:
1>向表Table1添加數據1,表保存後,將數據1添加到表Table2,並將操作類型:insert,保存到表Table2的ChangeType列。
2>修改表Table1,將數據1改成數據2,表保存後,將數據2添加到表Table2,並將操作類型:update,保存到表Table2的ChangeType列。
3>刪除表Table1數據1,表保存後,將數據2添加到表Table2,並將操作類型:delete,保存到表Table2的ChangeType列。
表結構:
(1)表Table1
1 CREATE TABLE Table1( 2 [ID] [BIGINT] IDENTITY(1,1) NOT NULL, 3 [Name] [NVARCHAR](20) NULL, 4 [Sex] [NVARCHAR](2) NULL, 5 [Address] [NVARCHAR](50) NULL, 6 [Age] [INT] NULL, 7 [Birthday] [DATE] NULL 8 ) ON [PRIMARY]
(2)表Table2
1 CREATE TABLE Table2( 2 [ID] [BIGINT] IDENTITY(1,1) NOT NULL, 3 [Name] [NVARCHAR](20) NULL, 4 [Sex] [NVARCHAR](2) NULL, 5 [Address] [NVARCHAR](50) NULL, 6 [Age] [INT] NULL, 7 [Birthday] [DATE] NULL, 8 [ChangeType] [NVARCHAR](50) NOT NULL 9 ) ON [PRIMARY]
觸發器Tri_Table1
1 CREATE TRIGGER [Tri_Table1] ON [Table1] 2 --After觸發器,對表進行insert、delete、update後觸發 3 AFTER INSERT, DELETE, UPDATE 4 AS 5 BEGIN 6 BEGIN TRY 7 BEGIN TRAN; 8 DECLARE @maxID NVARCHAR(50) , 9 @inserted INT , 10 @deleted INT , 11 @ChangeType NVARCHAR(20); 12 SELECT @inserted = COUNT(1) 13 FROM Inserted; 14 SELECT @deleted = COUNT(1) 15 FROM Deleted; 16 --判斷對表Table1的操作類型 17 IF @inserted > 0 18 AND @deleted = 0 19 BEGIN 20 SET @ChangeType = 'INSERT'; 21 END; 22 ELSE 23 IF @inserted > 0 24 AND @deleted > 0 25 BEGIN 26 SET @ChangeType = 'UPDATE'; 27 END; 28 ELSE 29 IF @inserted = 0 30 AND @deleted > 0 31 BEGIN 32 SET @ChangeType = 'DELETE'; 33 END; 34 IF @ChangeType = 'DELETE' 35 BEGIN 36 SELECT @maxID = Id 37 FROM Deleted; 38 --如果對Table1同一條數據,進行多次操作,則Table2只保存最新數據 39 IF EXISTS ( SELECT COUNT(1) 40 FROM Table2 41 WHERE Id = @maxID ) 42 BEGIN 43 DELETE FROM Table2 44 WHERE Id = @maxID; 45 END; 46 INSERT INTO Table2 47 ( 48 ) 49 SELECT ID, 50 Name, 51 Sex, 52 Address, 53 Age, 54 Birthday, 55 @ChangeType AS ChangeType 56 FROM Deleted; 57 END; 58 ELSE 59 BEGIN 60 SELECT @maxID = Id 61 FROM Inserted; 62 IF EXISTS ( SELECT COUNT(1) 63 FROM Table2 64 WHERE Id = @maxID ) 65 BEGIN 66 DELETE FROM Table2 67 WHERE Id = @maxID; 68 END; 69 INSERT INTO Table2 70 ( 71 ID, 72 Name, 73 Sex, 74 Address, 75 Age, 76 Birthday, 77 ChangeType 78 ) 79 SELECT ID, 80 Name, 81 Sex, 82 Address, 83 Age, 84 Birthday, 85 @ChangeType AS ChangeType 86 FROM Inserted 87 END; 88 COMMIT TRAN; 89 END TRY 90 BEGIN CATCH 91 IF XACT_STATE() = -1 92 ROLLBACK TRAN; 93 END CATCH; 94 END; 95