Trigger

A trigger is a program in a database that gets called each time a row in a table is INSERTED, UPDATED, or DELETED. Triggers allow you to check that any changes are correct, or to fill in missing information before it is COMMITed. Triggers are normally written in PL/SQL or Java.

[edit]Examples

Audit logging:

CREATE TABLE t1 (c1 NUMBER);
CREATE TABLE audit_log(stamp TIMESTAMP, usr VARCHAR2(30), new_val NUMBER);

CREATE TRIGGER t1_trig
  AFTER INSERT ON t1 FOR EACH ROW
BEGIN
  INSERT INTO audit_log VALUES (SYSTIMESTAMP, USER, :NEW.c1);
END;
/

Prevent certain DML operations:

CREATE OR REPLACE TRIGGER t1_trig
  BEFORE INSERT OR UPDATE OR DELETE
  ON t1
BEGIN
  raise_application_error(-20001,'Inserting and updating are not allowed!');
END;
/

Function







A function is a block of PL/SQL code named and stored within the database. A function always returns a single value to its caller.



Contents


1 Creating and dropping functions
2 Calling functions
3 Examples
4 Also see




[edit]Creating and dropping functions

Create a function:
CREATE OR REPLACE FUNCTION mult(n1 NUMBER, n2 NUMBER) RETURN NUMBER
AS
BEGIN
  RETURN n1 * n2;
END;
/


Remove the function from the database:
DROP FUNCTION mult;


[edit]Calling functions

Call the above function from SQL:
SQL>  SELECT mult(10, 2) FROM dual;
MULT(10,2)
----------
        20


Call the above function from SQL*Plus:
SQL> VARIABLE val NUMBER
SQL> EXEC :val := mult(10, 3);
PL/SQL procedure successfully completed.
SQL> PRINT :val
       VAL
----------
        30


Calling the function from PL/SQL:
DECLARE
  v_val NUMBER;
BEGIN
  v_val := mult(10, 4);
  Dbms_output.Put_Line('Value is: '|| v_val);
END;
/


[edit]Examples

Simple lookup function (lookup an employee's salary):
CREATE OR REPLACE FUNCTION get_salary (p_empno NUMBER)
   RETURN NUMBER
AS
  v_sal emp.sal%TYPE;
BEGIN
  SELECT sal INTO v_sal FROM emp WHERE empno = p_empno;
  RETURN v_sal;
END;
/


Package







A package is a collection of procedures and functions stored within the database.

A package usually has a specification and a body stored separately in the database. The specification is the interface to the application and declares types, variables, exceptions, cursors and subprograms. The body implements the specification.

When a procedure or function within the package is referenced, the whole package gets loaded into memory. So when you reference another procedure or function within the package, it is already in memory.

[edit]Example
CREATE OR REPLACE PACKAGE my_pack AS
  g_visible_variable VARCHAR2(20);
  FUNCTION calc(n1 NUMBER, n2 NUMBER) RETURN NUMBER;
END;
/

CREATE OR REPLACE PACKAGE BODY my_pack AS

  g_hidden_variable CONSTANT INTEGER := 2;

  FUNCTION calc(n1 NUMBER, n2 NUMBER) RETURN NUMBER AS
  BEGIN
    RETURN g_hidden_variable * n1 * n2;
  END;

END;
/