Trigger 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.
Stored procedured are used for update, select, insert and delete purpose while triggers are used for event handling on table.
Trigger-A trigger is a special kind of a store procedure that executes in response to certain action on the table like insertion, deletion or updation of data. It is a database object which is bound to a table and is executed automatically. Store procedures- Stored Procedure in SQL Server can be defined as the set of logical group of SQL statements which are grouped to perform a specific task.
1. Stored Procedure can be run independentlyThe triggers executes based on table events2. The Stored Procedures are used for performing tasksThe Triggers for auditing work3. The Stored Procedures can have the parametersThe Triggers cannot have any parameters4. The Stored Procedure cannot call triggersThe triggers can call Stored Procedures
stored procedures are used for insert,update,delete purposes while triggers are used for event handling purposes
The trigger is fire after an insert ,update and delete while a stored procedure is server side program that is run when you invoke it
stored procedures is call and run but triggers fire on table insert update & delete action effect . this is main diffrent
Triggers are called automatically while stored procedure gets called explicitly.
stored procedures and triggers are both precompiled but the difference is procedures are callled explicitly by using exec whereas triggers are called implicitly
Store Procedure:- A store Procedure call manually and we can pass parameters or with parameters. In Store Procedure have a out parameter. A store Procedure call another procedure. Triggers: Not give any type of functionality.
Stored Procedures:- A Stored procedures is an action that has to be performed that is one's we defined a procedures & calls it its executes the logic that defined the under procedure.A procedures is exactly same as a method we defined under programming languages.Triggers:- A trigger is a special kind of stored procedure which doesn't required to be call for execution .Behavior wise triggers are similar to constraints which can be use for imposing business rules where is a constraint is pre-defined business rules & triggers is user defined business rules.
1) Stored Procedure is predefined collection of sql statements. 2) Stored Procedure is Precompiled Execution. 3) Trigger is not Precompiled Exectution. 4) Trigger is not Reduce Client / Server Network Traffic. 5) Trigger Execute Automatic Execution Process. Stored Procedure is not automatic Procedure process.
Triggers executes automatically when event fires on table. Stored Procedure can be run manually.