sqlserver的触发器练习实例

最新推荐文章于 2023-08-21 16:16:29 发布

weixin_30514745

最新推荐文章于 2023-08-21 16:16:29 发布

阅读量147

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/feifeicui/p/8868595.html

本文介绍了一个具体的SQL触发器实现案例，展示了如何通过触发器记录表Table1上的插入、更新及删除操作，并将这些变更同步到表Table2中，同时记录操作类型。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

https://www.cnblogs.com/julinhuitianxia/p/6823011.html

https://www.cnblogs.com/xinlj/p/5138974.html

触发器要实现的功能：

（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

CREATE TABLE Table1(
    [ID] [BIGINT] IDENTITY(1,1) NOT NULL,
    [Name] [NVARCHAR](20) NULL,
    [Sex] [NVARCHAR](2) NULL,
    [Address] [NVARCHAR](50) NULL,
    [Age] [INT] NULL,
    [Birthday] [DATE] NULL
) ON [PRIMARY]

（2）表Table2

CREATE TABLE Table2(
     [ID] [BIGINT] NOT NULL,
     [Name] [NVARCHAR](20) NULL,
     [Sex] [NVARCHAR](2) NULL,
     [Address] [NVARCHAR](50) NULL,
     [Age] [INT] NULL,
     [Birthday] [DATE] NULL,
     [ChangeType] [NVARCHAR](50) NOT NULL
 ) ON [PRIMARY]

触发器Tri_Table1

CREATE  TRIGGER [Triger_Table1] ON [Table1]
    --After触发器，对表进行insert、delete、update后触发
    AFTER INSERT, DELETE, UPDATE 
AS
    BEGIN
        BEGIN TRY
            BEGIN TRAN;
            DECLARE @maxID NVARCHAR(50) ,
                @inserted INT ,
                @deleted INT ,
                @ChangeType NVARCHAR(20);
            SELECT  @inserted = COUNT(1)
            FROM    Inserted;
            SELECT  @deleted = COUNT(1)
            FROM    Deleted;
            --判断对表Table1的操作类型
            IF @inserted > 0
                AND @deleted = 0
                BEGIN 
                    SET @ChangeType = 'INSERT';
                END;
            ELSE
                IF @inserted > 0
                    AND @deleted > 0
                    BEGIN 
                        SET @ChangeType = 'UPDATE';
                    END;
                ELSE
                    IF @inserted = 0
                        AND @deleted > 0
                        BEGIN 
                            SET @ChangeType = 'DELETE';
                        END;
            IF @ChangeType = 'DELETE'
                BEGIN
                    SELECT  @maxID = ID
                    FROM    Deleted;
                    --如果对Table1同一条数据，进行多次操作，则Table2只保存最新数据    
                    IF EXISTS ( SELECT  COUNT(1)
                                FROM    Table2
                                WHERE   ID = @maxID )
                        BEGIN 
                            DELETE  FROM Table2
                            WHERE   ID = @maxID;    
                        END;
                    INSERT  INTO Table2
                            ( 
                                                                ID,
                                Name,
                                Sex,
                                Address,
                                Age,
                                Birthday,
                                ChangeType
                            )
                            SELECT  ID,
                                    Name,
                                    Sex,
                                    Address,
                                    Age,
                                    Birthday,
                                    @ChangeType AS ChangeType   
                            FROM    Deleted;
                END;
             ELSE
                BEGIN
                    SELECT  @maxID = ID
                    FROM    Inserted;    
                    IF EXISTS ( SELECT  COUNT(1)
                                FROM    Table2
                                WHERE   ID = @maxID )
                        BEGIN 
                            DELETE  FROM Table2
                            WHERE   ID = @maxID;    
                        END;
                    INSERT  INTO Table2
                            ( 
                                ID,
                                Name,
                                Sex,
                                Address,
                                Age,
                                Birthday,
                                ChangeType
                            )
                            SELECT  ID,
                                    Name,
                                    Sex,
                                    Address,
                                    Age,
                                    Birthday,
                                    @ChangeType AS ChangeType                           
                            FROM    Inserted 
                END;   
            COMMIT TRAN;
        END TRY
        BEGIN CATCH
            IF XACT_STATE() = -1
                ROLLBACK TRAN;
        END CATCH;  
    END;

下面是只有新增和修改的

CREATE TABLE Table1(
    [ID] [BIGINT] IDENTITY(1,1) NOT NULL,
    [Name] [NVARCHAR](20) NULL,
    [Sex] [NVARCHAR](2) NULL,
    [Address] [NVARCHAR](50) NULL,
    [Age] [INT] NULL,
    [Birthday] [DATE] NULL
) ON [PRIMARY]

CREATE TABLE Table2(
     [ID] [BIGINT] IDENTITY(1,1) NOT NULL,
     [Name] [NVARCHAR](20) NULL,
     [Sex] [NVARCHAR](2) NULL,
     [Address] [NVARCHAR](50) NULL,
     [Age] [INT] NULL,
     [Birthday] [DATE] NULL,
     [ChangeType] [NVARCHAR](50) NOT NULL
 ) ON [PRIMARY]

CREATE  TRIGGER [Triger_Table1] ON [Table1]
    --After触发器，对表进行insert、delete、update后触发
    AFTER INSERT, UPDATE 
AS
    BEGIN
        BEGIN TRY
            BEGIN TRAN;
            DECLARE @maxID NVARCHAR(50) ,
                @inserted INT ,
                @deleted INT ,
                @ChangeType NVARCHAR(20);
            SELECT  @inserted = COUNT(1)
            FROM    Inserted;
            SELECT  @deleted = COUNT(1)
            FROM    Deleted;
            --判断对表Table1的操作类型
            IF @inserted > 0
                AND @deleted = 0
                BEGIN 
                    SET @ChangeType = 'INSERT';
                END;
            ELSE
                IF @inserted > 0
                    AND @deleted > 0
                    BEGIN 
                        SET @ChangeType = 'UPDATE';
                    END;          

                BEGIN                         
                    INSERT  INTO Table2
                            ( 
                               
                                Name,
                                Sex,
                                Address,
                                Age,
                                Birthday,
                                ChangeType
                            )
                            SELECT  
                                    Name,
                                    Sex,
                                    Address,
                                    Age,
                                    Birthday,
                                    @ChangeType AS ChangeType                           
                            FROM    Inserted 
                END;   
            COMMIT TRAN;
        END TRY
        BEGIN CATCH
            IF XACT_STATE() = -1
                ROLLBACK TRAN;
        END CATCH;  
    END;