SQL 而不是触发器有时不触发？

发布于 2024-10-12 22:06:26 字数 486 浏览 1 评论 0原文

Table Event有一个替代触发器，其目的是生成主键EventId为Max+1，其余列由insert填充。

EventId 不是身份，我们无法使其身份，因为那里有很多依赖性，触发逻辑：

SELECT TOP 1 @ID = Event.EventID FROM Event
IF (@ID IS NULL)
BEGIN
  SET @ID=1
END
ELSE
BEGIN
  SELECT @ID = MAX(Event.EventID) FROM Event
  SET @ID=@ID+1
END
--Then just a insert statment with this id as EventId and rest of the columns from inserted table

现在有时当我尝试插入到该表中时，它仍然说无法在 eventId 中插入重复项，不知道为什么会这样正在发生...

看起来触发器在某些情况下没有触发？为什么

原文

Table Event has one instead of trigger, whos purpose is to generate primary key EventId as Max+1, rest of the columns are populated from inserted.

EventId is not Identity, and we can't make it identity as there is lots of depedency there, Trigger logic:

SELECT TOP 1 @ID = Event.EventID FROM Event
IF (@ID IS NULL)
BEGIN
  SET @ID=1
END
ELSE
BEGIN
  SELECT @ID = MAX(Event.EventID) FROM Event
  SET @ID=@ID+1
END
--Then just a insert statment with this id as EventId and rest of the columns from inserted table

Now sometimes when i tries to insert into this table, it still says can't insert duplicate in eventId, Not sure why this is happning...

Looks like trigger is not fireing in some case? Why

分享到QQ

分享到微博

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

发布评论

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

夜唯美灬不弃 2024-10-19 22:06:26

您可能假设插入的伪表中只有一行，并且在发生多行插入时失败。

你想要这样的东西：

;with maxID as (
    select MAX(EventID) as EventID from Event
), nrows as (
    select
        ROW_NUMBER() OVER (ORDER BY newid()) +
            COALESCE((select EventID from maxID),0) as EventID,
        /* columns from inserted table */
    from inserted
)
insert into Event (EventID,/* other columns */)
select EventID,/* other columns */
from nrows

You're probably assuming that there's a single row in the inserted pseudo table, and failing when a multi-row insert occurs.

You want something like:

;with maxID as (
    select MAX(EventID) as EventID from Event
), nrows as (
    select
        ROW_NUMBER() OVER (ORDER BY newid()) +
            COALESCE((select EventID from maxID),0) as EventID,
        /* columns from inserted table */
    from inserted
)
insert into Event (EventID,/* other columns */)
select EventID,/* other columns */
from nrows

回复收藏 0 原文