Database stored procedures and triggers

Stored Procedures
is a precompiled collection of SQL statements and optional control-flow statements, stored as a single name and processed as a unit. Stored procedures are stored in the database and can be executed by the application through a call, and allow the user to declare variables, conditional execution, and other powerful programming features.
Stored procedures can contain program flow, logic, and queries against the database. They can accept parameters, output parameters, return single or multiple result sets, and return values.
You can use stored procedures for any purpose that uses SQL statements, and it has the following advantages:
You can execute a series of SQL statements in a single stored procedure.
You can reference other stored procedures from within your own stored procedure, which simplifies a series of complex statements.
Stored procedures are compiled on the server at the time they are created, so they execute faster than a single SQL statement.

Trigger
is a special type of stored procedure in which the trigger takes effect when modifying data in a specified table using one or more of the following data modification operations: UPDATE, INSERT, or DELETE. Triggers can query other tables and can contain complex SQL statements. They are primarily used to enforce complex business rules or requirements. For example, you can control whether a customer-based current account state is allowed to insert an order.
Triggers also help enforce referential integrity to preserve defined relationships between tables when rows are added, updated, or deleted in a table. However, the best way to enforce referential integrity is to define primary key and foreign key constraints in related tables. If you use a database diagram, you can create relationships between tables to automatically create foreign key constraints. For more information, see table relationships.

Advantages of using triggers
The advantages of a trigger are as follows:
Triggers are automatic: They are activated immediately after making any modifications to the table's data, such as manual input or actions taken by the application.
Triggers can cascade changes through related tables in the database. For example, you can write a delete trigger on the title_id column of the titles table so that each matching row in the other table takes a delete operation. The trigger uses the title_id column as the unique key to locate each matching row in the titleauthor, sales, and roysched tables.
Triggers can enforce throttling, which is more complex than defined with a CHECK constraint. Unlike a CHECK constraint, a trigger can reference a column in another table. For example, a trigger can roll back an update that tries to apply a discount (stored in the discounts table) to a book that is priced below $10 (stored in the titles table).

Database stored procedures and triggers