Triggers get invoked automatically when the event specified in it gets fulfilled, it can't return any value.So user have no control over it's invocation.Whereas stored procedure can be called as per application requirement and can get output parameters.
1. trigger doesnot except argument but store procedure does. 2. trigger cannot be local and store as a stand alone object in database. but store procedure can be store in package. 3. Trigger execute implicitly whereas store procedure execute via procedure call from another block.
Stored procedures are used to perform a task and should be invoked explicitly using exec. Triggers are used to restrict invalid transactions and invokes(fires) implicitly when an dml manipulation happens
A Stored Procedure is set of pre-compiled sql statements which are stored in memory. These statements are executed by executing that procedure which display results based on arguments passed to that procedure.
A trigger is invoked automatically when any operation (insertion,deletion and updation) is performed on particular table.Triggers are used to record events performed on table.
Triggers are stored procedures created in order to enforce integrity rules in a database. A trigger is executed every time a data-modification operation occurs (i.e., insert, update or delete). Triggers are executed automatically on occurance of one of the data-modification operations. A trigger is a database object directly associated with a particular table. It fires whenever a specific statement/type of statement is issued against that table. The types of statements are insert,update,delete and query statements. Basically, trigger is a set of SQL statements A trigger is a solution to the restrictions of a constraint. For instance: 1.A database column cannot carry PSEUDO columns as criteria where a trigger can. 2. A database constraint cannot refer old and new values for a row where a trigger can.
1> For executing Sp we have to call Stored Procedure. But in case of Trigger it is call whenever there is an action taken on table or column(Insert, Update ,Delete). 2> From SP we pass Values But from Trigger we cant. 3> stored procedure returns value but trigger cannot returns value. 4> For SP we have to compile. for Trigger no need to compile. 5> we return SP in Trigger but Trigger can not be in SP.