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.
在过去的18个月里,我们一直在研究复杂的数据库和客户端界面。我们定期为此应用程序添加新的功能,现在我们所有办公室(包括站点和海外)每天都有数十名用户使用它。这只是告诉你它是一个真正的应用程序与REAL数据库。
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.
同样,我们仍然不需要任何触发器。实际上,唯一的SP和触发器是系统的,或者是为复制目的而添加的。
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.
我有一种奇怪的感觉,当开发人员认为数据库优化必须反对数据库规范化时,SP和触发器主要用于补偿数据库设计默认值和/或试图绕过数据库设计规则。
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.
有些人为使用触发器和SP提供了公平的参数。但我一直认为,大多数时候这些工具是以不正当或过度的方式使用的。设置了多少个触发器来在表字段之间进行一些奇特的更新,或者重新计算总计或其他聚合数据?有多少SP用于构建临时表以报告问题?这些是开发人员使用这些工具的许多情况中的两个,我认为这通常说明了数据库设计/规范化缺陷。
Some others admit that use of SPs and triggers should be strictly controled. I find it necessary too.
其他一些人承认应严格控制SP和触发器的使用。我发现它也是必要的。
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!"
我必须承认,我正试图找到一些坚持不懈的论点,所有这些在我们的其他数据库上工作的SQL极客都看不起我们,告诉他们的朋友“你知道吗?他们甚至不使用SP和触发器!哈哈!”
10 个解决方案
#1
12
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)
触发器具有多种用途,从大大简化历史表的维护(其中每一行代表主表的过去时间段)到将ETL的请求排队到数据仓库(取决于特定的RDBMS)
Stored procedures also have their place, whether they're invoked from the application or from SQL command line tools.
无论是从应用程序还是从SQL命令行工具调用存储过程,它们都有其自己的位置。
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.
如果你不需要他们,请不要使用他们。但是,不要认为触发器或存储过程的存在表明设计不佳。
#2
12
Could we consider that having none or few stored procedures / triggers in a database is a good indication of its normalization level and/or its code maintenance cost?
我们是否可以认为数据库中没有或很少存储过程/触发器是其正常化级别和/或代码维护成本的良好指示?
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.
我对SP的看法是数据库和使用它的人之间的抽象层。
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.
强制人们使用SP而不是直接CRUD操作将更容易更改表的设计而不会破坏它们。
#3
5
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.
除了在代码中遇到大量的内联SQL之外,没有什么比这更容易了。至少使用Stored Proc,您可以对其进行语法检查,甚至执行它以查看问题所在。更不用说它会比保存执行计划时在DB上发出查询更快。我一直认为数据库代码属于数据库,但这只是我的看法。
And triggers have their uses. They aren't always the best thing, but certainly are there for a reason.
触发器有它们的用途。它们并不总是最好的,但肯定是有原因的。
#4
5
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.
至于存储过程,我们不要忘记安全问题。允许应用程序运行内联SQL意味着您的用户帐户需要直接读取,插入,更新和删除对所有表的访问权限。如果存在违规行为,则会泄露您的数据库。
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.
触发器有它们的位置。特别是在有许多数据库开发人员可能会或可能不知道(例如)SOX要求的环境中,我们会保留预算信息变更的历史记录。
#5
2
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.
我认为唯一适合使用触发器的地方是重构数据库。所以也许你正在谈论这一点。但其他人可能会以其他方式使用它们。
#6
2
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.
你如何从数据库中获取数据?你构建SQL字符串并执行它们?如果是这样,您如何验证条目是否会破坏数据库?存储过程有助于大大降低风险,因为文本由服务器作为文本而不是作为命令处理。
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.
存储过程通常比对数据库执行SQL字符串要快得多,这也意味着您不必为不同的信息组编写不同的选择,因为它可以由存储的proc完成。从程序中抽象数据库的能力也是一些已经提出的好处。
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.
最后,我实际上只使用触发器进行数据库审计(在SQL2005之前没有内置的审计功能),这将使用每个更改的prev和new值更新表。
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
规范化和优化与存储过程或触发器无关,规范化和优化可能会影响您抽象数据库所需的数量,但每次进行数据库更改时必须重构代码,在我看来比使用存储过程更糟糕
#7
2
"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.
使用触发器根据业务规则执行复杂更新不是一个缺陷。这是首选方法。如果要保持数据完整性,应在数据库级别强制执行所有业务规则。除了用户界面之外,还有其他方法可以影响数据库中的数据,无论使用何种方法,业务规则都应该适用。这样导入的数据必须遵循规则,新功能必须遵循规则(而不是必须记住有规则并找到您为强制执行而构建的功能),人们从查询工具批量更新数据(认为将所有价格提高10%)必须遵守规则等。
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).
如果您没有单独的报告数据库,则可以重新计算报告的速度。当财务运行需要数小时运行的季度报告时,您是想减慢或锁定整个数据库,因为他们必须计算数百万条记录的总数?或者您是希望每次更改数据需要更长时间?这通常是一种方法,仅在数据库变大并且在变得足够大以至于具有单独的报告数据库的成本合理之前使用。因此,是的,这是一个临时的权宜之计,但是当你从原始设计转移到新设计时,保持业务运行是非常必要的(构建OLAP需要相当长的一段时间和不同的技能)数据库)。
#8
1
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 !
这是一个SP绝对必需的例子:用户界面只是整个应用程序的一小部分。当整个过程独立于用户发生时。例如,我从一个涉及大量数据处理的项目,从许多不同的来源。所以我们接收这些文件,然后我们只运行一个脚本Shell,它将简单地启动一个SP来导入文件中的所有数据,检查它们,操纵它们等等......猜猜是什么?用户也可以从用户界面使用同样的SP,而无需再次重写整个数据处理查询!
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.
当然,如果那些处理查询只是简单的SELECT,那么你可以争论SP的必要性,但是当你需要更新几十个表,计算字段,清除数据,清理数据时,那么SP就很幸运了。它并不意味着我们的数据库缺乏规范化,但是当你每天处理数十亿个数据时,并非一切都很简单。
#9
1
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).
我们有一个我正在工作的程序,我认为这是触发器的一个很好的例子,因为现在有大约8个不同版本(API和许多版本的前端和后端)。如果我想改变它处理某些东西的方式,那么如果它是在触发器中而不是必须在8个以上不同的代码库中进行完全相同的更改(具有不同级别的意大利面条编码)会更容易和命名不好的变量)。
#10
0
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?
如果你有七个不同的应用程序都在与用户数据库交谈,那么使用名为“createUser”的存储过程而不是七个不同的应用程序自己构建INSERT语句会不会更有意义?
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.
现在,你冷静地改变那七个应用程序,加上新的应用程序,与第三方应用程序交谈以获得值,同时构建INSERT语句。
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.
或者,您可以修改用户数据库的createUser proc以查找来自第三方数据库的数据作为默认值,因此您的其他程序都不需要更改和重新部署,因为它们并不真正关心该值...然而。
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.
或者,您可以在更新users表时向用户数据库添加触发器,以从第三方数据库中获取该值。
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.
存储过程还可以将单个复杂的sql语句拆分为几个更简单的语句,以提高查询运行的速度。
When data requirements change, it's much simpler to modify a stored proc, than to update 1000s of installations of an application.
当数据需求发生变化时,修改存储过程要比更新应用程序的1000个安装简单得多。
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.
PS。多年来我没有在应用程序中编写一行sql。这一切都在存储过程中。它是一个简单的选择,插入,更新,复杂报告或更新,它实际上是应用程序中的单个对象,但存储在数据库中的7个不同表中。
#1
12
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)
触发器具有多种用途,从大大简化历史表的维护(其中每一行代表主表的过去时间段)到将ETL的请求排队到数据仓库(取决于特定的RDBMS)
Stored procedures also have their place, whether they're invoked from the application or from SQL command line tools.
无论是从应用程序还是从SQL命令行工具调用存储过程,它们都有其自己的位置。
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.
如果你不需要他们,请不要使用他们。但是,不要认为触发器或存储过程的存在表明设计不佳。
#2
12
Could we consider that having none or few stored procedures / triggers in a database is a good indication of its normalization level and/or its code maintenance cost?
我们是否可以认为数据库中没有或很少存储过程/触发器是其正常化级别和/或代码维护成本的良好指示?
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.
我对SP的看法是数据库和使用它的人之间的抽象层。
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.
强制人们使用SP而不是直接CRUD操作将更容易更改表的设计而不会破坏它们。
#3
5
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.
除了在代码中遇到大量的内联SQL之外,没有什么比这更容易了。至少使用Stored Proc,您可以对其进行语法检查,甚至执行它以查看问题所在。更不用说它会比保存执行计划时在DB上发出查询更快。我一直认为数据库代码属于数据库,但这只是我的看法。
And triggers have their uses. They aren't always the best thing, but certainly are there for a reason.
触发器有它们的用途。它们并不总是最好的,但肯定是有原因的。
#4
5
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.
至于存储过程,我们不要忘记安全问题。允许应用程序运行内联SQL意味着您的用户帐户需要直接读取,插入,更新和删除对所有表的访问权限。如果存在违规行为,则会泄露您的数据库。
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.
触发器有它们的位置。特别是在有许多数据库开发人员可能会或可能不知道(例如)SOX要求的环境中,我们会保留预算信息变更的历史记录。
#5
2
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.
我认为唯一适合使用触发器的地方是重构数据库。所以也许你正在谈论这一点。但其他人可能会以其他方式使用它们。
#6
2
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.
你如何从数据库中获取数据?你构建SQL字符串并执行它们?如果是这样,您如何验证条目是否会破坏数据库?存储过程有助于大大降低风险,因为文本由服务器作为文本而不是作为命令处理。
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.
存储过程通常比对数据库执行SQL字符串要快得多,这也意味着您不必为不同的信息组编写不同的选择,因为它可以由存储的proc完成。从程序中抽象数据库的能力也是一些已经提出的好处。
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.
最后,我实际上只使用触发器进行数据库审计(在SQL2005之前没有内置的审计功能),这将使用每个更改的prev和new值更新表。
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
规范化和优化与存储过程或触发器无关,规范化和优化可能会影响您抽象数据库所需的数量,但每次进行数据库更改时必须重构代码,在我看来比使用存储过程更糟糕
#7
2
"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.
使用触发器根据业务规则执行复杂更新不是一个缺陷。这是首选方法。如果要保持数据完整性,应在数据库级别强制执行所有业务规则。除了用户界面之外,还有其他方法可以影响数据库中的数据,无论使用何种方法,业务规则都应该适用。这样导入的数据必须遵循规则,新功能必须遵循规则(而不是必须记住有规则并找到您为强制执行而构建的功能),人们从查询工具批量更新数据(认为将所有价格提高10%)必须遵守规则等。
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).
如果您没有单独的报告数据库,则可以重新计算报告的速度。当财务运行需要数小时运行的季度报告时,您是想减慢或锁定整个数据库,因为他们必须计算数百万条记录的总数?或者您是希望每次更改数据需要更长时间?这通常是一种方法,仅在数据库变大并且在变得足够大以至于具有单独的报告数据库的成本合理之前使用。因此,是的,这是一个临时的权宜之计,但是当你从原始设计转移到新设计时,保持业务运行是非常必要的(构建OLAP需要相当长的一段时间和不同的技能)数据库)。
#8
1
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 !
这是一个SP绝对必需的例子:用户界面只是整个应用程序的一小部分。当整个过程独立于用户发生时。例如,我从一个涉及大量数据处理的项目,从许多不同的来源。所以我们接收这些文件,然后我们只运行一个脚本Shell,它将简单地启动一个SP来导入文件中的所有数据,检查它们,操纵它们等等......猜猜是什么?用户也可以从用户界面使用同样的SP,而无需再次重写整个数据处理查询!
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.
当然,如果那些处理查询只是简单的SELECT,那么你可以争论SP的必要性,但是当你需要更新几十个表,计算字段,清除数据,清理数据时,那么SP就很幸运了。它并不意味着我们的数据库缺乏规范化,但是当你每天处理数十亿个数据时,并非一切都很简单。
#9
1
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).
我们有一个我正在工作的程序,我认为这是触发器的一个很好的例子,因为现在有大约8个不同版本(API和许多版本的前端和后端)。如果我想改变它处理某些东西的方式,那么如果它是在触发器中而不是必须在8个以上不同的代码库中进行完全相同的更改(具有不同级别的意大利面条编码)会更容易和命名不好的变量)。
#10
0
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?
如果你有七个不同的应用程序都在与用户数据库交谈,那么使用名为“createUser”的存储过程而不是七个不同的应用程序自己构建INSERT语句会不会更有意义?
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.
现在,你冷静地改变那七个应用程序,加上新的应用程序,与第三方应用程序交谈以获得值,同时构建INSERT语句。
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.
或者,您可以修改用户数据库的createUser proc以查找来自第三方数据库的数据作为默认值,因此您的其他程序都不需要更改和重新部署,因为它们并不真正关心该值...然而。
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.
或者,您可以在更新users表时向用户数据库添加触发器,以从第三方数据库中获取该值。
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.
存储过程还可以将单个复杂的sql语句拆分为几个更简单的语句,以提高查询运行的速度。
When data requirements change, it's much simpler to modify a stored proc, than to update 1000s of installations of an application.
当数据需求发生变化时,修改存储过程要比更新应用程序的1000个安装简单得多。
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.
PS。多年来我没有在应用程序中编写一行sql。这一切都在存储过程中。它是一个简单的选择,插入,更新,复杂报告或更新,它实际上是应用程序中的单个对象,但存储在数据库中的7个不同表中。