哪个是最佳触发器或存储过程? [英] which is best trigger or stored procedure?

问题描述

你好，

据我所知，在触发器中，我们对表中的数据更改执行操作.
我想问一下我是否正在使用存储过程并从表中执行删除操作，然后是否要从其他表中删除记录，为此我可以在存储过程中再编写一个Sql查询以执行操作.

为什么我们需要触发器?触发器有什么用?

请给我答案并作解释.

Hello,

As I know that in triggers we perform an action on change of data in table.
I want to ask that if i am using stored procedure and perform delete from a table and then if i want to delete records from other table, for this i can write one more Sql query in stored procedure to perform action.

Why we need triggers? what is the use of triggers?

please give me answer with explanation.

thanks in advance.

推荐答案

A ^ ]在您调用它并 ^ ]都会执行一些操作(通常是更新或插入).
因此，假设您有一个包含三个字段ID，Name 和Counter的Table .现在，每次Name 都已更新，您想将Counter 加1.您可以为此编写一个Stored Procedure，但是要进行更新，您将始终需要调用Stored Procedure.有人不会这样做，而您的Counter 现在正在后面.如果为此编写了Trigger ，则始终会对其进行更新(无论您是从代码中更改了Name ，还是手动更改了Stored Procedure！
当然，使用Triggers 有其缺点.我总是发现它是一个黑匣子"，我做了一些更新声明，结果与我预期的不同！经过数小时的调试，我发现一个Trigger 会更改我的数据.另外，如果您不小心使用Triggers ，它们可能会无休止地触发(并导致和Exception).在上面的示例中，如果您的触发器意外地观看了整行，该怎么办? Name 将被更新，然后Counter.再次Counter 触发Trigger ，它将更新Counter etc ... :)
我最近在Triggers 上经历过，它锁定了整个Database Tables！
另外，触发Trigger 的任何更新当然都将花费一些时间，因为Trigger 必须执行.

我并不是说Triggers 不好，但是您必须知道何时以及如何使用它们.最好谨慎使用它们！因此，这不是最好"的问题，而是您需要什么.
您必须确定特定案例所需的方法.
祝您好运:)

A Stored Procedure[^] does some stuff (like select, update, delete, make calculations) when you call it and Trigger[^] does some stuff (usually updates or inserts) whenever it is automatically triggered.
So assume you have a Table with three fields ID, Name and Counter. Now EVERYTIME Name is updated you want to increment Counter by one. You could write a Stored Procedure for this, but for an update you would always need to call the Stored Procedure. Someone WILL not do this though and your Counter is now running behind. If you write a Trigger for this Counter is ALWAYS updated (wether you change Name from code, a Stored Procedure or even manually!
Of course the use of Triggers has its downsides. I always find it to be a ''black box'', I do some update statement and the result is different then I expected! After hours of debugging I find a Trigger that alters my data. Also, if you''re not careful with Triggers they could trigger around endlessly (and cause and Exception). In the above example what if your trigger accidentally watched the entire row? Name will be updated then Counter. Counter again triggers the Trigger which will update Counter etc... :)
I recently had an experience with Triggers that locked entire Database Tables!
Also, any update that triggers a Trigger will, of course, take a little longer because the Trigger has to execute.

I am not saying Triggers are bad, but you have to know when and how to use them. Best use them with extreme caution! So this is not a question of ''which is best'', but what do you need.
You have to decide which approach your specific case needs.
Good luck :)

触发器和过程之间的区别简而言之:

• 触发器根据修改情况自动触发
• 过程根据请求执行

The difference between triggers and procedures in short:

• triggers fire autonomously upon modifications
• procedures are executed based on a request

这篇关于哪个是最佳触发器或存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！