Database Trigger (All Types: Row level and Statement level triggers, Before and After

 

Database Trigger (All Types: Row level and Statement level triggers, Before and After Triggers)

Problem Statement:

Write a database trigger on Library table. The System should keep track of the records that are being updated or deleted. The old value of updated or deleted records should be added in Library_Audit table.

Note: Instructor will Frame the problem statement for writing PL/SQL block for all types of Triggers in line with above statement.

"Write a database trigger on the Library table that maintains an audit log. Whenever a record is updated or deleted, the old values of that record should be inserted into the Library_Audit table. The trigger should differentiate between update and delete operations and store additional details like the type of operation and the timestamp."

Let's Study First What is Trigger? Why do we need Trigger? How to implement Trigger?

Note: Instructor will Frame the problem statement for writing PL/SQL block for all types of Triggers in line with above statement.

"Write a database trigger on the Library table that maintains an audit log. Whenever a record is updated or deleted, the old values of that record should be inserted into the Library_Audit table. The trigger should differentiate between update and delete operations and store additional details like the type of operation and the timestamp."

let's understand what we want to do here in this.

You have a Library table in a database.

Whenever a record in this table is UPDATED or DELETED, you don’t want to lose the old information.

So, you need to:

Write a database trigger that automatically captures the old values before they are changed or removed.

Insert those old values into another table called Library_Audit (this act like a log/history table).

This way:

If someone updates a book’s details → the previous details are saved in Library_Audit.

If someone deletes a book record → the deleted record is also saved in Library_Audit.

But why this is needed?

Audit trail: Helps to keep track of who changed or removed data.

Data recovery: If someone deletes a row by mistake, you still have the backup in Library_Audit.

Accountability: Ensures transparency in changes to critical data.

Let's start the Implementation part:

Since we want all types of triggers, we’ll cover:

1. Row-level trigger

2. Statement-level trigger

3. BEFORE trigger

4. AFTER trigger

5. INSTEAD OF trigger (used for views)

First, we have to create two tables 1. Library and 2. Library_Audit common for all types of triggers.

What is Cursor? Why do we need cursor? Example.

 

Q. What is a Cursor?

Ans:

A cursor is a pointer/handle to the result set of a query in PL/SQL.

It lets us process rows one by one (row-by-row iteration).

Think of it like:

👉 SQL gives you a whole table of results.

👉 A cursor lets you pick each row, work with it, then move to the next.

Q. Why Do We Need Cursors?

Ans:

SQL by itself works in sets (all rows at once).

Example:

UPDATE students SET grade='A' WHERE marks > 90.

→ These updates all qualifying rows in one go. 

But PL/SQL is procedural. 

Sometimes you need to:

1. Fetch one row, process it, make a decision

2. Then move to next row, apply a different logic

👉 In such cases, we require cursors.

Example Without Cursor (Problem Case)

Suppose we have a table Students (roll_no, name, marks)

Now we want to give grades like:

Marks ≥ 90 → Grade 'A'

Marks 75–89 → Grade 'B'

Marks < 75 → Grade 'C'

We cannot write one simple SQL UPDATE because logic is conditional per row.

Notes in Detail:

Problem Statement:

Cursors: (All types: Implicit, Explicit, Cursor FOR Loop, Parameterized Cursor)

Write a PL/SQL block of code using parameterized Cursor that will merge the data available in the newly created table N_RollCall with the data available in the table O_RollCall. If the data in the first table already exist in the second table, then that data should be skipped.

Note: Instructor will frame the problem statement for writing PL/SQL block using all types of Cursors in line with above statement.

Algorithm:

Let's start the Implementation of above problem statement as follows:

1. We need to create two tables N_RollCall (Rno, Sname, Per) and O_RollCall (Rno, Sname, Per) with same attributes.

2. Insert the 3-4 records inside the O_RollCall table after that Insert 4-8 records inside the N_RollCall table out of which 3 records keep same as it is inside the O_RollCall tables.

3. First try to understand what Implicit Cursor is and how it works?

4. Second try to implement Explicit Cursor.

5. Third try to implement Cursor FOR Loop example.

6. Forth try to implement Parameterized Cursor that will merge the data available in the newly created table N_RollCall with the data available in the table O_RollCall.

6.1 Apply the logic: If the data in the first table already exist in the second table, then that data should be skipped.

Implementation:

1. We need to create two tables N_RollCall (Rno, Sname, Per) and O_RollCall (Rno, Sname, Per) with same attributes.

2. Insert the 3-4 records inside the O_RollCall table after that Insert 4-8 records inside the N_RollCall table out of which 3 records keep same as it is inside the O_RollCall tables.

Here, if we observe carefully in N_RollCall table two records 101 and 103 already present inside the O_RollCall table. so, by skipping these two records we want to merge remaining two records 102 and 104 inside the O_RollCall table.

3. First try to understand what Implicit Cursor is and how it works?

4. Second try to implement Explicit Cursor.

How to find candidate key before Normalization?

Finding candidate keys is the foundation before we normalize a relation (2NF, 3NF, BCNF)

Q. What is a Candidate Key?

Ans: A candidate key is a minimal set of attributes that can uniquely identify every tuple (row) in a relation. "Minimal" means: if you remove any attribute from it, it will no longer uniquely identify.

Q. What are the steps to find candidate key?

Steps to Find Candidate Keys

1. List all attributes in the relation.

2. List given Functional Dependencies (FDs).

3. Find closure of attributes (denoted as X⁺ = all attributes X can determine).

4. If X⁺ = all attributes in relation → X is a superkey.

5. Check minimality: if no proper subset of X is a superkey → X is a candidate key.

Let's try to understand this with simple example

Example:

Given: 

Relation with attributes and its functional dependencies are given here in case study.

Relation R (A, B, C, D, E)

Functional Dependencies (FDs):

A → B

B → C

CD → E

E → A

Now apply above steps to find out Candidate Key from the above relation.

Step 1: Check closures

A⁺ = {A, B, C} (from A→B, then B→C) → not all attributes.

B⁺ = {B, C} → not all.

C⁺ = {C} → not all.

D⁺ = {D} → not all.

E⁺ = {E, A, B, C} (E→A, then A→B, B→C) → missing D → not all.

Step 2: Try combinations

AE⁺ = {A, B, C, E} → still missing D.

AD⁺ = {A, B, C, D} → missing E.

ED⁺ = {E, A, B, C, D} → this gives all attributes → superkey.

Step 3: Check minimality

From ED, 

Remove E: D⁺ = {D} → not all.

Remove D: E⁺ = {E, A, B, C} → not all.

→ So, ED is minimal → Candidate Key.

Step 4: Verify if there are others

Try CD⁺ = {C, D, E, A, B} → also gives all → Candidate Key.

So, candidate keys are:

{ED}, {CD}

Q. Why Candidate Key helps in Normalization?

Ans:

Candidate keys help us identify prime attributes (those that are part of any key).

In higher normalization (2NF, 3NF, BCNF), we check FDs based on candidate keys and prime attributes

PL / SQL Unnamed PL SQL code block Assignment

Problem Statement:

Unnamed PL/SQL code block: Use of Control structure and Exception handling is

mandatory.

Suggested Problem statement:

Consider Tables:

1. Borrower (Roll_no, Name, DateofIssue, NameofBook, Status)

2. Fine (Roll_no,Date,Amt)

 Accept Roll_no and NameofBook from user.

 Check the number of days (from date of issue).

 If days are between 15 to 30 then fine amount will be Rs 5per day.

 If no. of days>30, per day fine will be Rs 50 per day and for days less than 30, Rs. 5 per

day.

 After submitting the book, status will change from I to R.

 If condition of fine is true, then details will be stored into fine table.

 Also handles the exception by named exception handler or user define exception handler.

OR

Write a PL/SQL code block to calculate the area of a circle for a value of radius varying from 5 to

9. Store the radius and the corresponding values of calculated area in an empty table named areas,

consisting of two columns, radius and area

Now the first question comes to our mind is how to implement this code? 

1. Which Software I have to use for implementation, if I don't have that software how to install it in my computer so that I can implement the same.

Ans: To implement this assignment we need following installation or setup

1. Choose Operating System: Windows

2. Open below link in new tab inside the browser to download free oracle database setup

https://www.oracle.com/database/free/get-started/?source=ubi-lightbox

3. Windows appear like this here you have to click on "Link to Download"

4. Once the file downloaded successfully you have to unzip or extract it in some where then go to the folder and double click on Setup file of type Application.

5. Select the destination folder or by default it shows the installation path keep it same as it is and click on Install button.

6. It will ask for password keep it simple like 123456 and just click on next button.

7. In between it will ask for java update just click on Allow button. after that it will take 5 to 6 minutes for installation.

8. If your installation done successfully following message appears on the screen.

Now required software installation is done. what next?

2. How do we use this software for implementation of above PL/ SQL assignment.

2.1 To connect the install database, first open your command prompt in Run as Administrator mode.

       and type following command 

       > sqlplus / as sysdab

2.2 In the above command prompt if we carefully observe then we come to know that we have successfully connected the Oracle free pluggable database container CDS$ROOT. as a system database administrator so we have all rights which administrator have.

if you want to see tables from the database you have to write following query:

SQL > select table_name from user_tables;

2.3 Now if you are having a knowledge of PL/SQL code then you can directly write code in the following manner inside the command prompt.

2.4 First basic PL/SQL unnamed code for understanding.

2.5 How to use for(...) loop inside the PL/SQL code block simple demo for the same. Here we have used DML insert command it means first of all we have to create table circle_data with two columns radius and area.

2.6 Complete code as per problem statement is as follows.

Write a PL/SQL code block to calculate the area of a circle for a value of radius varying from 5 to

9. Store the radius and the corresponding values of calculated area in an empty table named areas,

consisting of two columns, radius and area

 

Assignment No 5 PL SQL Create Procedure for Student Categorization

 

Database Transaction Management: Introduction, ACID, Schedule

 Introduction to Database Transaction, Transaction states, ACID properties, Concept of Schedule, Serial Schedule 

Normalization: Algorithms for Decomposition, 2NF, 3NF, BCNF