[SQL] Stored Procedure & Trigger

Stored Procedure (wiki)

A stored procedure is a subroutine available to applications accessing a relational database system. Stored procedures (sometimes called a proc, sproc, StoPro, or SP) are actually stored in the database data dictionary.

Typical uses for stored procedures include data validation (integrated into the database) or access control mechanisms. Furthermore, stored procedures are used to consolidate and centralize logic that was originally implemented in applications. Large or complex processing that might require the execution of several SQL statements is moved into stored procedures and all applications call the procedures only.

Stored procedures are similar to user-defined functions (UDFs). The major difference is that UDFs can be used like any other expression within SQL statements, whereas stored procedures must be invoked using the CALL statement^{[citation needed]}

Trigger (wiki)

A database trigger is procedural code that is automatically executed in response to certain events on a particular table in a database. Triggers can restrict access to specific data, perform logging, or audit data modifications.

There are two classes of triggers, they are either "row triggers" or "statement triggers". Row triggers define an action for every row of a table, while statement triggers occur only once per INSERT, UPDATE, or DELETE statement. Triggers cannot be used to audit data retrieval via SELECT statements.

Each class can be of several types. There are "BEFORE triggers" and "AFTER triggers" which identifies the time of execution of the trigger. There is also an "INSTEAD OF trigger" which is code that gets executed instead of the triggering statement.

There are typically three triggering events that cause triggers to 'fire':

• INSERT event (as a new record is being inserted into the database).
• UPDATE event (as a record is being changed).
• DELETE event (as a record is being deleted).

The trigger is used to automate DML condition process.

The major features of database triggers, and their effects, are:

• do not accept parameters or arguments (but may store affected-data in temporary tables)
• cannot perform commit or rollback operations because they are part of the triggering SQL statement (only through autonomous transactions)
• can cause mutating table errors, if they are poorly written.

Reference

1. MS SQL Server的T-SQL指令、Stored Procedure與Trigger
2. 如何使用MySQL的Stored Procedure與Trigger
3. CREATE TRIGGER (Transact-SQL)