首页 > 代码库 > Trigger VS Procedure
Trigger VS Procedure
今天,碰到一个Procedure的问题,刚好一个同事提出了一个疑问,为什么不用Trigger,Trigger这么好用。
第一反应,这个问题很奇怪,通常只把Procedure与在APP中执行的SQL文作区别。个人脑海里,Trigger是
用于触发事件用,与某个表的相关动作进行关联。
从各个角度,似乎关系不大,但是又有所关联,扯不开关系。
纯理论上考虑,二者的区别可以简单描述如下:
1.Trigger是被绑定到某个具体的Table的更新,删除,插入的动作中;而Procedure则是DataBase中的一个
公用方法, 可以被任意调用。
2.Trigger的管理相对隐蔽,而Procedure则相当明了直接。后期维护管理上,显然Procedure更容易些。
一旦Trigger被绑定,无论是后期维护认为更改对象数据,也将触发关联Trigger。产生一些意想不到的问题。
很多人,基本设计者基本不考虑采用Trigger。
综合看来,Procedure用途显然更广泛也更受欢迎,但为什么微软要增加Trigger的功能?笔者认为这是一个
思维模式,和编成相通。可以说微软的偏好。
如在C#中,我们既可以用共通方法(Public Method),也可用通过共通事件 (EVENT)来处理相关。而者
有区别,也有共通点。如何理解二者的区别似乎也可对等的看待Trigger和Procedure的区别。
- We can execute a stored procedure whenever we want with the help of the
exec
command, but a trigger can only be executed whenever an event (insert, delete, and update) is fired on the table on which the trigger is defined. - We can call a stored procedure from inside another stored procedure but we can‘t directly call another trigger within a trigger. We can only achieve nesting of triggers in which the action (insert, delete, and update) defined within a trigger can initiate execution of another trigger defined on the same table or a different table.
- Stored procedures can be scheduled through a job to execute on a predefined time, but we can‘t schedule a trigger.
- Stored procedure can take input parameters, but we can‘t pass parameters as input to a trigger.
- Stored procedures can return values but a trigger cannot return a value.
- We can use Print commands inside a stored procedure for debugging purposes but we can‘t use print commands inside a trigger.
- We can use transaction statements like begin transaction, commit transaction, and rollback inside a stored procedure but we can‘t use transaction statements inside a trigger.
- We can call a stored procedure from the front end (.asp files, .aspx files, .ascx files, etc.) but we can‘t call a trigger from these files.
- Stored procedures are used for performing tasks. Stored procedures are normally used for performing user specified tasks. They can have parameters and return multiple results sets.
- The Triggers for auditing work: Triggers normally are used for auditing work. They can be used to trace the activities of table events.
Trigger VS Procedure
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。