Last post Jun 20, 2013 08:21 AM by sujithkumar
Jun 19, 2013 11:53 AM|Shivarajb|LINK
Hai all, I have a doubt with triggers,
What triggers will do is will perform some action after or before DML operation,Insted of triggers why can't we do with Stored procedures?.
any demerits with SP's?
Please correct me.
Jun 19, 2013 11:58 AM|Sujeet Saste|LINK
You can not use SP's instead of Triggers, because Triggers fired at Sql Server itself and to perform any operation by using SP you need to call that SP from code.
Triggers get called automatically on certain operation in table.
Its not demerit of SP it is normal behaviour of SP.
Refer following link for detailed description : http://msdn.microsoft.com/en-us/library/ms189550(v=sql.105).aspx
Jun 19, 2013 12:00 PM|sujithkumar|LINK
1) 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.
2) 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 action (insert, delete and update) defined within a trigger can initiate
the execution of another trigger defined on the same table or different table.
3) Stored procedures can be scheduled through a job to execute on a predefined time, but we can't schedule a trigger.
4) Stored procedure can take the input parameters, but we can't pass the parameters as an input to a trigger.
5) Stored procedures can return values but a trigger cannot return a value.
6) We can use the Print commands inside the stored procedure to debug purpose but we can't use the print command inside a trigger.
7) We can use the transaction statements like begin transaction, commit transaction and rollback inside a stored procedure but we can't use the transaction statements inside a trigger.
8) We can call a stored procedure from front end (.asp files, .aspx files, .ascx files etc.) but we can't call a trigger from these files.
Jun 19, 2013 04:33 PM|ramiramilu|LINK
any demerits with SP's?
no not at all (atleast to best of my knowledge), but you end up doing everything manually....like getting the contextual information of the row, you have to feed to the sp...
Jun 19, 2013 04:54 PM|anand.csharp|LINK
Please see these link , You will get ans of your all questions
Jun 20, 2013 05:26 AM|Shivarajb|LINK
Does it possible to do what trigger will do with SP's?
Jun 20, 2013 08:21 AM|sujithkumar|LINK
A SP can also do what the trigger performs, But you need to do it explicitly by calling the stored procedure...