在 SQL Server 2008 中捕获主键约束冲突 [英] Capture primary key constraint violation in SQL server 2008

问题描述

有没有办法自动捕获(在 SQL 服务器日志或窗口事件日志中)由于插入到具有重复主键值的 SQL 服务器表而导致的约束冲突?

is there a way to automatically capture (either in SQL server log or Window Event Log) constraint violation due to Inserts into SQL server table with duplicate primary key values?

例如:当我尝试在 SQL Server Management Studio 中插入重复的主键时，出现以下错误

for Ex: When I try to insert duplicate primary key in SQL Server Management Studio, i get the following error

消息 2627，级别 14，状态 1，第 2 行违反 PRIMARY KEY 约束PK_客户_A4AE64D87F60ED59".无法在对象dbo.Customer"中插入重复键.声明已终止.

Msg 2627, Level 14, State 1, Line 2 Violation of PRIMARY KEY constraint 'PK_Customer_A4AE64D87F60ED59'. Cannot insert duplicate key in object 'dbo.Customer'. The statement has been terminated.

但我无法在 SQL 服务器日志或 Windows 事件日志中找到该消息.

But i am unable to find that message in SQL server Logs or Windows Event log.

谢谢

推荐答案

SQL 不会自动记录该信息.有很多方法可以做到这一点，但您必须自己进行设置/配置，而且实施起来都不是很简单.

That information does not get automatically logged by SQL. There are a number of ways to do this, but you would have to do the setup/configuration yourself, and none of them would be simple to implement.

最简单"的方法是使用 SQL Profiler，跟踪异常事件，并过滤适当的错误号(或数字，可能有几个类似的异常要跟踪).如果您想要永久记录此信息，这可能会出现问题.

The "simplest" one is to use SQL Profiler, tracking the Exception event, and filtering on the appropriate error number (or numbers, there might be several similar exceptions to trace). This could be problematic if you're want a permanent always-on recording of this information.

SQL 代理警报系统可能会执行某些操作.我从来没有用过这个，我似乎记得它要求将警报写入 Windows 事件日志，所以它可能不起作用 - 但它仍然值得研究.

Something might be done with the SQL Agent Alert system. I've never used this, and I seem to recall it requring that alerts be written to the Windows event log, so it might not work--but it still might be worth looking in to.

大多数其他事情都需要修改您的代码，以捕获和跟踪引发的错误.这可能会很尴尬，细节将取决于您的代码库的性质和范围.

Most anything else requires modifying your code, to trap and track raised errors. This could be very awkward, and details would depend on the natrure and extent of your code base.

这篇关于在 SQL Server 2008 中捕获主键约束冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！