Implementing stored procedures, user-defined functions, triggers

Stored procedures

Stored procedures are linear or sequential programs.

Stored procedures can accept parameters and allow local variable declarations; they are structured and allow the use of submodules; also, they allow repeated and conditional statement execution.

Create

CREATE PROC[EDURE] <procedure_name>
[@<parameter_name> <datatype> [= <default>] [OUTPUT] ], ...
AS
   <procedure_body>

Remove

DROP PROCEDURE [qualifier.]<procedure_name>

User-defined Functions

User-defined functions combine the advantages of stored procedures with the capabilities of SQL predefined functions. They can accept parameters, perform specific calculations based on data retrieved by one or more SELECT statement, and return results directly to the calling SQL statement.

Create

CREATE FUNCTION <function_name>
([@<parameter_name> <datatype> [ = <default>]],...)
RETURNS <datatype>
[AS]
BEGIN
   <function_body>
   RETURN <value>
END

Remove

DROP FUNCTION [qualifier.]<function_name>

Triggers

A trigger is a special type of stored procedure that fires off automatically whenever a special event in the database occurs. For example, a trigger can be invoked when a row is inserted into a specified table or when certain table columns are being updated.

Create

CREATE TRIGGER <trigger_name>
ON <table_or_view>
{ FOR | AFTER | INSTEAD OF }
{INSERT | UPDATE | DELETE} 
AS 
[IF UPDATE ( <column_name> )
           [AND | OR UPDATE ( <column_name> ) ],...]
<trigger_body>

Remove

DROP TRIGGER [qualifier.]<trigger_name>