Stored Procedure In SQL

Question

Hi Friends...... I want to known that when we use stored procedure and it is same as trigger or not? thanks......

Satyapriya Nayak · Answer

Hi Vikas Normal 0 Stored procedures:- Its nothing but a set of T-SQL statements combined to Perform a single task of several tasks. Its basically like a Macro so when you invoke the Stored Procedure you actually run a set of statements. Stored Procedure are the precompiled set of sql command. Stored procedures means containing a precompiled block of code. if we call stored procedures they need not compiled only execution takes place. With this advantage work on database is less. Triggers: - A set of PLSQL statements automatically executed when DML Statements are performed and stored permanently in database.It is used to impose user defined restrictions or business rules on database table. Difference between Stored Procedures and Triggers Stored Procedures are called by the programmer wherever it Wants to fire but triggers fired automatically when insert delete and update occurred Stored procedure normally used for performing tasks But the Trigger normally used for tracing and auditing logs. Stored procedures should be called explicitly by the user in order to execute But the Trigger should be called implicitly based on the events defined in the table. Stored Procedure can run independently. But the Trigger should be part of any DML events on the table. Stored procedure can be executed from the Trigger. But the Trigger cannot be executed from the Stored procedures. Stored Procedures can have parameters. But the Trigger cannot have any parameters. Refer http://docs.oracle.com/cd/E12151_01/doc.150/e12155/triggers_proc_mysql.htm Thanks

Vikas Mishra · Answer

Thanks.........

Jean Paul · Answer

Stored Procedures are used to improve the performance in case: - To reduce the roundtrip to database by the application Stored Procedure can have LOOPs Conditions SELECT statements DATA processing and makes it optimized to move the application logic to database tier. But manageability and debugging induce higher costs. In SQL Server STORED PROCEDURES are not PRE-COMPILED. (ONLY UPTO 6.5 it was pre-compiled) so there won't be much performance advantage in the case of normal queries. http://forloveofsoftware.blogspot.com/2007/12/are-ms-sql-server-stored-procesures.html Trigger differs from Stored Procedure as: - Trigger invoked by an operation like insert/update/delete - Stored Procedure invoked directly by the application