Triggers

Database trigger is a PL/SQL block that is executed on an event in the database. The event is raised to particular data manipulation of a table such as inserting, updating or deleting a row of a table.

Triggers may be used for any of the following:

To implement complex business rules which cannot be implemented by integrity constraints.

To audit the process, i.e, keep track of changes made to a table.

To automatically perform an action when another concerned action takes place.

For example, updating a table whenever there is an insertion of a row into another table.

Types Of Triggers
Triggers are classified into different categories based on whether they are executed on row or statement, where they are fired before or after the event.

Row-level Trigger
A row-level trigger is fired once for each row that is affected by DML command. 

For example, if an update command updates 100 rows then row-level trigger is fired 100 times for each row.

Statement-level Trigger

A statement trigger is fired only for once for a DML statement irrespective of the number of rows affected by the statement.

Before Triggers

While defining a trigger, you can specify whether the trigger is to be fired before the command(INSERT,UPDATE and DELETE) is executed or after the command is executed.

After Trigger
After triggers are fired after the triggering action is completed. For example, if after trigger is associated with INSERT command then it is fired after the row is inserted into the table.

Note: To run Trigger programs ,the best way is to use the default DBA i.e 

SQL> conn hr/hr

Connected.

How to Create a Database Trigger using row-level and statement level triggers

SQL> desc employees

 Name                                      Null?    Type

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

 EMPLOYEE_ID                       NOT NULL       NUMBER(6)

 FIRST_NAME                                              VARCHAR2(20)

 LAST_NAME                          NOT NULL      VARCHAR2(25)

 EMAIL                                   NOT NULL     VARCHAR2(25)

 PHONE_NUMBER                                       VARCHAR2(20)

 HIRE_DATE                            NOT NULL     DATE

 JOB_ID                                  NOT NULL    VARCHAR2(10)

 SALARY                                                     NUMBER(8,2)

 COMMISSION_PCT                                     NUMBER(2,2)

 MANAGER_ID                                            NUMBER(6)

 DEPARTMENT_ID                                       NUMBER(4)

SQL> create table emp55 as select employee_id,first_name,hire_date,salary from employees;

Table created.

SQL> desc emp55;

 Name                                      Null?    Type

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

 EMPLOYEE_ID                                               NUMBER(6)

 FIRST_NAME                                                 VARCHAR2(20)

 HIRE_DATE                                 NOT NULL    DATE

 SALARY                                                         NUMBER(8,2)

Ex: Row-level Trigger
/*Write a ROW-level trigger to check hire_date column of employees table during insertion and updating. If hire_date is greater than sysdate trigger raises application error.*/

create or replace trigger emp55_hiredate

before insert or update of hire_date on emp55

for each row

begin

if :new.hire_date>sysdate then

raise_application_error(-20200,'Hire date cannot be after sysdate');

end if;

end;

/

Output:

SQL> @d:\rl.sql;

Trigger created.

Check your trigger working or not:

SQL> update emp55 set hire_date='6-jul-2022';

update emp55 set hire_date='6-jul-2022'

       *

ERROR at line 1:

ORA-20200: Hire date cannot be after sysdate

ORA-06512: at "HR.EMP55_HIREDATE", line 3

ORA-04088: error during execution of trigger 'HR.EMP55_HIREDATE'

Ex: Statement-level Trigger
Sunday-1,Monday-2 ,Tuesday-3,wednesday-4,thursday-5,friday-6,saturday-7

SQL> select sysdate from dual;
SYSDATE
---------
02-NOV-21
SQL> select to_char(sysdate,'day') from dual;
TO_CHAR(SYSDATE,'DAY')
------------------------------------
tuesday
SQL> select to_char(sysdate,'d') from dual;
T
-
3

/*Write a Statement-level trigger that prevents any changes made to emp55 table on Tuesday.*/ 

create or replace trigger emp55_tue

before insert or update or delete on emp55

begin

if to_char(sysdate,'d')=3 then

raise_application_error(-20210,'No changes can be made on tuesday');

end if;

end;

/

Ouputs:

SQL> @d:\rl.sql

Trigger created.

SQL> delete from emp55;

delete from emp55

            *

ERROR at line 1:

ORA-20210: No changes can be made on tuesday

ORA-06512: at "HR.EMP55_TUE", line 3

ORA-04088: error during execution of trigger 'HR.EMP55_TUE'

SQL> insert into emp55(hire_date) values('16-jul-2022');

insert into emp55(hire_date) values('16-jul-2022')

            *

ERROR at line 1:

ORA-20210: No changes can be made on tuesday

ORA-06512: at "HR.EMP55_TUE", line 3

ORA-04088: error during execution of trigger 'HR.EMP55_TUE'

SQL> update emp55 set salary=32323;

update emp55 set salary=32323

       *

ERROR at line 1:

ORA-20210: No changes can be made on tuesday

ORA-06512: at "HR.EMP55_TUE", line 3

ORA-04088: error during execution of trigger 'HR.EMP55_TUE'

Procedures & Functions

Procedures: A stored procedure is a subprogram unit that consists of a group of PL/SQL statements, stored in the database with a name. Each procedure in Oracle has its own unique name by which it can be referred. This subprogram unit is stored as a database object. A stored procedure can be called from client programs such as PL/SQL, Java programs, PHP programs etc.

Advantages of stored subprograms:

Reduced network traffic : As only name is passed from client to server.

Better performance : As procedure is loaded into memory it stays in memory so that subsequent invocations can make use of procedure in memory. Procedures are stored in compiled format, which make them run faster.

Easy maintenance : As a change of business logic needs only change to procedure in the database. All client applications can immediately use new business logic.

Security : As it is possible to grant privilege to users to execute procedure even though no privilege is granted on table, there is no need to grant privilege on the table to others.

Parameter Modes: When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:

IN - An IN parameter is read-only. You can reference an IN parameter inside a procedure(or function), but you cannot change its value. Oracle uses IN as the default mode. It means that if you don’t specify the mode for a parameter explicitly, Oracle will use the IN mode.

OUT - An OUT parameter is writable. Typically, you set a returned value for the OUT parameter and return it to the calling program. Note that a procedure ignores the value that you supply for an OUT parameter.

IN OUT - An INOUT parameter is both readable and writable. The procedure can read and modify it.

Syntax:

CREATE [OR REPLACE] PROCEDURE procedure_name

[ (parameter [,parameter]) ]

Example:

SQL> select sysdate from dual;
SYSDATE
---------
27-OCT-21
SQL> select to_char(sysdate,'DAY') from dual;
TO_CHAR(S
---------
WEDNESDAY

Program on Procedure with no arguments :

Write PL/SQL block to demonstrate procedure with no arguments.

set serveroutput on

create or replace procedure rkdispdate

is

tempvar date;

cursor c1 is

select sysdate from dual;

begin

open c1;

fetch c1 into tempvar;

if c1%found then

dbms_output.put_line

('Details Of Todays Date Is :');

dbms_output.put_line

('Week Name :'||to_char(tempvar,'Day'));

dbms_output.put_line

('Day Number Is :'||to_char(tempvar,'DD'));

dbms_output.put_line

('Month Name Is :'||to_char(tempvar,'Month'));

dbms_output.put_line

('Year Is :'||to_char(tempvar,'YYYY'));

dbms_output.put_line

('Year In Words Is :'||to_char(tempvar,'Year'));

else

dbms_output.put_line('No Records Found');

end if;

close c1;

end;

/

Output:

Step1:

SQL> @C:\Users\student\Desktop\rk.sql

Procedure created.

Step 2:

Now type the exec <procedure name>

SQL> exec rkdispdate

Details Of Todays Date Is :

Week Name :Wednesday

Day Number Is :27

Month Name Is :October

Year Is :2021

Year In Words Is :Twenty Twenty-One

PL/SQL procedure successfully completed.

Program on Procedure with arguments :

Write PL/SQL block to demonstrate procedure with arguments.

create the following table in the database to proceed for procedure execution.

SQL> desc departments;

 Name                                             Null?         Type

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

 DEPARTMENT_ID                             NOT NULL NUMBER(4)

 DEPARTMENT_NAME                        NOT NULL VARCHAR2(30)

 LOCATION_ID                                                  NUMBER(4)

set serveroutput on

create or replace procedure depart_dtls(d_id number)

is

ddetails departments%ROWTYPE;

cursor c

is

select * into ddetails from departments where department_id>d_id;

begin

open c;

loop

fetch c into ddetails;

dbms_output.put_line(ddetails.department_name|| ' '||ddetails.location_id);

exit when c%notfound;

end loop;

end;

/

Output:

Step1:

SQL> @d:\abc.sql

Procedure created.

Step 2:

SQL> exec depart_dtls(200)

IT Support 1700

NOC 1700

IT Helpdesk 1700

Government Sales 1700

Retail Sales 1700

Recruiting 1700

Payroll 1700

Payroll 1700

PL/SQL procedure successfully completed.

Functions:

1) Write a PL/SQL script to create a function.

clear screen

set serveroutput on

create or replace function getsal(eid number)

return number

is

sal employees.salary%TYPE;

begin

select salary into sal from employees where employee_id=eid;

return sal;

end;

/

output:

Step1:

SQL> @d:\abc.sql

Function created.

Step 2:

begin

dbms_output.put_line('employee salary is :'||getsal(222));

end;

/

2)Write a PL/SQL script to create a function.

clear screen

set serveroutput on

create or replace function getage(dob IN date)

return number is

age number;

begin

age:=TRUNC((SYSDATE - dob)/365.25);

return age;

end;

/

output:

Step1:

SQL> @d:\abc7.sql

Function created.

Step 2:

SQL> begin

  2  dbms_output.put_line('std age  is :'||getage('28-feb-1979'));

  3  end;

  4  /

std age  is :42

PL/SQL procedure successfully completed.

ER - Entity Relationship Model

The two  techniques  that  which  are  used  for  designing  a  database  from  the  system  requirement  are  

1.ER Modelling  (Top  Down approach)

2.Normalization (Bottom Up approach)

Note:

Specialization (Top Down approach) is always applied on single entities

Generalization (Bottom Up approach) is always applied to the group of entities 

ER Modelling [Top down approach]:

ER-Models are originally proposed by peter in 1976 ,to unify the network and the relational database that are being used. 

ER-Model is a conceptual data model that views the real world as entities and relation ships.

Entity: An entity is an object whose information is stored in the database it has an independent  existence to collect the data.

Entities are classified as dependent/independent (or) strong/weak

Entity Set: An Entity Set is a set of entities of some type that share the same properties.

Relationship: A relationship represent an association between two (or) more entities they are classified as 

Degree of Relationship :

1.Unary Relationship

2.Binary Relationship

3.Ternary Relationship 

Relationship Constraints:

1.Cardinality Ratio.

2. Participation Constraints 

Entity: An entity is anything (a person, a place, a thing, or an event) about which data are to be collected and stored. An entity represents a particular type of object in the real world. Entity occurrence is unique and distinct.

Ex: customer, student, employee etc…

Types of Entity:

• Strong Entity
• Weak Entity

Strong Entity:

Strong  entity  are  those  entity  types  which  has  a  key  attribute. The primary key helps in identifying each entity uniquely. It is represented  by  a rectangle. In the above example, Roll_no identifies each element  of  the  table uniquely and hence, we can say that STUDENT is a strong entity type.

Weak Entity:

Weak entity type doesn't have a key attribute. Weak entity type can't be identified on its own. It depends upon some other strong entity for its distinct identity. A weak entity is represented by a double outlined rectangle.

Attributes: Attributes describe the behavior as the properties of an entity that which are associated, different types of attributes are 

1.Simple Attribute

2 Composite Attribute

3.Single value attribute

4.Multi valued Attributes

5.Derived attribute

1.Simple Attribute: It is an attribute composed of single component with an independent existence. These are atomic attributes that which cannot be further sub divided

Example: 

2.Composite Attribute: An attribute composed of multiple components each with an independent existence is called composite attribute

Example:

3.Single valued Attributes: A single value attribute is that which holds a single value for a single entity

Example 1:

Example 2:

4.Multi valued Attributes: The attribute that which holds multiple values for a single entity

Example:

5.Derived attribute :A derived attribute represents a values that is derived from a value of a related attributes  (or) set of attributes where some attributes are related for a particular entity 

Example:

Symbols used in Entity Relationship diagrams:  

Steps in designing ER diagram

Step1:Identify the entities that which are required for developing a project

Step2:Identify  the key attributes for every entity and other relevant attributes

Step3:Find the relationship  among the entities

Step4:Now draw the complete ER diagram with all attributes including the key attributes

Example 1:
ER Diagram for Hospital Management System:

Example 2:
ER-diagram for university management System Database:

Step1: Using MS-Access 

Create the data base for the below ERD diagram with four entity (patient, doctor, nurse and ward)using MS-Access database as shown 

Entity 1: 
patient

pat_id	pat_name	pat_disease
101	raju	tb
102	rani	cancer
103	kani	fever
104	vani	cough
105	dhoni	typhoid

Entity 2:

doctor

doc_id	doc_name	pat_id
201	rk	101
202	pk	102
203	ck	103
204	dk	104
205	sk	105

Entity 3:

nurse

n_id	ward_id	pat_id
301	401	101
302	402	102
303	403	103
304	404	104
305	405	105

Entity 4:
ward

ward_id	pat_id
401	101
402	102
403	103
404	104
405	105

Entity Relationship for Hospital Management System :

Step1: Using MS-Visio

Draw the ERD as shown in fig using MS Visio 

click on file menu -> new -> Software and Database -> Database Model Diagram

Now draw the ERD Diagram as shown in MS-Visio

patient entity

doctor entity

nurse entity

ward entity

Step 2:

Now we shall establish hospital management system using the above four entity relations using the relationship component as shown 

Step 3:

click on Data menu bar -> Link Data to Shapes and then select Microsoft Access Database which will be as shown 

Step 4:

Now patient entity database created in the ms access will be on to your ms visio  as shown

Step 5:

Repeat the same procedure for the remaining entities database  (doctor, nurse and ward) created in the ms access will be on to your ms visio  as shown

Step 6: 

How to run the query in MS-Visio 2007 : To run the query in MS-Visio we need to create a query in MS-Access as follows 

Click on Create menu bar -> Query Design

Step 7: Now add all the entity on to the query wizard

Step 8: Now write your query using the sql view by clicking on design menu bar -> View 

Queries:

1. select * from patient where pat_disease='tb';
2. select * from patient where pat_name like 'r%';
3. select * from patient where pat_id=105;
4. select * from patient where pat_name like '%i';
5. select * from doctor where doc_name like '%k';

Cursor

when an SQL statement is processed, Oracle creates a memory area(work area) known as context area, to execute SQL commands and store processing information in that context area. A cursor is a pointer to this context area, PL/SQL allows us to access this area, through a name – Cursor name. Cursors used in Oracle are of two types.

1)Implicit cursor

2)Explicit cursor

1)Implicit cursor

It is automatically generated by Oracle when an SQL statement is executed, It is used to store information about most recently executed SQL command. These are created by default to process the DML statements like INSERT, UPDATE,DELETE etc. are executed. Oracle provides some attributes known as Implicit cursor's attributes to check the status of DML operations. Some of them are:

%FOUND - It returns TRUE if the most recent fetch operation fetched a

record successfully.

%NOTFOUND - It returns 'TRUE' if the most recent fetch operation could not able to fetch any record.

%ROWCOUNT -It returns 'TRUE' if the given cursor is already opened, else it returns 'FALSE'

%ISOPEN - It returns the numerical value. It gives the actual count of records that got affected by the DML commands.

SQL> select * from emp;

 ENO       ENAME      SALARY        

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

       111 raju             12720
       222 rani             41220
       333 vani             72220
       444 joy              88220

Program on Implicit cursor:

set serveroutput on

cl scr

declare

t number(2);

begin

update emp set salary=salary+500 where eno>300;

if(sql%found) then

dbms_output.put_line('FOUND');

else

dbms_output.put_line('NOT FOUND');

end if;

t:=sql%rowcount;

dbms_output.put_line('Number of Rows Updated:'||t);

end;

/

Output:

SQL> @d:\joy.sql

FOUND

Number of Rows Updated:2

PL/SQL procedure successfully completed.

2)Explicit Cursor:

The Explicit cursors are defined by the programmers to gain more control over the context area. It is used to store result of a multi-row query. The data retrieved is stored in this type of cursor for further process. These cursors should be defined in the declaration section of the PL/SQL block. It is created on a SELECT statement which returns more than one row.

General Syntax of explicit cursor:

CURSOR cursor_name IS select_statement; 

We need to follow the steps given below while working with Explicit Cursors

1.Declare the cursor to initialize in the memory.

2.Open the cursor to allocate memory. 

3.Fetch the cursor to retrieve data.

4.Close the cursor to release allocated memory.

Declare cursor : It defines the cursor with a name and the associated SELECT statement.

Syntax :CURSOR cursor_name IS SELECT statement;

Open the cursor: It is used to allocate memory for the cursor and make it available to fetch the rows returned by the SQL statements into it.

Syntax:OPEN cursor_name;

Fetch the cursor: It is used to access one row at a time. We can fetch rows from a cursor once it is opened.

Syntax:FETCH cursor_name INTO variable_list;

Close the cursor: It is used to release the allocated memory. 

Syntax:Close cursor_name;

 

SQL> select * from emp;

 ENO       ENAME      SALARY        

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

       111 raju             12720
       222 rani             41220
       333 vani             72220
       444 joy              88220

Program on Explicit cursor:

set serveroutput on

cl scr

declare

c_eno emp.eno%type;

c_ename emp.ename%type;

c_salary emp.salary%type;

cursor c_emp is  select eno,ename,salary from emp where eno>300;

begin

open c_emp;

dbms_output.put_line('ENO ENAME SALARY');

loop

fetch c_emp into c_eno,c_ename,c_salary ;

exit when c_emp%notfound;

dbms_output.put_line(c_eno||' '||c_ename||' '||c_salary);

end loop;

close c_emp;

end;

/

Output:

SQL> @d:\joy.sql

ENO ENAME SALARY

333 vani 72720

444 joy 88720

PL/SQL procedure successfully completed.

PL/SQL

PL/SQL stands for “Procedural Language extensions to the Structured Query Language”. SQL is a popular language for both querying and updating data in the relational database management systems (RDBMS). 

PL/SQL is a block structured language that enables developers to combine the power of SQL with procedural statements.

SQL:SQL is a single query that is used to perform DML and DDL operations.

PL/SQL:PL/SQL is a block of codes that used to write the entire program blocks/ procedure/ function, etc.

Program 1:

Write PL/SQL block to display a String....My First PL/SQL Program

Step-1 : Open Notepad and then type the pl/sql program .

set serveroutput on
cl scr
declare
n1 varchar2(30):='My First PL/SQL Program';
begin
dbms_output.put_line(n1);
end;
/

Step-2 : save the notepad file with an extension .sql 
Example : "ram.sql"

Step-3 : Execute the file as follows.
  @d:\ram.sql;

Program 2:

Write PL/SQL block to add 3 numbers(Assign values in Program)

set serveroutput on

cl scr

declare

n1 number(3):=12;

n2 number(3):=13;

n3 number(3):=14;

t number(5);

begin

dbms_output.put_line('---Assigned values in the Progarm---');

t:=n1+n2+n3;

dbms_output.put_line('Sum Of Numbers:'||t);

end;

/

Program 3:

Write PL/SQL block to add 3 numbers(Access values from user)

set serveroutput on

declare

n1 number(3):=&x;

n2 number(3):=&y;

n3 number(3):=&z;

t number(5);

begin

dbms_output.put_line('---Accessed values from User---');

t:=n1+n2+n3;

dbms_output.put_line('Sum Of Numbers:'||t);

end;

/

Program 4:

Write PL/SQL block to compare two numbers(Using If...else construct)

set serveroutput on

declare

n1 number(3):=&x;

n2 number(3):=&y;

begin

if(n1>n2) then

dbms_output.put_line(n1||' is greater');

else

dbms_output.put_line(n2||' is greater');

end if;

end;

/

Program 4.1:

Write PL/SQL block to compare three numbers(Using If...elsif construct)

set serveroutput on

declare

n1 number(3):=&x;

n2 number(3):=&y;

n3 number(3):=&z;

begin

if(n1>n2 and n1>n3) then

dbms_output.put_line(n1||' is largest');

elsif(n2>n1 and n2>n3) then 

dbms_output.put_line(n2||' is largest');

else

dbms_output.put_line(n3||' is largest');

end if;

end;

/

Program 5

Write PL/SQL block to generate numbers from 2 to 10(Using while)

set serveroutput on

declare

x number(3):=2;

begin

dbms_output.put_line('---Using While---');

while x<11

loop

dbms_output.put_line(x);

x:=x+1;

end loop;

end;

/

Program 6:

Write PL/SQL block to demonstrate CASE...END CASE

set serveroutput on

declare

n1 number:=&x;

begin

case n1

when 1 then dbms_output.put_line('One');

when 2 then dbms_output.put_line('Two');

when 3 then dbms_output.put_line('Three');

else

dbms_output.put_line('Invalid Input');

end case;

end;

/

Program 7:

Write PL/SQL block to generate numbers from 2 to 10(Using For)

set serveroutput on

declare

x number(3);

begin

dbms_output.put_line('---Using For---');

for x in 2..10

loop

dbms_output.put_line(x);

end loop;

end;

/

Program 8:

Write PL/SQL block to generate numbers from 10 to 2(Using For)

set serveroutput on

declare

x number(3);

begin

dbms_output.put_line('---Using For---');

for x in reverse 2..10

loop

dbms_output.put_line(x);

end loop;

end;

/

Program 9:

Write PL/SQL block to display multiplication table 19(Using While)

set serveroutput on

declare

n number(3):=1;

begin

dbms_output.put_line('---Multiplication Table Using While---');

while n<21

loop

dbms_output.put_line(19||' X '||n||' = '||n*19);

n:=n+1;

end loop;

end;

/

Program 10:

Write PL/SQL block to display multiplication table 19(Using For)

set serveroutput on

declare

n number(3);

begin

dbms_output.put_line('---Multiplication Table Using For---');

for n in 1..20

loop

dbms_output.put_line(19||' X '||n||' = '||n*19);

end loop;

end;

/

Program 11:

Write a PL/SQL script to check given number is an Armstrong number or not.

set serveroutput on

declare

set serveroutput on

declare

n number(9):=1634;

r number(9);

t number(9):=0;

i number(9);

begin

i:=n;

while n>0

loop

r:=mod(n,10);

t:=t+power(r,4);

n:=floor(n/10);

end loop;

if(t=i) then

dbms_output.put_line('Amstrong');

else

dbms_output.put_line('Not Amstrong');

end if;

end;

/

Program 12:

Write PL/SQL block to display numbers in the given range and find thir sum.

set serveroutput on

declare

n1 number(3):=&x;

n2 number(3):=&y;

x number(3);

t number(3):=0;

begin

for x in n1..n2

loop

dbms_output.put_line(x);

t:=t+x;

end loop;

dbms_output.put_line('Sum of numbers in the given range: '||t);

end;

/

Program 13:

Write a PL/SQL script to find the sum of digits in the given number.

set serveroutput on

declare

n number(9):=&x;

r number(9);

t number(9):=0;

begin

while n!=0

loop

r:=mod(n,10);

t:=t+r;

n:=floor(n/10);

end loop;

dbms_output.put_line('Sum Of Digits In The Given Number: '||t);

end;

/