blocks|key|2578992|text|在运行DELETE语句之前，请尝试使用BEGIN+TRANSACTION。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2578993|然后你可以选择COMMIT或ROLLBACK+COMMIT。|2578994|entityMap^0|3|6|J|H|0|7|6|E|8|N|6|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]|$9|M|A|N|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|H|3|-4|5|6|7|V|8|@]|D|@]|E|$]]]|I|$]]

Try using a <code>BEGIN TRANSACTION</code> before you run your <code>DELETE</code> statement.

Then you can choose to <code>COMMIT</code> or <code>ROLLBACK</code> same.

blocks|key|2579032|text|在Tools>Options>Query+Execution>SQL+Server>ANSI下，您可以启用隐式事务选项，这意味着您不需要显式包含Begin+Transaction命令。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2579033|这样做的明显缺点是，您可能忘记在末尾添加提交(或回滚)，或者更糟糕的是，默认情况下，您的同事会在每个脚本的末尾添加Commit。|2579034|你可以把马引到水边。|2579035|您可能会建议他们在做任何事情(取决于数据库的大小)之前始终进行临时备份，以防万一。|2579036|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

Under Tools>Options>Query Execution>SQL Server>ANSI, you can enable the Implicit Transactions option which means that you don't need to explicitly include the Begin Transaction command.

The obvious downside of this is that you might forget to add a Commit (or Rollback) at the end, or worse still, your colleagues will add Commit at the end of every script by default.

You can lead the horse to water...

You might suggest that they always take an ad-hoc backup before they do anything (depending on the size of your DB) just in case.

blocks|key|2579058|text|这就是为什么我认为你应该永远：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2579059|1在部署到生产环境之前，使用在开发数据库上经过测试的存储过程|2579060|2选择删除前的数据|2579061|3使用面试和绩效评估流程筛选开发人员:)|2579062|4根据他们删除/不删除的数据库表数量进行性能评估|2579063|5对待生产数据就像对待它是有毒的，并且非常害怕。|2579064|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|P|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|Q|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|R|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|S|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|M|$]]

That is why I believe you should always:

1 Use stored procedures that are tested on a dev database before deploying to production

2 Select the data before deletion

3 Screen developers using an interview and performance evaluation process :)

4 Base performance evaluation on how many database tables they do/do not delete

5 Treat production data as if it were poisonous and be very afraid

blocks|key|2579124|text|穿上你最好的Trogdor和Burninate，直到他们学会放在WHERE子句中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2579125|最好的建议是让在数据库中乱七八糟的东西在测试时使用事务。它对防止“哇”的时刻有很大的帮助。需要注意的是，现在你必须告诉他们提交或回滚，因为他们肯定会锁定你的数据库至少一次。|2579126|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Put on your best Trogdor and Burninate until they learn to put in the WHERE clause.

The best advice is to get the muckety-mucks that are mucking around in the database to use transactions when testing. It goes a long way towards preventing "whoops" moments. The caveat is that now you have to tell them to COMMIT or ROLLBACK because for sure they're going to lock up your DB at least once.

blocks|key|1909787|text|在SSMS2005中，您可以在Tools%7COptions%7CQuery+Execution%7CSQL+Server%7CANSI+...下启用此选项。检查SET+IMPLICIT_TRANSACTIONS。这将需要提交才能影响将来连接的更新/删除查询。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1909788|对于当前查询，请转到查询%7C查询选项%7C执行%7CANSI并选中相同的复选框。|1909789|如果您正在使用SSMS2000，This+page还提供了相应的说明。|1909790|正如其他人所指出的，这并不能解决根本原因:在您创建的每个新查询的末尾粘贴一个提交几乎和一开始启动一个查询一样容易。|1909791|entityMap|0|LINK|mutability|MUTABLE|url|http://bytes.com/forum/thread499461.html^0|21|P|0|0|G|9|0|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@$9|U|A|V|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|W|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|X|8|@]|D|@$9|Y|A|Z|1|10]]|E|$]]|$1|J|3|K|5|6|7|11|8|@]|D|@]|E|$]]|$1|L|3|-4|5|6|7|12|8|@]|D|@]|E|$]]]|M|$N|$5|O|P|Q|E|$R|S]]]]

In SSMS 2005, you can enable this option under Tools|Options|Query Execution|SQL Server|ANSI ... check <code>SET IMPLICIT_TRANSACTIONS</code>. That will require a commit to affect update/delete queries for future connections. 

For the current query, go to Query|Query Options|Execution|ANSI and check the same box.

<a href="http://bytes.com/forum/thread499461.html" rel="nofollow noreferrer">This page</a> also has instructions for SSMS 2000, if that is what you're using.

As others have pointed out, this won't address the root cause: it's almost as easy to paste a COMMIT at the end of every new query you create as it is to fire off a query in the first place.

blocks|key|1903878|text|我建议您应该始终使用带有WHERE子句的写SELECT语句，然后执行它来实际查看您的delete命令将删除哪些行。然后使用相同的WHERE子句执行DELETE。这同样适用于更新。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1903879|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I'd suggest that you should always write SELECT statement with WHERE clause first and execute it to actually see what rows will your DELETE command delete. Then just execute DELETE with the same WHERE clause. The same applies for UPDATEs.

blocks|key|4693381|text|因此，连续第二天，有人擦除了整个数据表，而不是他们试图删除的一行，因为他们没有限定的where子句|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4693382|可能唯一的解决方案将是用其他人替换某人;)。否则，他们总会找到解决办法|4693383|最终限制该人员的数据库访问，并为他们提供存储过程，该存储过程采用where子句中使用的参数，并授予他们执行该存储过程的访问权限。|4693384|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

So for the second day in a row, someone has wiped out an entire table of data as opposed to the one row they were trying to delete because they didn't have the qualified where clause

Probably the only solution will be to replace someone with someone else ;). Otherwise they will always find their workaround

Eventually restrict the database access for that person and provide them with the stored procedure that takes the parameter used in the where clause and grant them access to execute that stored procedure.

blocks|key|4693442|text|首先，这就是审计表的用途。如果您知道是谁删除了所有记录，那么您可以限制他们的数据库权限，或者从性能的角度来处理他们。上一个在我办公室干这事的人现在还在缓刑中。如果她再这样做，她会被解雇的。如果您有权访问生产数据，并确保不会造成损害，那么您就有责任。这既是一个技术问题，也是一个性能问题。你永远不会找到一种方法来防止人们犯愚蠢的错误(数据库没有办法知道你的意思是删除表a还是删除表a，其中id+=+100，大多数人会自动点击确认)。您只能通过确保运行此代码的人是负责任的，并制定适当的策略来帮助他们记住要做什么来减少它们。对你的业务数据有不负责任行为的员工(特别是在受到警告后)应该被解雇。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4693443|其他人提出了我们应该做的事情，以防止这种情况发生。我总是在从查询窗口运行的delete中嵌入select，以确保它只删除我想要的记录。我们在生产环境中更改、插入或删除数据的所有代码都必须包含在一个事务中。如果它是手动运行的，则在看到受影响的记录数之前，不会运行回滚或提交。|4693444|嵌入select的delete示例|4693445|delete+a
--select+a.*+from+
from+table1+a+
join+table+2+b+on+a.id+=+b.id
where+b.somefield+=+'test'|code-block|syntax|javascript|4693446|但即使是这些技术也不能防止所有的人为错误。不理解数据的开发人员可能会运行select，但仍然不理解它删除了太多记录。在事务中运行可能意味着当人们忘记提交或回滚并锁定系统时，您可能会遇到其他问题。或者，人们可能将其放入事务中，但仍然不假思索地点击提交，就像他们在消息框上点击确认(如果有确认)一样。最好的预防方法是有一种方法可以从这样的错误中快速恢复。从审核日志表中恢复往往比从备份中恢复更快。此外，您还可以知道是谁犯了错误，以及哪些记录受到了影响(也许您没有删除整个表，但where子句是错误的，并且您删除了一些错误的记录)。|4693447|在大多数情况下，生产数据不应该在运行中更改。您应该编写更改脚本，并首先在dev上检查它。然后在prod上，您所要做的就是不做任何更改地运行脚本，而不是一次突出显示和运行一小部分。现在，在现实世界中，这并不总是可能的，因为有时您只在prod上修复某些损坏的东西，而现在需要修复这些损坏的东西(例如，当您的客户都无法登录时，因为关键数据被删除)。在这种情况下，您可能没有机会在dev上先重现问题，然后再编写修复程序。当您遇到这些类型的问题时，您可能需要直接在prod上进行修复，并且应该只让dbas或数据库分析师、配置经理或其他通常负责prod上的数据的人员进行修复，而不是由开发人员进行修复。一般情况下，开发人员不应该有访问prod的权限。|4693448|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|S|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|T|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

First, this is what audit tables are for. If you know who deleted all the records you can either restrict their database privileges or deal with them from a performance perspective. The last person who did this at my office is currently on probation. If she does it again, she will be let go. You have responsibilites if you have access to production data and ensuring that you cause no harm is one of them. This is a performance problem as much as a technical problem. You will never find a way to prevent people from making dumb mistakes (the database has no way to know if you meant delete table a or delete table a where id = 100 and a confirm will get hit automatically by most people). You can only try to reduce them by making sure the people who run this code are responsible and by putting into place policies to help them remember what to do. Employees who have a pattern of behaving irresponsibly with your busness data (particulaly after they have been given a warning) should be fired. 

Others have suggested the kinds of things we do to prevent this from happening. I always embed a select in a delete that I'm running from a query window to make sure it will delete only the records I intend. All our code on production that changes, inserts or deletes data must be enclosed in a transaction. If it is being run manually, you don't run the rollback or commit until you see the number of records affected. 

Example of delete with embedded select

<pre><code>delete a
--select a.* from 
from table1 a 
join table 2 b on a.id = b.id
where b.somefield = 'test'
</code></pre>

But even these techniques can't prevent all human error. A developer who doesn't understand the data may run the select and still not understand that it is deleting too many records. Running in a transaction may mean you have other problems when people forget to commit or rollback and lock up the system. Or people may put it in a transaction and still hit commit without thinking just as they would hit confirm on a message box if there was one. The best prevention is to have a way to quickly recover from errors like these. Recovery from an audit log table tends to be faster than from backups. Plus you have the advantage of being able to tell who made the error and exactly which records were affected (maybe you didn't delete the whole table but your where clause was wrong and you deleted a few wrong records.)

For the most part, production data should not be changed on the fly. You should script the change and check it on dev first. Then on prod, all you have to do is run the script with no changes rather than highlighting and running little pieces one at a time. Now inthe real world this isn't always possible as sometimes you are fixing something broken only on prod that needs to be fixed now (for instance when none of your customers can log in because critical data got deleted). In a case like this, you may not have the luxury of reproducing the problem first on dev and then writing the fix. When you have these types of problems, you may need to fix directly on prod and you should have only dbas or database analysts, or configuration managers or others who are normally responsible for data on the prod do the fix not a developer. Developers in general should not have access to prod.

blocks|key|4693468|text|难道没有一种方法可以在不提供对SQL的原始访问的情况下为用户提供所需的结果吗？如果您至少有一个单独的"WHERE“输入框，则可以将其默认为"WHERE+1=+0”或其他值。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4693469|我认为一定也有一种方法可以将它们从事务日志中剔除。但如果不回滚所有内容，然后有选择地重新应用致命错误之后出现的任何内容，可能就不会这样。|4693470|另一个丑陋的选择是创建一个触发器，将所有删除(可能超过最小数量的记录)写入一个日志表。|4693471|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Isn't there a way to give users the results they need without providing raw access to SQL? If you at least had a separate entry box for "WHERE", you could default it to "WHERE 1 = 0" or something.

I think there must be a way to back these out of the transaction journaling, too. But probably not without rolling everything back, and then selectively reapplying whatever came after the fatal mistake.

Another ugly option is to create a trigger to write all DELETEs (maybe over some minimum number of records) to a log table.

blocks|key|2579455|text|锁定它：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2579456|所有表的REVOKE删除权限。|offset|length|style|CODE|2579457|放入一个审计触发器和审计表。|2579458|创建参数化的删除SP，并仅根据需要授予执行权限。|2579459|entityMap^0|0|4|6|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@$D|P|E|Q|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|R|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|S|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|M|$]]

Lock it down:

<code>REVOKE</code> delete rights on all your tables.

Put in an audit trigger and audit table.

Create parametrized delete SPs and only give rights to execute on an as needed basis.

So for the second day in a row, someone has wiped out an entire table of data as opposed to the one row they were trying to delete because they didn't have the qualified where clause.

I've been all up and down the mgmt studio options, but can't find a confirm option. I know other tools for other databases have it.

Confirm before delete/update in SQL Management Studio?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

因此，连续第二天，有人擦除了整个数据表，而不是他们试图删除的一行，因为他们没有限定的where子句。我在mgmt的演播室里到处找，但就是找不到一个确认选项。我知道其他数据库的工具也有这个功能。

问是否在SQL Management Studio中删除/更新前确认？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问是否在SQL Management Studio中删除/更新前确认？EN