你的位置:首页 > 数据库

[数据库]SqlServer触发器判断对表操作类型(增、删、改)并将修改后的数据映射到新表


该文章为原创,日后可能会根据实际开发经验和网友评论,进行相应地方修改,为获得最新博客动态,望在转发博客的时候注明出处。

触发器要实现的功能:

(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] NULL8 ) 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 NULL9 ) 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 > 018         AND @deleted = 019         BEGIN 20           SET @ChangeType = 'INSERT';21         END;22       ELSE23         IF @inserted > 024           AND @deleted > 025           BEGIN 26             SET @ChangeType = 'UPDATE';27           END;28         ELSE29           IF @inserted = 030             AND @deleted > 031             BEGIN 32               SET @ChangeType = 'DELETE';33             END;34       IF @ChangeType = 'DELETE'35         BEGIN36           SELECT @maxID = Id37           FROM  Deleted;38           --如果对Table1同一条数据,进行多次操作,则Table2只保存最新数据  39           IF EXISTS ( SELECT COUNT(1)40                 FROM  Table241                 WHERE  Id = @maxID )42             BEGIN 43               DELETE FROM Table244               WHERE  Id = @maxID;  45             END;46           INSERT INTO Table247               ( 48               )49               SELECT ID,50                   Name,51                   Sex,52                   Address,53                   Age,54                   Birthday,55                   @ChangeType AS ChangeType  56               FROM  Deleted;57         END;58       ELSE59         BEGIN60           SELECT @maxID = Id61           FROM  Inserted;  62           IF EXISTS ( SELECT COUNT(1)63                 FROM  Table264                 WHERE  Id = @maxID )65             BEGIN 66               DELETE FROM Table267               WHERE  Id = @maxID;  68             END;69           INSERT INTO Table270               ( 71                 ID,72                 Name,73                 Sex,74                 Address,75                 Age,76                 Birthday,77                 ChangeType78               )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 TRY90     BEGIN CATCH91       IF XACT_STATE() = -192         ROLLBACK TRAN;93     END CATCH; 94   END;95