Appendix G - Stored Procedures and Triggers

 

Stored Procedures Syntax

CREATE { PROC | PROCEDURE } [ schema_name.] { procedure_name | number } 

    [ { @parameter_name data_type } [ = default_value ] ] [ ,...n ] 

AS <sql_statement_block> [;]

DELETE { PROC | PROCEDURE } [schema_name.] { procedure_name | number }  [;]

SQL Triggers Syntax

Trigger on an INSERT, UPDATE, or DELETE statement to a TABLE or VIEW: 

CREATE TRIGGER [schema_name.] trigger_name 

ON { TABLE schema_name[.table_name] | VIEW schema_name[.view_name] } 

{ <trigger_event> <procedure_specifier> } [ ; ] 

Trigger on an INSERT, UPDATE, or DELETE statement to a COLUMN or KEY: 

CREATE TRIGGER [schema_name.] trigger_name 

ON { COLUMN schema_name[.table_name][.column_name] 

   | [VIRTUAL] KEY schema_name[.table_name][.key_name] } 

{ <trigger_event> <procedure_specifier> } [ ; ] 

DELETE TRIGGER [schema_name.] trigger_name [;]

<trigger_event> ::= 

{ BEFORE | AFTER | INSTEAD OF } 

{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 

<procedure_specifier> ::= 

[ WITH <procedure_option> [ ,...n ] ]

AS { [schema_name.] { procedure_name | number } | <sql_statement_block> }  

<procedure_option> ::=

[ @parameter_name =

  { table_name

  | column_name [.$Value]

  | $RowID

  | $Date

  | $Time

  | $Timestamp

  | constant } ]

 

<sql_statement_block> ::= 

{ [ BEGIN ] sql_statement_list [ END ] }

<sql_statement_list> ::= 

{ <sql_statement> } [;][ ...n ] }

 

 <sql_statement> ::= 

{ { DECLARE @variable-name <sqltype>

  | SET @variable-name = <expression>

  | IF <Boolean_expression> { <sql_statement> | <sql_statement_block> }

    [ ELSE { <sql_statement> | <sql_statement_block> } ]

  | WHILE <Boolean_expression>

      { <sql_statement> | <sql_statement_block> | BREAK | CONTINUE }

  | { CASE <select_expression>

        { WHEN <case_expression>

            THEN { <sql_statement> | <sql_statement_block> } } [ ,...n ]

      [ ELSE { <sql_statement> | <sql_statement_block> } ]

     END }

  | { CASE

        { WHEN <boolean_expression>

            THEN { <sql_statement> | <sql_statement_block> } } [ ,...n ]

      [ ELSE { <sql_statement> | <sql_statement_block> } ]

     END }

  | RETURN <integer_expression>

  | EXEC { [schema_name.] { procedure_name | number } } 

     [ <expression> ] [ ,...n ]

  | <sql_dml_statement>

  | <sql_ddl_statement> }