无需存储过程或触发器即可工作
过去 18 个月来，我们一直致力于复杂的数据库和客户端界面的开发。 我们定期向该应用程序添加新功能，现在每天都有数十名用户在我们所有的办公室（包括站点和海外）使用它。 这只是为了告诉您这是一个具有真实数据库的真实应用程序。
到目前为止，我们仍然不需要编写任何存储过程，除非临时解决客户端版本和更新的数据库模型之间的小问题（旧客户端版本将无法正确更新新创建的字段，直到每个人都安装最新的客户端版本）版本）。
同样，我们仍然不需要任何触发器。 事实上，唯一的 SP 和触发器是系统的，或者是为了复制目的而添加的。
我有一种奇怪的感觉，当开发人员认为数据库优化必须反对数据库规范化时，SP 和触发器主要用于补偿数据库设计默认值和/或尝试绕过数据库设计规则。
问题是这些工具非常耗时（无论是开发还是维护）。 每个开发人员都应该非常小心地使用它们，记住它们是数据库中维护的最“昂贵”的项目。
我们是否可以认为数据库中没有或很少有存储过程/触发器可以很好地表明其规范化级别和/或其代码维护成本？
编辑：
你们中的一些人为触发器和 SP 的使用提供了公平的论据。 但我一直认为大多数时候这些工具的使用方式不正确或过度。 设置了多少触发器来在表字段之间进行一些奇特的更新，或者重新计算总计或其他聚合数据？ 使用多少个 SP 来构建用于报告问题的临时表？ 这是开发人员使用这些工具的众多情况中的两种，我认为这通常说明数据库设计/规范化缺陷。
其他一些人承认应该严格控制 SP 和触发器的使用。 我觉得也有必要。
我必须承认，我正试图找到一些支持性的论据，而所有这些在我们其他数据库上工作的 SQL 极客都瞧不起我们，告诉他们的朋友“你知道吗？他们甚至不使用 SP 和触发器！哈哈！”
We have been working on a complex database and client interface for the last 18 months. We are regularly adding new functionnalities to this application, and it is now used by tens of users on a daily basis in all our offices, including sites and overseas. This is just to tell you it is a REAL application with a REAL database.
Until now, we still did not have to write any stored procedures, except on a temporary basis to solve minor issues between client versions and updated database model (where the old client version will not properly update the newly created field, until everybody installs the newest version).
In the same way, we still did not need any triggers. In fact, the only SPs and triggers are the system ones, or the ones added for replication purpose.
I have the strange feeling that SPs and Triggers are mainly used to compensate for database design defaults and/or attempts to bypass database design rules, when developers consider that database optimisation has to oppose database normalisation.
The problem is that these tools are time-consuming (for both development or maintenance). Each developer shall then be very carefull using them, keeping in mind that they are the most "expensive" items to maintain in a database.
Could we consider that having none or few stored procedures / triggers in a database is a good indication of its normalisation level and/or its code maintenance cost?
EDIT:
Some of you have supplied fair arguments for the use of both triggers and SPs. But I keep on thinking that most of the time these tools are used in an improper or excessive way. How many triggers are set to make some fancy updates between table fields, or to recalculate totals or other aggregated data? How many SPs are used to build temporary tables for reporting issues? These are 2 among many situations where developers use these tools, and I think this usually illustrate database design/normalisation flaws.
Some others admit that use of SPs and triggers should be strictly controled. I find it necessary too.
I must confess that I am trying to find some upholding arguments, where all these SQL geeks working on our other databases look down at us, telling their friends "You know what? they do not even use SPs and Triggers! Haha!"
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（10）
这是一个 SP 绝对必要的示例：用户界面只是整个应用程序的一小部分。 当整个过程独立于用户发生时。
例如，我从事的项目涉及来自许多不同来源的大量数据处理。 所以我们收到这些文件，然后我们只需运行一个脚本外壳，它将简单地启动一个 SP 来导入文件中的所有数据，检查它们，操作它们等等......
你猜怎么着？ 用户也可以从用户界面使用同一个 SP，而无需再次重写整个数据处理查询！
当然，如果那些处理查询只是简单的 SELECT，那么你可能会争论 SP 的必要性，但是当你需要 UPDATE 几十个表、计算字段、清除数据、清理数据时，那么 SP 就有福了。 这并不意味着我们的数据库缺乏规范化，但是当您每天处理数十亿数据时，并非一切都会变得简单。
Here is one example where SPs are ABSOLUTELY necessaries : the User Interface is only a small part of the whole application. And when the whole process occurs independantly from the users.
For instance, i work on a project that involves a lot of data processing, from many different sources. So we receive those files, then we just run a Script Shell that will simple launch a SP to import all the data from the files, check them, manipulate them etc...
And guess what? this same SP can be used ALSO by the user, from the user interface, without the need to rewrite the whole data processing queries again !
Of course if those processing queries were just simple SELECT, then you could argue about the necessity of SP, but when you need to UPDATE dozens of tables, calculate fields, purge data, clean data , then SPs are blessed. And its doesnt mean our database lacks of normlization, but when you process billions of data everyday, not everything CAN be simple.
我们有一个我正在工作的程序，我认为它是触发器的一个很好的例子，因为现在有大约 8 个以上不同的版本（一个 API 和许多版本的前端和后端）。 如果我想改变它处理某些东西的方式，如果它在触发器中，而不是必须在 8 个以上不同的代码库中进行完全相同的更改（具有不同级别的意大利面条编码），那么会容易得多和命名不当的变量）。
We have a program where I am working that I think is a good case for triggers as there are now about 8+ different versions floating around (an API and many versions of frontends and backends). If I want to make a change in the way that it processes something, it would have been far easier if it were in a trigger rather than to have to make that exact same change in 8+ different code bases (with varrying levels of spaghetti coding and poorly named variables).
如果您有七个不同的应用程序都与用户数据库通信，那么拥有一个名为“createUser”的存储过程而不是七个不同的应用程序自己构建 INSERT 语句不是更有意义吗？
现在，一个新应用程序必须将用户添加到该数据库，但它有一个新要求，并且需要添加一个新字段，该字段的默认值是从存储在完全不同的第三方应用程序数据库中的值填充的。
现在，您可以更改这七个应用程序以及新的应用程序，以与第 3 方应用程序对话以获取值，同时构建 INSERT 语句。
或者，您可以修改用户数据库的 createUser 过程以从第 3 方数据库中查找数据作为默认值，因此您的其他程序都不需要更改和重新部署，因为它们并不真正关心该值...然而。
或者，您可以在更新用户表时向用户数据库添加触发器，以从第 3 方数据库获取该值。
存储过程还具有可编译的优点，因此比常规语句更快。
存储过程还可以将单个复杂的 sql 语句分解为多个简单的语句，以提高查询运行的速度。
当数据需求发生变化时，修改存储过程比更新数千个应用程序安装要简单得多。
我的 $.02
美元。
我已经很多年没有在应用程序中编写过一行 sql 了。 这一切都在存储过程中。 无论是简单的选择、插入、更新、复杂的报告，还是实际上是应用程序中的单个对象但存储在数据库中 7 个不同表中的更新。
If you have seven different apps all talking to the users database, wouldn't it make more sense having a stored proc called "createUser" rather than seven different applications building that INSERT statement on their own?
And now, a new app, has to add users to this database, but it has a new requirement, and a new field that needs to be added, that's default value is populated from a value stored in a completely different 3rd party application's database.
Now, you cold go change those seven apps, plus the new one, to talk to the 3rd party app to get the value, while building the INSERT statement.
Or, you can modify the users database's createUser proc to lookup the data from the 3rd party database as a default value, so none of your other programs need to be changed and redeployed, since they don't really care about that value...yet.
Or, you can add a trigger to the users database when the users table is updated, to get that value from the 3rd party database.
Stored procedures also have the benefit of being compiled and therefor quicker than a regular statement.
Stored procedures also can break up a single complex sql statement into several simpler statements to increase the speed at which the query runs.
When data requirements change, it's much simpler to modify a stored proc, than to update 1000s of installations of an application.
My $.02
ps.
I haven't written a line of sql in an application in years. It all goes in stored procedures. Be it a simple select, insert, update, a complex report, or an update that is effectively a single object in the application, but stored across 7 different tables in the database.
存储过程和触发器是工具——在数据库管理系统中使用的非常具体的工具。
触发器有多种用途，从极大地简化历史表的维护（其中每一行代表主表的过去一段时间）到将 ETL 请求排队到数据仓库（取决于特定的 RDBMS）
存储过程也有其用武之地，无论它们是从应用程序还是从 SQL 命令行工具调用。
存储过程或触发器的包含实际上与规范化或“数据库设计默认值”没有关系。 它们在应用程序中的使用通常与应用程序的其他要求直接相关，例如可扩展性、可靠性、复制或使用这些工具可以最有效地满足的其他要求。
如果您不需要它们，请不要使用它们。 但是，不要认为触发器或存储过程的存在表明设计不佳。
Stored Procedures and Triggers are tools -- very specific tools for use within a database management system.
Triggers have a number of uses, from greatly simplifying the maintenance of history tables (where each row represents a past period in time for the primary table) to queueing requests for ETL to a data warehouse (depends on the specific RDBMS)
Stored procedures also have their place, whether they're invoked from the application or from SQL command line tools.
Inclusion of Stored Procedures or Triggers really has no bearing on the Normalization or "database design defaults". Their use in applications often relates directly to other requirements of the application, those of scalability, reliability, replication or other requirements that can be most effectively met by using these tools.
If you don't need 'em, don't use 'em. Do not, however, assume that the presence of triggers or stored procedures indicates poor design.
你不能。
规范化和存储过程彼此完全分开。
我对 SP 的看法是数据库和使用它的人之间的一个抽象层。
强迫人们使用 SP 而不是直接的 CRUD 操作将使更改表的设计变得更容易，而不会破坏它们。
No you cannot.
Normalization and stored procedures are completely separate from each other.
My view on SP's is a layer of abstraction between the database and the people using it.
Forcing people to use the SP instead of direct CRUD operations will make it easier to change the design of the tables without breaking them.
没有什么比在代码中遇到大量存在错误的内联 SQL 更让我讨厌的了。 至少使用存储过程，您可以对其进行语法检查，甚至执行它以查看问题可能出在哪里。 更不用说，在保存执行计划时，它比仅在数据库中触发查询要快。 我长期以来一直认为数据库代码属于数据库，但这只是我的观点。
触发器有其用途。 它们并不总是最好的，但肯定是有原因的。
There's nothing I hate more than coming across a huge bunch of in-line SQL in code that's got a bug in it. At least with a Stored Proc you can syntax check it, or even execute it to see where the problem may lie. Not to mention that it would be quicker than just firing queries at the DB as the execution plan is saved. I've long been of the opinion that DB code belongs in the DB, but that's just my opinion.
And triggers have their uses. They aren't always the best thing, but certainly are there for a reason.
至于存储过程，我们不要忘记安全问题。 允许应用程序运行内联 SQL 意味着您的用户帐户需要对所有表进行直接读取、插入、更新和删除访问。 如果出现漏洞，您的数据库就会暴露。
触发器有其用武之地。 特别是在有很多数据库开发人员的环境中，他们可能知道也可能不知道（例如）SOX 要求，我们保留预算信息更改的历史记录。
As for stored procs, let's not forget security issues. To allow an application to run inline SQL means that your user account needs direct read, insert, update, and delete access to all of the tables. If there is ever a breach, your database is exposed.
Triggers have their place. Especially in an environment where there are a lot of database developers who may or may not know (for example) the SOX requirements that we keep a history of changes to budget info.
不会。存储过程和触发器有多种不同的使用方式。 这取决于环境、开发人员等。例如，存储过程经常被用作安全机制。
我认为唯一适合使用触发器的地方是重构数据库时。 所以也许你在这一点上有所了解。 但其他人可能会以其他方式使用它们。
No. Stored procedures and triggers are used in many different ways. It depends on the circumstances, the developers, etc. For example, stored procedures are often used as a security mechanism.
The only place I've seen fit to use a trigger is when refactoring the database. So maybe you are on to something with that point. But other folks might use them in other ways.
如何从数据库取回数据？ 你构建 SQL 字符串并执行它们吗？ 如果是这样，您如何验证这些条目不会破坏数据库？ 存储过程有助于大大降低这种风险，因为服务器将文本作为文本而不是命令来处理。
存储过程通常比对数据库执行 SQL 字符串要快得多，这也意味着您不必为不同的信息组编写不同的选择，因为这一切都可以由存储过程完成。 从程序中抽象数据库的能力也是一个被多次提出的好处。
最后，我实际上只使用触发器进行数据库审计（在 SQL2005 之前没有内置的审计功能），它将使用每次更改的先前值和新值更新表。
规范化和优化与存储过程或触发器无关，规范化和优化可能会影响您需要抽象数据库的程度，但在我看来，每次更改数据库时都必须重构代码，这比使用存储过程要糟糕得多
How do you get data back from the database? do you build SQL Strings and execute them? If so how do you validate the entries arent going to destroy the database? Stored procs help reduce the risk of this greatly just by virtue of the fact that the text is handled by the server as text and not as a command.
Stored procedures normally work much faster than executing SQL Strings against the database, it also means you dont have to write different selects for different groups of information as it can all be done by the stored proc. The ability to abstract the database from the program is also a benefit thats been raised a few times.
Lastly, I've really only used triggers for database auditing (before SQL2005 there was no in built auditing functionality) which would update tables with prev and new values of each change.
Normalisation and optimisation have nothing to do with stored procs or triggers, Normalisation and optimisation may affect how much you need to abstract your database but having to refactor your code each time you make a database change would in my opinion be much worse than using stored procs
“设置了多少触发器来在表字段之间进行一些奇特的更新，或者重新计算总计或其他聚合数据？”
使用触发器根据业务规则进行复杂的更新并不是一个缺陷。 这是首选方法。 如果您想维护数据完整性，则应在数据库级别强制执行所有业务规则。 除了用户界面之外，还有其他方法可以影响数据库中的数据，并且无论使用哪种方法，业务规则都应该适用。 这样导入的数据将必须遵循规则，新功能将必须遵循规则（而不是必须记住有规则并找到您构建的功能来强制执行它），人们从查询工具批量更新数据（ 则
如果您没有单独的报告数据库， 通常会为了报告速度而重新计算总计。 当财务部门运行需要数小时才能运行的季度报告时，您是否希望减慢或锁定整个数据库，因为他们必须根据数百万条记录计算总计？ 或者您宁愿对数据进行每次更改都多花一秒钟的时间？ 这通常是仅在数据库变得很大并且在它变得足够大以证明拥有单独的报告数据库的成本合理之前使用的方法。 因此，是的，这是一种临时的权宜之计，但当您从原始设计转向新设计时，这对于保持业务运行可能是非常必要的（构建 OLAP 需要相当长的时间和一套不同的技能）数据库）。
"How many triggers are set to make some fancy updates between table fields, or to recalculate totals or other aggregated data?"
Using a trigger to do complex updates based on business rules is not a flaw. It is the preferred method. All business rules should be enforced at the database level if you want to maintain data integrity. There are ways other than the user interface to affect the data in the database and business rules should apply no matter the method used. That way imported data will have to follow the rules, new functionality will have to follow the rules (instead of having to remember that there are rules and find the functionality you built to enforce it), people updating data in bulk from the query tool (think raise all prices by 10%) will have to follow the rules, etc.
Recalculating totals tends to be done for speed of reporting if you do not have a separate reporting database. Do you want to slow down or lock the entire database when finance runs their quarterly reports that take hours to run because they have to calculate totals against millions of records? Or would you rather make each change to the data take a second longer? This is generally a method only used when a database gets large and before it gets large enough to cost justify having a separate reporting database. AS such, yes it is a temporary expedient, but one which can be quite necessary to keep the business running as you move from the original design to the the new one (it takes quite some time and a different set of skills to build an OLAP database).