Difference Between Stored Procedures and Triggers in SQL Server

Question

Hi friends I want to know what is difference between stored procedures and triggers in SQL Server ? Thank you.

jaya kumar · Answer

Hai Vipendra Verma, am add some more point's 1.trigger cannot be local and store as a stand alone object in database.but store procedure can be store in package. 2.Trigger can call the specific Stored procedure in it but the reverse is not possible. thanks

Senthilkumar · Answer

Hi, The stored procedures used for performing task The stored procedures normally used to performing user specified tasks. It can have the parameters. It can return multiple results set. The Triggers for auditing work The triggers normally used for auditing work. It can be used to trace the activities of the table events. The stored procedures can have the parameters The procedures can have the input and output parameters with all the data types available in the sql server as well as user defined data types. The Triggers cannot have any parameters The triggers cannot have any parameters. The stored procedure can be run independently The stored procedures can be run independently .It stores as a database object. It can be called from the application. The triggers executes based on table events The DML triggers are get executed based on the table events defined on the particular table. There are different types of triggers like DML triggers, DDL triggers (from 2005 onwards) and logon triggers (from 2008 onwards). The DDL triggers can control the stored procedures creation, drop, ect., The stored procedure cannot call triggers The stored procedures cannot call the triggers directly. But when we do the DML operations on the table, if the corresponding table has the trigger then that time it will get trigger. The triggers can call stored procedures The triggers can call the stored procedures. For more info: http://www.c-sharpcorner.com/Blogs/7852/difference-between-the-stored-procedures-and-trigger.aspx

Satyapriya Nayak · Answer

Hi Vipender, 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. Please refer the below link http://www.c-sharpcorner.com/blogs/7852/difference-between-the-stored-procedures-and-trigger.aspx http://beyondrelational.com/modules/17/interview-questions/238/interview-questions/7056/difference-between-stored-procedure-and-trigger-in-sql-server.aspx Thanks

Difference Between Stored Procedures and Triggers in SQL Server

Answers (3)