differences between stored procedures and triggers in databases?

Question

in databases, stored procedures and triggers function for same cause, what is the precise difference between them

Answer 1

Stored procedures are compiled collection of programs or SQL statements that live in the database. A stored procedure can access and modify data present in many tables. Also a stored procedure is not associated with any particular database object. But triggers are event-driven special procedures which are attached to a specific database object say a table. Stored procedures are not automatically run and they have to be called explicitly by the user. But triggers get executed when the particular event associated with the event gets fired. For example in case of a database having say 200 users and the last modified timestamp need to be updated every time the database is accessed and changed. To ensure this one may have a trigger in the insert or update event. So that whenever any insert or update event of the table gets fired the corresponding trigger gets activated and updates the last modified timestamp column or field with the current time. Thus the main difference between stored procedure and trigger is that in case of stored procedure the program logic is executed on the database server explicitly under eth user’s request but in case of triggers event-driven procedures attached to database object namely table gets fired automatically when the event gets fired.

Answer 2

Stored Procedure And Function Difference

Answer 3

A stored procedure (and function) is a program block that can be called upon to perform it's function.



A trigger "fires" (is run) upon a certain action taken on a table and or column. It can contain it's own procedure or call a stored procedure within the database.



For example, whenever an employee table record's salary changes (update), a trigger can be set up to call a procedure to print out a salary change notification.

Answer 4

Hello,

This is the complete answer.

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 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.



3)Stored procedures can be scheduled through a job to execute on a predefined time, but we can't schedule a trigger.



3)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.



4)We can use Print commands inside a stored procedure for debugging purposes but we can't use print commands inside a trigger.



5)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.



6)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.



7)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.



8)The Triggers for auditing work: Triggers normally are used for auditing work. They can be used to trace the activities of table events.



find the attach image.