MsSQL DB 上 INSTEAD OF UPDATE 触发器的默认操作

发布于 2024-12-10 07:47:58 字数 620 浏览 0 评论 0原文

我在 MsSQL 中遇到 INSTEAD OF 触发器问题。我的应用程序出现一些错误，作为一种快速解决方法，我不希望用户修改数据库中的某一行。我创建了以下触发器：（在桌子上）

 create trigger UglyWorkaround ON Configuration instead of update 
  as
    begin
        if (select COUNT(1) from inserted where Key='Key01' and Value<>'2') > 0 begin
            update Configuration set Value='2' where Key='Key01'
        end else begin
            -- DEFAULT ACTION (do update as intended)
        end;


    end;

但是我在确定如何设置默认操作时遇到问题。 更新配置集 Value=inserted.Value where Key=inserted.Key 对我不起作用。有什么办法可以用触发器来做到这一点吗？（我知道这个解决方案很糟糕，但我没有其他选择，因为我现在无法更改代码。）

原文

I've got problem with INSTEAD OF trigger in MsSQL. I got an app with some error, and as a quick workaround I don't want user to modify one exact row in DB.
I created following trigger: (on table)

 create trigger UglyWorkaround ON Configuration instead of update 
  as
    begin
        if (select COUNT(1) from inserted where Key='Key01' and Value<>'2') > 0 begin
            update Configuration set Value='2' where Key='Key01'
        end else begin
            -- DEFAULT ACTION (do update as intended)
        end;


    end;

But I've got problem with determining, how to set default action.
Update Configuration set Value=inserted.Value where Key=inserted.Key doesn't work for me. Is there any way how to do this with triggers? (I know that the solution is bad, but I got no other option, as I can't change code now.)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

醉殇 2024-12-17 07:47:58

inserted 是一个表，因此请尝试加入：

update c set c.Value = i.Value
from Configuration c
    inner join inserted i on c.Key = i.Key

您也可以同时过滤掉 Key01，如果他们尝试更新 的值也没关系>Key01 为 2 之外的其他内容。

update c set c.Value = i.Value
from Configuration c
    inner join inserted i on c.Key = i.Key
where i.Key <> 'Key01'

inserted is a table, so try joining:

update c set c.Value = i.Value
from Configuration c
    inner join inserted i on c.Key = i.Key

You could also filter out Key01 at the same time, and it wouldn't matter if they tried to update the value for Key01 to something other than 2.

update c set c.Value = i.Value
from Configuration c
    inner join inserted i on c.Key = i.Key
where i.Key <> 'Key01'

回复收藏 0 原文

ペ泪落弦音 2024-12-17 07:47:58

INSTEAD OF 部分意味着您将必须更新其他列以及值列。

无需使用 if .. else 执行此操作，因为逻辑可以在列级别完成

create trigger NicerWorkaround ON Configuration instead of update 
as
begin

update c set 
c.Value = CASE WHEN i.Key='Key01' THEN '2' ELSE i.Value END,
c.OtherColumn1 = i.OtherColumn1,
c.OtherColumn2 = i.OtherColumn2
from Configuration c
inner join inserted i on c.Key = i.Key    
END;

The INSTEAD OF part means that you're going to have to update the other columns as well as the value one.

There's no need to do this with an if .. else as the logic can be done at column level

create trigger NicerWorkaround ON Configuration instead of update 
as
begin

update c set 
c.Value = CASE WHEN i.Key='Key01' THEN '2' ELSE i.Value END,
c.OtherColumn1 = i.OtherColumn1,
c.OtherColumn2 = i.OtherColumn2
from Configuration c
inner join inserted i on c.Key = i.Key    
END;

回复收藏 0 原文