SQL Server 触发器

发布于 2024-09-29 04:16:11 字数 863 浏览 0 评论 0原文

我有一个有 4 个字段的表。

Col1 Guid Primary-Key (FK to another table)
Col2 Guid Primary-Key (FK to another table)
Col3 Int Primary-Key
Col4 Int

现在我想这样做：当用户插入的新记录与之前存在的记录具有相同值时，如下表所示：

有此记录：

X Y Z 2

新记录

X Y Z 3

将现有记录更新到下面：

X Y Z 5

并防止插入重复记录。我写了非常基本的触发器。现在帮我完成这个。提前致谢。 ;)

CREATE TRIGGER [Tbl].[TriggerName] 
ON  [dbo].[Tbl] 
AFTER INSERT, UPDATE
AS 
BEGIN
SET NOCOUNT ON;
UPDATE [dbo].[Tbl]
SET [dbo].[Tbl].[Col4] += [Inserted].[Col4]
From [dbo].[Tbl] INNER JOIN [Inserted] ON [dbo].[Tbl].[Col1] = [Inserted].[Col1] AND
                                          [dbo].[Tbl].[Col2] = [Inserted].[Col2]
WHERE [dbo].[Tbl].[Col3] = [Inserted].[Col3];
//How to prevent from inserting duplicate record?
END

原文

I have a table which have 4 fields.

Col1 Guid Primary-Key (FK to another table)
Col2 Guid Primary-Key (FK to another table)
Col3 Int Primary-Key
Col4 Int

Now i want to do this: when a new record inserted by user which have same value as a record exist before on the table like below:

There was this Record:

X Y Z 2

New record

X Y Z 3

Update existing record to below:

X Y Z 5

and also prevent from inserting duplicate record. I wrote very basic trigger. now help me to complete this. Thanks in advance. ;)

CREATE TRIGGER [Tbl].[TriggerName] 
ON  [dbo].[Tbl] 
AFTER INSERT, UPDATE
AS 
BEGIN
SET NOCOUNT ON;
UPDATE [dbo].[Tbl]
SET [dbo].[Tbl].[Col4] += [Inserted].[Col4]
From [dbo].[Tbl] INNER JOIN [Inserted] ON [dbo].[Tbl].[Col1] = [Inserted].[Col1] AND
                                          [dbo].[Tbl].[Col2] = [Inserted].[Col2]
WHERE [dbo].[Tbl].[Col3] = [Inserted].[Col3];
//How to prevent from inserting duplicate record?
END

分享到QQ

分享到微博

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

发布评论

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

濫情▎り 2024-10-06 04:16:11

我将把我的评论合并为答案。

在这种情况下我会避免使用触发器。我相信最好的方法是编写一个可以从代码中调用的存储过程。该存储过程将执行“如果存在...更新...否则插入...”逻辑。

回复收藏 0 原文

久而酒知 2024-10-06 04:16:11

如果您使用的是 SQL Server 2008，则可以使用 MERGE

回复收藏 0 原文

撩心不撩汉 2024-10-06 04:16:11

正如 Joe Stefanelli 所说，最好的方法是通过使用插入或更新存储过程并撤销表上的 INSERT 权限来替换对表的直接插入。

但是，如果您对触发器死心塌地，请尝试将以下内容添加到触发器中

DELETE [dbo].[Tbl]
From [dbo].[Tbl] INNER JOIN [Inserted] ON [dbo].[Tbl].[Col1] = [Inserted].[Col1] AND
                                          [dbo].[Tbl].[Col2] = [Inserted].[Col2]
WHERE [dbo].[Tbl].[Col3] = [Inserted].[Col3] AND [dbo].[Tbl].[Col4] = [Inserted].[Col4];

As Joe Stefanelli said, the BEST approach is to replace the direct inserts into the table by having an insert-or-update stored proc and revoking the INSERT perms on the table.

However, if you are dead set on the trigger, try adding the following to the trigger

DELETE [dbo].[Tbl]
From [dbo].[Tbl] INNER JOIN [Inserted] ON [dbo].[Tbl].[Col1] = [Inserted].[Col1] AND
                                          [dbo].[Tbl].[Col2] = [Inserted].[Col2]
WHERE [dbo].[Tbl].[Col3] = [Inserted].[Col3] AND [dbo].[Tbl].[Col4] = [Inserted].[Col4];

回复收藏 0 原文

~没有更多了~