AFTER Delete trigger sample in Oracle

In this post I'll describe create after delete trigger and access the deleted row data from the trigger.

Prerequisites 

1. You should have install Oracle database installed in your PC.
2. I'm using Oracle SQL developer to execute SQL queries.

Step 1 Lets create two sample tables

I'll create student and subjects table to demonstrate this After Delete trigger.

create student table using following SQL.

create table student

(

studentid number,

firstName varchar2(20) not null,

lastName varchar2(20) not null,

age number(2),

primary key(studentid)

);

Add sample data in to student table.

insert into student values(1,'Lakshan','Silva',21);

insert into student values(2,'Kuma','Sangakara',35);

insert into student values(3,'Nilakshi','Dissanayaka',29);

insert into student values(4,'Laksh','Perera',29);

After inserting above data student table will be similar to below.

Then lets create subjects table and add some sample data.

create table subjects(

  subjectID int,

  subjectName varchar(200),

  studentId number,

  primary key(subjectID) );

  

  insert into subjects values(1,'Phys',2);

  insert into subjects values(2,'Bio',2);

  insert into subjects values(3,'Chemi',3);

  insert into subjects values(4,'Phys',1);

  insert into subjects values(5,'Bio',4);

  insert into subjects values(6,'Chemi',1);

  

Here Subjects table will have details about subjects assigned to each student. 

Step 2 Lets create the trigger

Lets create the trigger which delete related subjects when we delete a particular student in student table. By executing following SQL we can create the trigger.

create or replace trigger deleteTrigger

after delete on student

for each row

declare stdname varchar(100);

begin

  dbms_output.put_line('Name :: '|| :old.studentid);

  delete subjects where studentId=:old.studentid;

end;

Step 3 Lets delete student and check 

Then lets delete student with student id 2 and then check the content of the subjects table. To delete student lets use following sql.

delete from student where studentid=2;

Then our subjects table will be similar to below.

You can see that subjects related to student with studentid 2 has deleted from the trigger.

Due to this kind of triggers may provide many advantages such as it reduces the interactions between application and the database since trigger will be automatically calls when triggering event(In here after delete ) happens.