触发器中的动态SQL [英] Dynamic SQL in trigger

问题描述

是否可以在触发器内允许动态SQL?

如果不允许，是否有办法允许它?


提前thnx

Is it possible to allow dynamic SQL inside a trigger or not ?

if it is not allowed, is there a way to allow it ?


thnx in advance

推荐答案

检查此页:

http://dev.mysql.com/doc/refman/5.0/zh_cn/sql-syntax-prepared-statements.html [ ^ ]

Check this page:

http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html[^]

我非常确定您可以在触发器中做任何您想做的事情(大部分情况下).但我要首先警告使用触发器.它们往往会浪费性能，有时在调试复杂的数据库时会引起混乱.

对于动态SQL，如果要使用它，它们也可能是性能，安全性和维护方面的问题.由于它们是在每次执行时实时生成的，因此SQL引擎能够保存其执行计划将不会有任何好处.其次，除非您对如何使用输入非常小心，否则动态SQL可能会为SQL注入打开大门.最后，由于要查看编写代码的代码，因此调试起来要困难得多.

您可以执行此操作，但是只需确保您已意识到决策将对其周围的系统产生的潜在影响.在踏上那条路之前，我将确保甚至需要触发器(甚至是动态SQL).我个人将系统锁定为仅使用存储过程，并通过触发器使它们执行动态代码".

但是，如果需要的话，以下链接可能会帮助 [ ^ ].

I''m fairly certain that you can do whatever you want inside triggers (for the most part). But I would caution using triggers in the first place. They tend to be performance hogs and can sometimes lead to confusion when debugging complex databases.

As for the dynamic SQL, if you''re going to use it, they are also potential performance, security, and maintenance issues. Because they are generated live each execution, you won''t have any benefits from the SQL engine being able to save its execution plan. Secondly, dynamic SQL opens you up potentially for SQL injection unless you''re careful about how you use your inputs. Finally, it''ll be that much harder to debug because you''ll be looking at code writing code.

You can do it, but just make sure you''re aware of the potential influences that decision will have on the system around it. I would make sure a trigger (and even dynamic SQL) is even necessary before embarking down that road. I would personally lock down the system to only use stored procedures and have them execute the ''dynamic code'' over using a trigger.

But, if you must, here is a link that might help[^].

这篇关于触发器中的动态SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！