Programming Oracle with PL/SQL - Learn About Procedure and Function in Oracle with example
A PL_SQL Block are broadly divided into two categories that are anonymous block and named block .

Anonymous Block:-
Anonymous Block - These blocks have no name and are generally stored in a host file or entered directly into SQL*Plus and executed just once. Most of my example uses salary table and here also i used this table. The following example shows how anonymous block work with oracle.

SQL> select * from salary;
    ROLLNO EMPNAME         DESIGN                BPAY         DA         TA         PF     NETSAL
    ---------- --------------- ---------------            ---------- ---------- ---------- ---------- ----------
     10001 S.Krishnan        HOD                     25000       1500       1200       2250      27000
     10002 K.K.Omana       Asst.Manager         19500       1500       1200       1800      22000
     10003 Anishkumar.K    Asst.Manager         19500       1500       1200       1800      22000
     10004 Girishkumar.K   Asst.Manager         19500       1500       1200       1800      22000

SQL> declare increase  number :=10;
  2  begin
  3  update salary set netsal=netsal*(100+increase)/100;
  4  commit;
  5  end;
  6  /
PL/SQL procedure successfully completed. ---> it is an example for anonymous block

SQL> select * from salary;

    ROLLNO EMPNAME         DESIGN                BPAY         DA         TA         PF     NETSAL
    ---------- ---------------   --------------- ---------- ---------- ---------- ---------- ----------
     10001 S.Krishnan        HOD                     25000       1500       1200       2250      29700
     10002 K.K.Omana       Asst.Manager         19500       1500       1200       1800      24200
     10003 Anishkumar.K    Asst.Manager         19500       1500       1200       1800      24200
     10004 Girishkumar.K   Asst.Manager         19500       1500       1200       1800      24200

Named Pl-SQL Block:-
A pl-sql named block is also known as PL-SQL subprogram that can be called anywhere in the pl-sql application and is saved in the data dictionary. 

The same example that we used for anonymous pl-sql block can be used for explaining name pl-sql block.

SQL> create procedure inc_sal(increase number) as
  2  begin
  3  update salary set netsal=netsal*(100+increase)/100;
  4  commit;
  5  end;
  6  /
Procedure created.

SQL> execute inc_sal(12);  ---> calling the procedure

PL/SQL procedure successfully completed.

SQL> select * from salary;

    ROLLNO EMPNAME         DESIGN                BPAY         DA         TA         PF     NETSAL
       ---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------
     10001 S.Krishnan           HOD                  25000       1500       1200       2250      33264
     10002 K.K.Omana       Asst.Manager         19500       1500       1200       1800      27104
     10003 Anishkumar.K    Asst.Manager         19500       1500       1200       1800      27104
     10004 Girishkumar.K   Asst.Manager         19500       1500       1200       1800      27104

PL-SQL subpogram have been devided into two,

1. Procedure
2. Function

Differences between Anonymous Blocks and Subprograms:-

Anonymous blocks	Subprograms
Unnamed PL/SQL blocks	Named PL/SQL blocks
Compiled every time	Compiled only once
Does not store in database	Stores in database
Cannot be invoked by other applications	These are named and therefore can be invoked by other applications
Do not return values	Subprogram called functions must return values
Cannot take parameters	Can take parameters

Procedure:-
A procedure is a block of code that carries out some action. It can, optionally, be defined
with a number of arguments. These arguments are replaced with the actual parameters
given when the procedure is invoked. The arguments can be IN arguments, meaning
that they are used to pass data into the procedure (if you not specify the variable type it will assume as IN type) , or OUT arguments, meaning that they are modified by the procedure and after execution the new values are passed out of the procedure. Arguments can also be IN-OUT, where the one variable serves both purposes. Within a procedure, you can define any number of variables that, unlike the arguments, are private to the procedure. To run a procedure, either call it from within a PL/SQL block or use the interactive EXECUTE command

The order that the variables are passed is important as PL/SQL will assign them to the procedure’s variables in the order that they were passed to the procedure.

POSITIONAL vs. NOTATIONAL parameters
A procedure can be communicated by passing parameters to it. The parameters passed to a procedure may follow either positional notation or named notation.
Example:-
If a procedure is defined as GROSS (ESAL NUMBER, ECOM NUMBER) 
If we call this procedure as GROSS (ESA, ECO) then parameters used are called positional parameters. For Notational Parameters we use the following syntax 
GROSS (ECOM => ECO, ESAL => ESA)

Example.1
A simple procedure and calling it from command mode by execute command,

SQL> select * from salary;

    ROLLNO EMPNAME         DESIGN                BPAY         DA         TA         PF     NETSAL
      ---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------
     10001 S.Krishnan           HOD                  25000       1500       1200       2250      27000
     10002 K.K.Omana       Asst.Manager         19500       1500       1200       1800      22000
     10003 Anishkumar.K    Asst.Manager         19500       1500       1200       1800      22000
     10004 Girishkumar.K   Asst.Manager         19500       1500       1200       1800      22000

SQL>  create or replace procedure  inserttosal (prollno number, pname varchar ,pbpay number)
  2     as
  3     begin
  4     insert into salary(rollno,empname,bpay) values(prollno, pname,pbpay);
  5     commit;
  6     end;
  7     /
Procedure created.

SQL> exec inserttosal(10005,'Rakesh.K',22000); --> proc called by exec command

PL/SQL procedure successfully completed.

SQL> select * from salary;

    ROLLNO EMPNAME         DESIGN                BPAY         DA         TA         PF     NETSAL
    ---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------
     10001 S.Krishnan           HOD                  25000       1500       1200       2250      27000
     10002 K.K.Omana       Asst.Manager         19500       1500       1200       1800      22000
     10003 Anishkumar.K    Asst.Manager         19500       1500       1200       1800      22000
     10004 Girishkumar.K   Asst.Manager         19500       1500       1200       1800      22000
     10005 Rakesh.K                                     22000 ---> new row inserted by proc
SQL>

Example.2
Here procedure is called from a pl-sql block,

SQL> create  or replace procedure pro_salinsert (prollno IN number,pempname IN varchar,
  2  pdesign In varchar , pbpay IN number ) is
  3  pda number(6,2);
  4  pta number(6,2);
  5  ppf number(6,2);
  6  pnetsal number(8,2);
  7  begin
  8  pda := pbpay*8/100;
  9  pta := pbpay*10/100;
 10  ppf := pbpay*7/100;
 11  pnetsal := pbpay + pda + pta - ppf;
 12  insert into salary values
 13  (prollno,pempname,pdesign,pbpay,pda,pta,ppf,pnetsal);
 14  end;
 15  /
Procedure created.

SQL> set lines 450
SQL> select * from salary;

    ROLLNO EMPNAME         DESIGN                BPAY         DA         TA         PF     NETSAL
---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------
     10001 S.Krishnan       HOD                      25000       1500       1200       2250      33264
     10002 K.K.Omana       Asst.Manager         19500       1500       1200       1800      27104
     10003 Anishkumar.K    Asst.Manager         19500       1500       1200       1800      27104
     10004 Girishkumar.K   Asst.Manager         19500       1500       1200       1800      27104

SQL> ed insert_by_proc
declare
prollno salary.rollno%type;

pempname salary.empname%type;

pdesign salary.design%type;

pbpay salary.bpay%type;

begin

prollno := &prollno;

pempname := '&pempname';

pdesign := '&pdesign';

pbpay := &pbpay;

pro_salinsert (prollno , pempname , pdesign ,pbpay); ---> procedure called here

end;

/

SQL> @insert_by_proc

Enter value for prollno: 10005

old   8: prollno := &prollno;

new   8: prollno := 10005;

Enter value for pempname: Aneesh.K

old   9: pempname := '&pempname';

new   9: pempname := 'Aneesh.K';

Enter value for pdesign: Typist

old  10: pdesign := '&pdesign';

new  10: pdesign := 'Typist';

Enter value for pbpay: 15000

old  11: pbpay := &pbpay;

new  11: pbpay := 15000;

PL/SQL procedure successfully completed.

SQL> select * from salary;

    ROLLNO EMPNAME         DESIGN                BPAY         DA         TA         PF     NETSAL

---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------

     10001 S.Krishnan        HOD                    25000       1500       1200       2250      33264

     10002 K.K.Omana       Asst.Manager         19500       1500       1200       1800      27104

     10003 Anishkumar.K    Asst.Manager         19500       1500       1200       1800      27104

     10004 Girishkumar.K   Asst.Manager         19500       1500       1200       1800      27104

     10005 Aneesh.K         Typist                    15000       1200       1500       1050      16650

SQL>

Example.3
In this example, i used OUT variable in a procedure,

SQL> create or replace procedure pro_circlearea ( r in number , area out number ) is
  2  begin
  3  area := 3.14159*r*r;
  4  end;
  5  /
Procedure created.

SQL> create table circle (
  2  radius number(5,2), area number(7,2));
Table created.

SQL> ed circleinsert
declare 
pradius circle.radius%type;
parea circle.area%type;
begin
pradius :=&pradius;
pro_circlearea(r => pradius, area => parea); --> here i used  NOTATIONAL parameters
insert into circle values (pradius,parea);
end;
/
SQL> @circleinsert
Enter value for pradius: 10
old   8: pradius :=&pradius;
new   8: pradius :=10;

PL/SQL procedure successfully completed.
SQL> select * from circle;

    RADIUS       AREA
   ---------    ----------
           10      314.16

Function:-

A function is similar in concept to a procedure, but it does not have OUT arguments and cannot be invoked with the EXECUTE command. It returns a single value, with the RETURN statement.Anything that a function can do, a procedure could also do. Functions are generally used to support specific operations: small code blocks, that will be used many times.Procedures are more commonly used to divide code into modules, and may contain long and complex processes.

Example.1
Here i created a function to calculate the area of the circle,


SQL> create or replace function func_circlearea
  2  (r number) return number is
  3  area number(7,2);
  4  begin
  5  area := 3.14159*r*r;
  6  return area;
  7  end;
  8  /
Function created.

SQL> ed circleinsert
declare 
pradius circle.radius%type;
parea circle.area%type;
begin
pradius :=&pradius;
parea := func_circlearea(pradius); ---> function is called here
insert into circle values (pradius,parea);
end;
/

SQL> @circleinsert
Enter value for pradius: 10
old   8: pradius :=&pradius;
new   8: pradius :=10;
PL/SQL procedure successfully completed.

SQL> select * from circle;

    RADIUS       AREA
   ----------   ----------
           10       314.16
SQL>
you can also retreive the information by,

SQL> select func_circlearea(11) from dual;

FUNC_CIRCLEAREA(11)
-------------------
             380.13
Note:- It will not insert the records into circle table , but display the return value of the function.

Learn About Different Type Of Triggers In Oracle

Database triggers are specialized stored programs. Oracle engine allow the definition of procedure that are implicitly executed when an insert, update, delete is issued again a table from sql or thought application the trigger automatically associated DML statement is executed.They are not called directly but are triggered by events in the database. They run between the time you issue a commandand the time you perform the database management system action. You can write triggers in PL/SQL.

PL/SQL Type of Triggers based on how they triggered:-

Before Triggers: These triggers are fired before the triggering SQL statement (INSERT, UPDATE, DELETE) is executed. The execution of triggering SQL statement is stopped depending on the various conditions to be fulfilled in BEFORE trigger.

After Triggers: These triggers are fired after the triggering SQL statement  (INSERT, UPDATE, DELETE) is executed. The triggering SQL statement is executed first followed by the code of trigger.

ROW Trigger: The triggers are fired for each and every record which is inserted or updated or deleted from a table.

Statement Trigger: The Trigger are fired for each row of DML operation being performed on a table. we can not access the column values for records being inserted, updated, deleted on the table and not individual records.

PL/SQL Triggers Syntax Description:-

CREATE or REPLACE TRIGGER trigger_name: Creates a trigger with the given name otherwise overwrites an existing trigger with the same name.

{BEFORE , AFTER }: Indicates the where should trigger get fired. BEFORE trigger execute before when statement execute before time or AFTER trigger execute after when statement execute after time.

{INSERT , UPDATE , DELETE}: Determines the performing trigger event. More than one triggering events allow can be used together separated by OR keyword.

ON Table Name: Determine the perform trigger event in selected Table.

[Referencing {old AS old, new AS new}]: Reference the old and new values of the data being changed. :old use to existing row perform and :new use to execute new row to perform. The reference names can also be changed from old (or new) to any other user-defined name. You cannot reference old values when inserting a record, or new values when deleting a record, because they do not exist.

Note:-

Insert have no :OLD value ( before execution) and have :NEW value (After execution)

Delete have no :OLD value but it have :NEW value.

Update have both :OLD and :NEW value.

for each row: Trigger must fire when each row gets Affected (Row Level Trigger) or just once when the entire sql statement is executed(statement level Trigger).

WHEN (condition): Valid only for row level triggers. The trigger is fired only for rows that satisfy the condition specified.

There are various events on which a trigger can be written, such as:

1.System events

.....a. Database startup and shutdown

.....b. Server error message events

2.User events

.....a. User logon and logoff

.....b. DDL statements (CREATE, ALTER, and DROP)

.....c. DML statements (INSERT, DELETE, and UPDATE)

Based on the above condition we can classify the trigger into 5 catogory dml trigger , ddl trigger ,Compound triggers, Instead-of triggers and System or database event triggers. Out of which here i am discussing mainly ddl and dml trigger.

1.DDL Trigger

DDL triggers fire when you create, change or remove objects in a database, they support both before and after event triggers and work at the database or schema level.

DDL event supported :-

alter, analyze, associate statistics, audit, comment, create, ddl, disassociate statistics, drop, grant, noaudit, rename, revoke, truncate .

There are a number of event attribute functions that can be used to get user, client or system information , commonly used are given below.

ORA_CLIENT_IP_ADDRESS	returns the client IP address as varchar2
ORA_DATABASE_NAME	returns database name as varchar2
ORA_DES_ENCRYPTED_PASSWORD	returns DES-encrypted password as varchar2
ORA_DICT_OBJ_NAME	returns object name as varchar2
ORA_DICT_OBJ_NAME_LIST	returns the number of elements in the list as a pls_integer
ORA_DICT_OBJ_OWNER	returns the owner of the object acted upon by the event as a varchar2
ORA_DICT_OBJ_OWNER_LIST	returns the number of elements in the list as a pls_integer
ORA_DICT_OBJ_TYPE	returns the datatype of the dictionary object changed by the event as a varchar2
ORA_GRANTEE	returns the number of elements in the list as a pls_integer
ORA_INSTANCE_NUM	returns the current database instance number as a number
ORA_IS_ALTER_COLUMN	returns true or false depending if the column has been altered (true = altered)
ORA_IS_CREATING_NESTED_TABLE	returns a true or false value when you create a table with a nested table
ORA_IS_DROP_COLUMN	returns true or false depending if the column has been dropped (true = dropped)
ORA_IS_SERVERERROR	returns true or false when the error is on the error stack
ORA_LOGIN_USER	returns the current schema name as a varchar2
ORA_PARTITION_POS	returns the numeric position with the SQL text where you can insert a partition clause
ORA_PRIVILEGE_LIST	returns the number of elements in the list as a pls_integer
ORA_REVOKEE	returns the number of elements in the list as a pls_integer
ORA_SERVER_ERROR	returns the error number as a number
ORA_SERVER_ERROR_DEPTH	returns the number of errors on the error stack as a pls_interger
ORA_SERVER_ERROR_MSG	returns an error message text as a varchar2
ORA_SERVER_ERROR_NUM_PARAMS	returns the count of any substituted strings from error messages as a pls_integer
ORA_SERVER_ERROR_PARAM	returns an error message text as a varchar2
ORA_SQL_TXT	returns the number of elements in the list as a pls_integer
ORA_SYSEVENT	returns the system event that was responible for firing the trigger as a varchar2
ORA_WITH_GRANT_OPTION	returns true or false when privileges are granted with grant option (true = with grant option)
SPACE_ERROR_INFO	returns true or false when the triggering event is related to an out-of-space condition.Now for an example

Example.1

The below given ddl trigger prevent truncating table on schema level,

SQL> create or replace trigger prevent_truncates

  2    before truncate on schema

  3    begin

  4    raise_application_error(-20001,'TRUNCATE not permitted');

  5   end;

  6    /

Trigger created.

SQL> create table salary_bk as select * from salary;

Table created.

SQL> select * from salary_bk;

    ROLLNO EMPNAME         DESIGN                BPAY         DA         TA         PF     NETSAL

    ---------- ---------------    ---------------       ----------   ---------- ---------- ---------- ----------

     10001 S.Krishnan        HOD                     25000       1500       1200       2250      27000

     10002 K.K.Omana       Asst.Manager         19500       1500       1200       1800      22000

     10003 Anishkumar.K    Asst.Manager         19500       1500       1200       1800      22000

     10004 Girishkumar.K   Asst.Manager         19500       1500       1200       1800      22000

SQL> truncate table salary_bk;

truncate table salary_bk

               *

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-20001: TRUNCATE not permitted

ORA-06512: at line 2

SQL>    

Example.2 

Below given trigger update every create statement that happens in the schema level into 

log_table,

SQL> CREATE TABLE log_table(

  2     user_name       VARCHAR2(100),

  3     event_date      DATE,

  4     detail          VARCHAR2(400));

Table created.

SQL> 

CREATE OR REPLACE TRIGGER log_create_trigg

  2    AFTER CREATE ON SCHEMA

  3  BEGIN

  4    INSERT INTO log_table

  5      (user_name, event_date, detail)

  6    VALUES

  7      (USER, SYSDATE, 'created object is: ' || ora_dict_obj_name);

  8  END;

  9  /

Trigger created.

SQL> select * from log_table;

no rows selected

SQL> create table abc as select * from dba_users;

Table created.

SQL> col user_name for a12

SQL> col detail for a25

SQL> select * from log_table;

USER_NAME    EVENT_DAT DETAIL

------------ --------- -------------------------

MAHI         19-OCT-12 created object is: ABC

2.Database event trigger

These triggers fire when a system activity occurs in the database, like the logon and logoff event triggers. They are useful for auditing information of system access. These triggers let you track system events and map them to users.

Example:-

Below given trigger logs the logging information into log_trigger_table table, 

SQL> CREATE TABLE log_trigger_table (

  2        user_name       VARCHAR2(30),

  3        event_date      DATE,

  4        action     VARCHAR2(300));

Table created.

SQL> CREATE OR REPLACE TRIGGER logon_trigger

  2      AFTER LOGON ON SCHEMA

  3      BEGIN

  4      INSERT INTO log_trigger_table

  5       (user_name, event_date, action )

  6       VALUES

  7       (USER, SYSDATE, 'Logging On');

  8    END;

  9     /

Trigger created.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\DELL\node1>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 19 17:39:19 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: mahi

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from log_trigger_table;

USER_NAME    EVENT_DAT ACTION

------------ --------- -----------------------------------

MAHI         19-OCT-12 Logging On

3.DML Trigger

These triggers fire when you insert, update, or delete data from a table. You can fire them once for all changes on a table, or for each row change, using statement- or row-level trigger types, respectively. DML triggers are useful to control DML statements. You can use these triggers to audit, check, save, and replace values before they are changed.

Example.1

Below given example insert each record that will deleted from salary table into sal_deleted table,

SQL> select * from salary;

    ROLLNO EMPNAME         DESIGN                BPAY         DA         TA         PF     NETSAL

    ---------- ---------------   ---------------        ---------- ---------- ---------- ---------- ----------

     10001 S.Krishnan        HOD                     25000       1500       1200       2250      27000

     10002 K.K.Omana       Asst.Manager         20000       1500       1200       1800      22000

     10003 Anishkumar.K    Asst.Manager         20000       1500       1200       1800      22000

     10004 Girishkumar.K   Asst.Manager         20000       1500       1200       1800      22000

     10005 Arunkumar.K     Programmer           12000       1440       1320       1080      13800

SQL> create table sal_deleted(

rollno number(5), name varchar(15),

del_date date);

Table created.
SQL>

Now create the trigger,

SQL>ed sal_delete_trig

create or replace trigger sal_delete before delete

on salary for each row

begin 

insert into sal_deleted values

 (:old.rollno, :old.empname,sysdate);

end;

/

SQL> @sal_delete_trig

Trigger created.

SQL> delete from salary where rollno = 10005;

1 row deleted.

SQL> select * from salary;

      ROLLNO  EMPNAME    DESIGN                BPAY         DA         TA         PF     NETSAL

      ---------- --------------- ---------------          ---------- ---------- ---------- ---------- ----------

     10001   S.Krishnan      HOD                     25000       1500       1200       2250      27000

     10002 K.K.Omana       Asst.Manager         20000       1500       1200       1800      22000

     10003 Anishkumar.K    Asst.Manager         20000       1500       1200       1800      22000

     10004 Girishkumar.K   Asst.Manager         20000       1500       1200       1800      22000

SQL> select * from sal_deleted;

    ROLLNO NAME            DEL_DATE

---------- --------------- ---------

     10005 Arunkumar.K     19-OCT-12

Example.2

Following trigger will insert the system time automatically into DOJ field while inserting records into student_details table,

SQL> create table student_details

  2  (rollno number(5), name varchar(15),

  3  dob date, doj date, dop date );

Table created.

SQL> ed student_details_trig;

create trigger student_details_trig before insert

on student_details for each row

begin

:new.doj := sysdate;

end;

/

SQL> @student_details_trig

Trigger created.

SQL> select * from student_details;

no rows selected

SQL> select sysdate from dual;

SYSDATE

---------

19-OCT-12

SQL> insert into student_details (rollno,name,dob) values (1001,'MAHESH','30-OCT-86');

1 row created.

SQL> select * from student_details;

    ROLLNO NAME            DOB       DOJ       DOP

---------- --------------- --------- --------- ---------

      1001 MAHESH          30-OCT-86 19-OCT-12

SQL>

Here you can see DOJ is automatically inserted by the trigger..

Example.3

Following trigger will insert each records into salupdated table before update happens in salary table,

SQL> select * from salary;

    ROLLNO EMPNAME         DESIGN                BPAY         DA         TA         PF     NETSAL

---------- ---------------         --------------- ---------- ---------- ---------- ---------- ----------

     10001 S.Krishnan        HOD                     25000       1500       1200       2250      27000

     10002 K.K.Omana       Asst.Manager         20000       1500       1200       1800      22000

     10003 Anishkumar.K    Asst.Manager         20000       1500       1200       1800      22000

     10004 Girishkumar.K   Asst.Manager         20000       1500       1200       1800      22000

SQL> create table salupdated(

  2  rollno number(5),

  3  empname varchar(15),

  4  design varchar(15),

  5  bpay number(8,2),

  6  da number(6,2),

  7  total number(8,2),

  8  ta number(6,2));

Table created.

SQL> ed salupdate_trig

create or replace trigger salupdate_trig before update

on salary for each row

begin

insert into salupdated values

(:old.rollno, :old.empname, :old.design, :old.bpay, :old.da, :old.netsal, :old.ta);

end;

/

SQL> @salupdate_trig

Trigger created.

SQL> select * from salupdated;

no rows selected

SQL> update salary set BPAY=21000 where DESIGN='Asst.Manager';

3 rows updated.

SQL> select * from salary;

    ROLLNO EMPNAME         DESIGN                BPAY         DA         TA         PF     NETSAL

---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------

     10001 S.Krishnan        HOD                     25000       1500       1200       2250      27000

     10002 K.K.Omana       Asst.Manager         21000       1500       1200       1800      22000

     10003 Anishkumar.K    Asst.Manager         21000       1500       1200       1800      22000

     10004 Girishkumar.K   Asst.Manager         21000       1500       1200       1800      22000

SQL> select * from salupdated;

    ROLLNO EMPNAME         DESIGN                BPAY         DA      TOTAL         TA

---------- --------------- --------------- ---------- ---------- ---------- ----------

     10002 K.K.Omana       Asst.Manager         20000       1500      22000       1200

     10003 Anishkumar.K    Asst.Manager         20000       1500      22000       1200

     10004 Girishkumar.K   Asst.Manager         20000       1500      22000       1200

SQL>

Example.4
Following dml trigger will raise an application error while trying to delete records belonging to Asst.Manager,

SQL> select * from salary;

    ROLLNO EMPNAME         DESIGN                BPAY         DA         TA         PF     NETSAL

---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------

     10001 S.Krishnan        HOD                     25000       1500       1200       2250      27000

     10002 K.K.Omana       Asst.Manager         19500       1500       1200       1800      22000

     10003 Anishkumar.K    Asst.Manager         19500       1500       1200       1800      22000

     10004 Girishkumar.K   Asst.Manager         19500       1500       1200       1800      22000

SQL> CREATE or REPLACE TRIGGER not_del

  2  AFTER

  3  DELETE ON salary

  4  for each row

  5

  6  BEGIN

  7  IF :old.DESIGN = 'Asst.Manager' THEN

  8     raise_application_error(-20015, 'Not Delete this Row');

  9  END IF;

 10  END;

 11  /

Trigger created.

SQL> delete from salary where rollno=10004;

delete from salary where rollno=10004

            *

ERROR at line 1:

ORA-20015: Not Delete this Row

ORA-06512: at "MAHI.NOT_DEL", line 3

ORA-04088: error during execution of trigger 'MAHI.NOT_DEL