What is a Trigger?

A procedural SQL code that is automatically invoked by the relational database management system upon the occurance of a data manipulation event. Triggers can automatically fireoff an action when a specific operation such as changing data in a table, occurs. A trigger consists of an event and an action. They are used to preserve data integrity by checking on changing data in a consistant manner.

• A trigger is invoked before or after a data row is inserted, updated, or deleted.
• A trigger is associated with a database table
• Each database table may have one or more triggers
• Each trigger is executed as part of the transaction that triggered it.
• Triggers can be used to enforce constraints that can not be enforced at the DBMS design and implementation levels.
• Triggers add functionality by automatting critical actions and providing appropriate warnings and suggestions for remedial actions.
• Triggers can be used to update table values, insert records in a table, and call other stored procedures.

What is a Stored Procedure?

A named collection of procedural and SQL statements. Bussiness logic stored on a server in the form of SQL code or some other DBMS-specific procedural language. Just like database triggers, stored procedures are stored in the data base. They can be used to encapsulate and represent business transactions. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data.

Advantages of Stored Procedure

• Stored procedures substantially reduce network traffic and increase performance. Because the stored procedure is stored at the server, there is no transaction of individual SQL statements over the network. As all transactions are executed locally, system performance is increased.
• Help reduce duplication by means of code isolation and code sharing.

What is the difference between trigger and stored procedure?

Triggers are executed automatically when something changes in the database(like new record inserted, deleted etc). Stored procs are executed when they are called.