如何为SQL Server编写这个触发器？

发布于 2024-10-01 15:30:38 字数 547 浏览 2 评论 0原文

假设我有一个表 my_table(id, x, y)。我想编写一个触发器来防止更新 y col 并将其设置为 non-null 值（如果 x 已为 null）。由于 SQL Server 没有更新前触发器，那么如何做到这一点呢？显然我们可以使用而不是触发器来达到此目的，但是我们如何检查旧值和当前值并决定是否应该引发错误或让更新正常执行？

示例：

假设我们在数据库中有这一行：

1, null, null

那么这应该会失败（引发错误）

update my_table set y = 'blah' where id = 1;

但这应该会成功：

update my_table set y = null where id = 1;

我知道这个示例不是很有意义，但它与我想要实现的目标类似。

原文

Suppose that I have a table my_table(id, x, y). I want to write a trigger to prevent updating the y col and setting it to a non-null value if x is already null. As SQL Server doesn't have a before update trigger, how can this be done? Apparently we can use an instead of trigger for this purpose, but how can we check the old and current values and decide whether we should raise an error or let the update execute normally?

Example:

Let's pretend we have this row in the DB:

1, null, null

Then this should fail (raise error)

update my_table set y = 'blah' where id = 1;

But this should succeed:

update my_table set y = null where id = 1;

I know the example isn't very meaningful, but it is similar to what I am trying to achieve.

分享到QQ

分享到微博

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

发布评论

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

与酒说心事 2024-10-08 15:30:38

这应该可行，但我不确定您需要处理哪些其他边缘条件：

create table my_table (id int identity, x varchar(20), y varchar(20))
go 
CREATE TRIGGER tgNotNullYonMyTable
ON my_table
FOR UPDATE
AS 
IF UPDATE(y)
   BEGIN
       IF exists (
                  select 1 
                    from deleted d 
                         join inserted i on i.id = d.id 
                   where (d.x is null or i.x is null)
                         and i.y is null
                 ) 
          BEGIN
              RAISERROR ('Leave Y alone if x is null.', 16, 1)
              rollback tran
          END
   END
go

insert my_table values (null,null)

go 

update my_table set y = 'blah' where id = 1;
go 
update my_table set y = null where id = 1;

This should work, but I am not sure what other edge conditions you need to handle:

create table my_table (id int identity, x varchar(20), y varchar(20))
go 
CREATE TRIGGER tgNotNullYonMyTable
ON my_table
FOR UPDATE
AS 
IF UPDATE(y)
   BEGIN
       IF exists (
                  select 1 
                    from deleted d 
                         join inserted i on i.id = d.id 
                   where (d.x is null or i.x is null)
                         and i.y is null
                 ) 
          BEGIN
              RAISERROR ('Leave Y alone if x is null.', 16, 1)
              rollback tran
          END
   END
go

insert my_table values (null,null)

go 

update my_table set y = 'blah' where id = 1;
go 
update my_table set y = null where id = 1;

回复收藏 0 原文

苍景流年 2024-10-08 15:30:38

CREATE TRIGGER yxnull
ON mytable
FOR UPDATE
AS 
IF UPDATE(y)
   BEGIN
       IF deleted.x is null and inserted.y is not null
          BEGIN
              RAISERROR ('Leave Y alone if x is null.', 16, 1)
              ROLLBACK TRANSACTION

          END
   END
go

CREATE TRIGGER yxnull
ON mytable
FOR UPDATE
AS 
IF UPDATE(y)
   BEGIN
       IF deleted.x is null and inserted.y is not null
          BEGIN
              RAISERROR ('Leave Y alone if x is null.', 16, 1)
              ROLLBACK TRANSACTION

          END
   END
go

回复收藏 0 原文

~没有更多了~