Viewing Defined Triggers in Oracle

Viewing Defined Triggers

To view all the defined triggers, use:

select name_of_trigger from user_triggers;

For more details on a particular trigger:

select trigger_type, triggering_event, table_name, referencing_names, trigger_body
from user_triggers
where trigger_name = '';

Example of PL/SQL trigger in Oracle

In the below example line 2 is A triggering event or statement, lines 4-9 are A trigger action.

Example of creating a trigger based on the following two tables:

CREATE TABLE T1 (a INTEGER);
CREATE TABLE T2 (b INTEGER);

We will create a trigger that may insert a tuple into T2 when a tuple is inserted into T1. The trigger checks if the inserted row in T1 is has a value less than 5 only then a tuple is inserted in T2.

1 CREATE TRIGGER tr1
2 AFTER INSERT ON T1
3 REFERENCING NEW AS newRow
4 FOR EACH ROW
5 WHEN (newRow.a <= 5)
6 BEGIN
7 INSERT INTO T2
VALUES(:newRow.a);
8 END tr1;
9 .
10 run;

PL/SQL Triggers in Oracle

PL/SQL Triggers

A PL/SQL trigger is a construct in PL/SQL that runs or "triggered" on event of changes being made to a table in the database. The triggering event is a INSERT, UPDATE or DELETE done on a table. The trigger can be made so it can be "fired" either BEFORE or AFTER the Data Manipulation Language is executed.

• A database trigger is a block of code that is automatically executed in response to certain events.
• Triggers are executed implicitly whenever the triggering event happens.
• The triggering event is an INSERT, DELETE, or UPDATE command.
• The timing can be either BEFORE or AFTER, INSTEAD OF trigger.

The trigger can be either row-level or statement-level, where the former fires once for each row affected by the triggering statement and the latter fires once for the whole statement.

You can write triggers that fire whenever one of the following operations occurs:

1. DML statements (INSERT, UPDATE, DELETE) on a particular table or view, issued by any user
   
   
2. DDL statements (CREATE or ALTER primarily) issued either by a particular schema/user or by any schema/user in the database
   
   
3. Database events, such as logon/logoff, errors, or startup/shutdown, also issued either by a particular schema/user or by any schema/user in the database

A trigger has three basic parts:

• A triggering event or statement
• A trigger restriction
• A trigger action

PL/SQL Function Example 2 in Oracle

Example 2 of PL/SQL Function:

create or replace function find_area
(Len in number, Wid in number)
return number
as
varea number;
begin
varea := Len * Wid;
return varea;
end;

SQL> select find_area (10, 30) area from dual;

AREA
---------
300

PL/SQL Function Example in Oracle

Example 1 of PL/SQL Function:

Creating a Function: Examples The following statement creates the function Ask_Balance on the sample table cust_orders (the PL/SQL is in italics):

CREATE FUNCTION Ask_Balance(acc_num IN NUMBER) 
   RETURN NUMBER 
   IS acc_bal NUMBER(11,2);
   BEGIN 
      SELECT order_total 
      INTO acc_bal 
      FROM cust_orders 
      WHERE customer_id = acc_num; 
      RETURN(acc_bal); 
    END;
/

The Ask_Balance function returns the balance of a specified account.

When you call the function, you must specify the argument acc_num, the number of the account whose balance is sought. The datatype of acc_num is NUMBER.

The function returns the account balance. The RETURN clause of the CREATE FUNCTION statement specifies the datatype of the return value to be NUMBER.

The function uses a SELECT statement to select the balance column from the row identified by the argument acc_num in the cust_orders table. The function uses a RETURN statement to return this value to the environment in which the function is called.

The function created in the preceding example can be used in a SQL statement. For example:

SELECT Ask_Balance(165) FROM DUAL;

Ask_Balance(165)
------------
2519

[Source]

Syntax of PL/SQL function in Oracle

PL/SQL Function

There are two types of PL/SQL blocks: named block and an anonymous block.
There are two types of named blocks: Functions and Procedures

FUNCTION name [(parameter[, parameter, …])] RETURN
datatype IS
[local declarations]
BEGIN
executable statements
[EXCEPTION
exception handlers]
END [name];

PL/SQL Function in Oracle

PL/SQL Function

There are two types of PL/SQL blocks: named block and an anonymous block.
There are two types of named blocks: Functions and Procedures

In PL/SQL a Function takes zero or more parameter values and returns one value.

Syntax of PL/SQL Procedure in Oracle

PROCEDURE name [(parameter[, parameter, …])] IS
[local declarations]
BEGIN
executable statements
[EXCEPTION
exception handlers]
END [name];

Example 1 of PL/SQL Procedure

create or replace procedure get_area
(Len in number, Wid in number, Area out number)
as
begin
Area := Len * Wid;
end;

SQL> variable area1 number;
SQL> execute get_area (10, 50, :area1);
PL/SQL procedure successfully completed.
SQL> print area1
AREA1
---------
500

Example 2 of PL/SQL Procedure

The following statement creates the procedure remove_emp in the schema hr. The PL/SQL is shown in italics:

CREATE PROCEDURE remove_emp (employee_id NUMBER) AS
tot_emps NUMBER;
BEGIN
DELETE FROM employees
WHERE employees.employee_id = remove_emp.employee_id;
tot_emps := tot_emps - 1;
END;
/


The remove_emp procedure removes a specified employee. When you call the procedure, you must specify the employee_id of the employee to be removed.

The procedure uses a DELETE statement to remove from the employees table the row of employee_id. [Source]

PL/SQL Procedures In Oracle

There are two types of PL/SQL blocks: named block and an anonymous block.
There are two types of named blocks: Functions and Procedures

A stored procedure is a PL/SQL block that accepts zero or more parameters as input (IN), output (OUT), or both (INOUT). PL/SQL Procedures do not return a value; instead the INOUT parameter or OUT parameter may be used to pass a value from the procedure. Procedures cannot be used in SQL statements; they are invoked using the EXECUTE command or called inside a PL/SQL block.

%TYPE and %ROWTYPE in Oracle

%TYPE is used to declare a variable that is of the same type as a specified table’s column.

Emp_number emp_name.emp_nameno%type;

%ROWTYPE is used to declare a record (variable that represents the entire row of a table).

Emp_record emp_name%rowtype;

Another example of declaring variable:

Declare
name varchar2(30);
Select emp_name_name into name from emp_name where emp_nameno = 203456;
Begin
Null;
End;

Any DML statements should be after Begin statement;

Begin
Delete from emp_name where emp_nameno = 24545459;
Commit;
End;

Declaring PL/SQL variables and constants in Oracle

Example of declaring Variableiables:

Variable1 varchar2(100);
Join_date Date;
Variable2 number default 5;
Variable3 number not null := 2;

Not Null means a value may change but it can never be assigned Null.

Variable4 varchar2(20) := Null;
Variable5 varchar2(20) default Null;

Example of declaring Constants:

Variable_constant constant number := 100;

Constants cannot be changed.

You must initialize constants at the time of declaration.

Oracle Syntax of a PL/SQL Block

DECLARE
Variable_declarations
BEGIN
Program_code
EXCEPTION
Exception_handlers
END;

Below is the basic structure of the PL/SQL program:

Set serveroutput on
Var1 varchar2(20);
Begin
Var1 := ‘welcome’;
Dbms_output.put_line(var1);
Exception
When others then
Dbms_output.put_line(‘It is an exception’);
End;
/
In the declaration section all the variables and constants are defined.

1. In PL/SQL all the errors are handled in the Exception block.
2. Begin and End are mandatory statements indicating begin and end of the PL/SQL Block.
3. Variables and Constants must be declared first before they can be used.
4. The declaration of variables and constants are alike, but constant definitions must contain the keyword CONSTANT and must be assigned a value as part of the definition. Later on any attempts to assign a value to a constant will result in an error message.
5. Values can be assigned to variables directly using the “:=” assignment operator, by way of a SELECT ... INTO statement or When used as OUT or IN OUT parameter from a procedure.

Oracle PL/SQL Introduction |Oracle PL/SQL Tutorial

PL/SQL stands for Procedural Language/SQL. PL/SQL is Oracle's Procedural Language extension to SQL. PL/SQL expands SQL by adding constructs found in procedural languages, resulting in a structural language that is more powerful than SQL. Basically it runs on the database server, but a few Oracle products such as Developer/2000 also contain a PL/SQL engine that resides on the client. Thus, you can run your PL/SQL code on either the client or the server depending on which is more suitable for the task at hand.

Unlike SQL, PL/SQL is procedural, not declarative.

A declarative (non-procedural) programming language is a language that allows the programmer to state the task to be accomplished without specifying the procedures needed to carry it out.

A Procedural programming language is a language in which programs largely consist of a series of commands to assign values to objects.

The basic unit in PL/SQL is a block. All PL/SQL programs are made up of blocks, which can be nested within each other. Typically, each block performs a logical action in the program. PL/SQL is Block Structured.