SQL：触发器未执行

发布于 2024-11-03 19:28:33 字数 475 浏览 0 评论 0原文

我创建了一个触发器，当插入的时间小于 GETDATE() 时，将数据类型 time 的列的值设置为 null

ALTER trigger [dbo].[NULL_TIME_TRIGGER]
  on [dbo].[Products]
  after insert
  as
  update Products
  set ParkingStartTime = NULL
  from Products
   join inserted i
     on i.ParkingStartTime  = Products.ParkingStartTime 
  where i.ParkingStartTime < CAST(GETDATE() AS TIME);

问题是，当 I select * From table 时，记录仍然是有时间而不是NULL。

有什么想法吗？

问候。

原文

I have created a trigger to set the value of a column of the data type time to null when the time inserted is Less than GETDATE()

ALTER trigger [dbo].[NULL_TIME_TRIGGER]
  on [dbo].[Products]
  after insert
  as
  update Products
  set ParkingStartTime = NULL
  from Products
   join inserted i
     on i.ParkingStartTime  = Products.ParkingStartTime 
  where i.ParkingStartTime < CAST(GETDATE() AS TIME);

The problem is that when I select * From table the record is still having the time rather than being NULL.

Any Ideas ?

Regards.

分享到QQ

分享到微博

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

发布评论

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

浅唱々樱花落 2024-11-10 19:28:33

我认为最后，您需要一个 sql 作业，当时间条件小于 getDate() 时，它只是查看并将时间设置为 null

回复收藏 0 原文

霓裳挽歌倾城醉 2024-11-10 19:28:33

rsenna 有一种选择，并且在很多方面都更胜一筹。以下是我将如何播放您的 AFTER INSERT 版本（假设 int 为 PK）：

ALTER trigger [dbo].[NULL_TIME_TRIGGER]       
ON [dbo].[Products]       
AFTER INSERT       
AS

DECLARE @ID int

SELECT @ID = ID FROM INSERTED

UPDATE Products
SET ParkingStartTime = NULL
WHERE ID = @ID

rsenna has one option, and it is superior in many respects. Here is how I would play your AFTER INSERT version (asssume int as PK):

ALTER trigger [dbo].[NULL_TIME_TRIGGER]       
ON [dbo].[Products]       
AFTER INSERT       
AS

DECLARE @ID int

SELECT @ID = ID FROM INSERTED

UPDATE Products
SET ParkingStartTime = NULL
WHERE ID = @ID

回复收藏 0 原文

酒几许 2024-11-10 19:28:33

我真诚地不知道你的触发器出了什么问题，据我所知它应该可以工作。

但这绝对不是正确的选择。您似乎正在尝试验证一些插入的值。如果是这种情况，您应该使用 INSTEAD OF INSERT 触发器，而不是像您当前所做的那样使用 AFTER INSERT。

所以它会是这样的：

ALTER trigger [dbo].[NULL_TIME_TRIGGER]
  on [dbo].[Products]
  instead of insert
  as
  begin
      insert into Products (
          field1, field2, ..., fieldn, ParkingStartTime
      ) select
          field1, field2, ..., fieldn,
          case when ParkingStartTime < CAST(GETDATE() AS TIME) then NULL else ParkingStartTime end
      from
          inserted
  end
  go

这样 SQL Server 将拦截对 Products 表的所有插入，并且只会执行触发器内的插入，从而修复 ParkingStartTime 值。因此触发器不需要执行另一个更新来使它们正确。

希望有帮助。

I sincerely don't know whats wrong with your trigger, it should be working AFAIK.

But this is definitely not the way to go. You seem to be trying to validate some inserted values. If that's the case, you should be using an INSTEAD OF INSERT trigger, not an AFTER INSERT, like you're currently doing.

So it would be like this:

ALTER trigger [dbo].[NULL_TIME_TRIGGER]
  on [dbo].[Products]
  instead of insert
  as
  begin
      insert into Products (
          field1, field2, ..., fieldn, ParkingStartTime
      ) select
          field1, field2, ..., fieldn,
          case when ParkingStartTime < CAST(GETDATE() AS TIME) then NULL else ParkingStartTime end
      from
          inserted
  end
  go

That way SQL Server will intercept all inserts into the Products table, and will only execute the insert inside the trigger, which fixes the ParkingStartTime value. So the trigger doesn't need to execute another update to make them right.

Hope it helps.

回复收藏 0 原文