Trigger

A trigger is a statement that is executed upon modification to the database. A trigger is triggered automatically when an associated DML statement is executed.

A trigger is a stored procedure in database which automatically invokes whenever a special event in the database occurs. 

A trigger description contains three parts:

Event: A change to database that activates the trigger

Condition: A query or test that is run when the trigger is executed

Action: A procedure that is executed when the trigger is activated and its condition is true.

Users may not be aware that a trigger was executed as a side effect of the procedure.

Syntax:

create trigger [trigger_name]

[before | after] 

{insert | update | delete} 

on [table_name] 

[for each row] 

[trigger_body]

Explanation of syntax:

1. create trigger [trigger_name]: Creates or replaces an existing trigger with the trigger_name.
2. [before | after]: This specifies when the trigger will be executed.
3. {insert | update | delete}: This specifies the DML operation.
4. on [table_name]: This specifies the name of the table associated with the trigger.
5. [for each row]: This specifies a row-level trigger, i.e., the trigger will be executed for each row being affected.
6. [trigger_body]: This provides the operation to be performed as trigger is fired

 

BEFORE and AFTER of Trigger:

BEFORE triggers run the trigger action before the triggering statement is run.
AFTER triggers run the trigger action after the triggering statement is run.

Procedures & Functions

"A procedures or function is a group or set of SQL and PL/SQL statements that perform a specific task."

A function and  procedure is a named PL/SQL Block which is similar . The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value.

Procedures:

 A procedure  is a named PL/SQL block which performs one or more specific task.  A procedure has a header and a body.

The header consists of the name of the procedure and the parameters or variables passed to the procedure. 

The body consists or declaration section, execution section and exception section similar to a general PL/SQL Block. A procedure is named for repeated usage.

parameters to procedures can be passes in three ways :

Parameters	Description
IN type	These types of parameters are used to send values to stored procedures.
OUT type	These types of parameters are used to get values from stored procedures. This is similar to a return type in functions.
IN OUT type	These types of parameters are used to send values and get values from stored procedures.

A procedure may or may not return any value.

Syntax:

CREATE [OR REPLACE] PROCEDURE procedure_name (<Argument> {IN, OUT, IN OUT}    <Datatype>,…) 

IS

  Declaration section<variable, constant> ;

BEGIN

  Execution section

EXCEPTION

  Exception section 

END

 

There are two ways to execute a procedure :

• From the SQL prompt : EXECUTE [or EXEC] procedure_name;
• from embedded SQl by call statement: call procedure-name;

 

Functions:

A function is a named PL/SQL Block which is similar to a procedure. The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value.

 Syntax:

CREATE [OR REPLACE] FUNCTION function_name [parameters] 

 RETURN return_datatype;  {IS, AS}

 Declaration_section <variable,constant> ;

 BEGIN  

    Execution_section

    Return return_variable;  

 EXCEPTION

   exception section  

    Return return_variable; 

 END;

A function can be executed in the following ways.

• As a part of a SELECT statement : SELECT emp_details_func FROM dual;
• In a PL/SQL Statements like,  :  dbms_output.put_line(emp_details_func);

 

Procedures VS Functions:

• A function MUST return a value
• A procedure cannot return a value
• Procedures and functions can both return data in OUT and IN OUT parameters
• The return statement in a function returns control to the calling program and returns the results of the function
• The return statement of a procedure returns control to the calling program and cannot return a value
• Functions can be called from SQL, procedure cannot
• Functions are considered expressions, procedure are not

Accessing SQL from Programming Language

A database programmer must have access to a general-purpose programming language for at least two reasons.

·         Not all queries can be expressed in SQL, since SQL does not provide the full expressive power of a general-purpose language.

·         Non-declarative actions – such as printing a report, interacting with a user, or sending the results of a query to a graphical user interface – cannot be done from within SQL

To access SQL from other programming languages, we can use:

• Dynamic SQL: JDBC and ODBC - A general-purpose program can connect to and communicate with a database server using a collection of functions
• Embedded SQL - provide a means by which a program can interact with a database server. The SQL statements are translated at compile time into function calls. At runtime, these function calls connect to the database using an API that provides dynamic SQL facilities

Ø  Dynamic SQL

ODBC (Open Database Connectivity) and JDBC (Java Database Connectivity) serve as APIs for a program to interact with a database server.

ODBC

ODBC works with C, C++, C# and Visual Basic (other APIs such as ADO.NET sit on top of ODBC).

ODBC is the standard for application programs communicating with a database server.

The API will:

·         open a connection with a database

·         send queries and updates

·         get back results

ODBC can be used with applications such as GUIs, spreadsheets etc.

JDBC

JDBC works with Java. Along with supporting various features for querying and updating data, and for retrieving query results, JDBC also supports metadata retrieval i.e. retrieving information about the database such as relations present in the database and the names and types of relation attributes.

JDBC connects with the database as follows:

·         open a connection

·         create a “Statement” object

·         execute queries using the Statement object to send queries and fetch results

·         exception mechanism to handle errors

 

Ø  Embedded SQL

Embedded SQL refers to embedding SQL queries in another language.

SQL can be embedded in various languages including C, Java and Cobol.

A language into which SQL queries are embedded is referred to as a host language, and the SQL structures permitted in the host language comprise embedded SQL.

The EXEC SQL statement is used to identify embedded SQL request to the preprocessor:

            EXEC SQL <embedded SQL statement> END_EXEC

Before executing any SQL statements, the program must first connect to the database. This is done using:

EXEC-SQL connect to server user user-name using password;

Here, server identifies the server to which a connection is to be established.

Variables of the host language can be used within embedded SQL statements. They are preceded by a colon (:) to distinguish from SQL variables (e.g., :credit_amount ).

Variables used as above must be declared within DECLARE section. The syntax for declaring the variables, follows the usual host language syntax.

            EXEC SQL BEGIN DECLARE SECTION

 int credit-amount ;

EXEC SQL END DECLARE SECTION;

·         To write an embedded SQL query, the statemenmt

declare c cursor for statement < SQL query>.

        is used. The variable c is used to identify the query

·         The open statement for our example is as follows:

 EXEC SQL open c ;

This statement causes the database system to execute the query and to save the results within a temporary relation.

·         The fetch statement causes the values of one tuple in the query result to be placed on host language variables.

EXEC SQL fetch c into :si, :sn;

      Repeated calls to fetch get successive tuples in the query result.

·         The close statement causes the database system to delete the temporary relation that holds the result of the query.

EXEC SQL close c ;