时间戳

首页 > 解决方案 > 如何使用触发器更新 SQL Server 中的列

sql-server - 如何使用触发器更新 SQL Server 中的列

问题描述

我正在尝试在 SQL Server Management Studio 中创建一个触发器，当在同一个表中更新了单独的列时，该触发器会将列值增加 1。

运行更新脚本时我们要更新的列的值变为 NULL

我的例子是，当我更改客户的地址时，我想要一个每次更改地址时增加 1 的列，即 NoOfAddressess = 1、2、3 等...

这是我正在编写的 SQL 代码

ALTER TRIGGER trg_customeraudit 
ON tblCustomer
AFTER UPDATE, DELETE, INSERT
AS
    INSERT INTO dbo.CustomerDetailsAudit 
    VALUES (CURRENT_USER, CURRENT_TIMESTAMP, 
            (SELECT CustomerID FROM inserted), 
            (SELECT CustomerAddress FROM deleted), 
            (SELECT CustomerAddress FROM inserted),
            (SELECT CustomerPostcode FROM deleted), 
            (SELECT CustomerPostcode FROM inserted), 
            (SELECT NumberOfChangedAddresses FROM dbo.CustomerDetailsAudit)  
           )

    IF ((SELECT CustomerAddress FROM inserted) = 
        (SELECT CustomerAddress FROM deleted) OR 
        (SELECT CustomerPostcode FROM deleted) = 
        (SELECT CustomerPostcode FROM inserted))
    BEGIN
        RAISERROR ('You must enter both a new postcode and address',16,10)
        ROLLBACK TRANSACTION
    END 
ELSE
BEGIN 
    PRINT 'Transaction successful'
    WHERE CustomerID = (SELECT CustomerID from inserted)
END

IF UPDATE (CustomerName)
BEGIN
    RAISERROR ('You cannot change the customer name', 16, 10)
    ROLLBACK
END

标签： sql-servertsqltriggers

解决方案

取决于此数据上发生的其他事情，触发器可能是一种非常低效的处理方法，但这是一种可能的解决方案。

1. 设置

首先创建一个表用于测试。

create table test_table (
      MyPrimaryKey int primary key clustered not null identity(1, 1)
    , SomeColumn varchar(255) not null
    , SomeColumnCounter int null
);
go

现在，添加一个触发器以将计数器初始化为 1。这可以由默认约束处理或在应用程序级别设置，但也可以通过触发器完成。

-- this trigger will set the counter to 1 when a record is first added
-- doesn't need to be a trigger, but since the question was on triggers
create trigger trg_test_table_insert
    on test_table
    after insert
as
    update tt
        set tt.SomeColumnCounter = 1
    from
        test_table as tt
    inner join
        Inserted as i
        on
            tt.MyPrimaryKey = i.MyPrimaryKey;
go

现在，添加一个触发器，该触发器将检查指定列上的更改并在需要时增加计数器。

-- this trigger will increment the counter by 1 if 'SomeColumn' changed
-- doesn't handle nulls so will need to be modified depending on schema
create trigger trg_test_table_update
    on test_table
    after update
as
    update tt
        set tt.SomeColumnCounter = tt.SomeColumnCounter + 1
    from
        Inserted as i -- new version of the record
    inner join
        Deleted as d -- old version of the record
        on
            i.MyPrimaryKey = d.MyPrimaryKey
            and i.SomeColumn <> d.SomeColumn
    inner join
        test_table as tt
        on
            tt.MyPrimaryKey = i.MyPrimaryKey
go

2. 测试

添加一些测试数据。

insert into test_table (SomeColumn)
values ('abc'), ('def');
go

现在我们有：

MyPrimaryKey    SomeColumn  SomeColumnCounter
1               abc         1
2               def         1

更新而不更改任何内容：

update tt
    set tt.SomeColumn = 'abc'
from
    test_table as tt
where
    tt.MyPrimaryKey = 1

我们还有：

MyPrimaryKey    SomeColumn  SomeColumnCounter
1               abc         1
2               def         1

更新实际上改变了一些东西：

update tt
    set tt.SomeColumn = 'abbc'
from
    test_table as tt
where
    tt.MyPrimaryKey = 1

现在我们有：

MyPrimaryKey    SomeColumn  SomeColumnCounter
1               abbc        2
2               def         1

改变一切的更新：

update tt
    set tt.SomeColumn = tt.SomeColumn + 'z'
from
    test_table as tt

现在我们有：

MyPrimaryKey    SomeColumn  SomeColumnCounter
1               abbcz       3
2               defz        2

推荐阅读