Oracle
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.
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.
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.
| The main difference lies in how these 2 are executed. Triggers are executed implicitly whereas stored procedures are executed explicitly by the user invocation. |
|
From a programming standpoint, there is no difference. The bodies of both (SP and Trig) can be structured in the same way. However, the difference is the way they are executed. 1. Stored Procedures could be executed from a SQL prompt, from within another SP, or from another program altogether. 2. Triggers - essentially are responses to specific database events. When an event (such as INSERT, or UPDATE etc) occurs on a database table, a database can be configured to execute a specific trigger. Therefore, in simple English, you would tell the databse - Create an INSERT Trigger that executes the following set of SQL statements every time there is an INSERT Statement performed on this table. |
|
1. a) SP are "compiled and stored" code which could be called in other programs whenever required. b) Triggers are also compiled and stored, BUT CANNOT be called in any programs. They are just defined as ACTIONS on tables. Whenever an update/delete/insert happens on the table on which the Trigger is written, it gets fired. THERE IS NO WAY a programmer can call it explicitly in any programs. 2. a) You can COMMIT inside an Stored Proc whereas you CANNOT in a trigger. (AUTONOUMOUS_TRANSACTION triggers can commit however) |
