Write a PL/SQL code to implement after row trigger.

Experiment-15

Object: Write a PL/SQL code to implement after row trigger. Create two tables as student_master and audit_student.

Requirement: Windows XP, Oracle 9i.

Program Code:

SQL> create table student_master(rollno number(2), name varchar2(20), age number(2

),city varchar2(20));

Table created.

SQL> drop table student_audit;

Table dropped.

SQL> create table student_audit(rollno number(2), name varchar2(20), operation var

pdate date);

Table created.

SQL> insert into student_master values(1,'sharad',20,9818923723,'delhi');

1 row created.

SQL>  insert into student_master values(2,'abhi',21,9775645621,'haryana');

1 row created.

SQL>  create or replace trigger mytrigger

  2   after update or delete on student_master

  3   for each row

  4   declare

  5   operation varchar2(10);

  6   roll_no number(10);

  7   name varchar2(20);

  8   begin

  9   if updating then

 10   operation:='update';

 11   end if;

 12   if deleting then

 13   operation:='delete';

 14   end if;

 15   roll_no:=:old.roll_no;

 16   name:=:old.name;

 17   insert into audit_student values(roll_no,name,operation,sysdate);

 18   end;

Output:

STUDENT_MASTER

FIELD NAME       DATA TYPE          SIZE

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

RollNo                 Number               2

Name                   Varchar2             20

Age                      Number               2

Phone                  Number               10    

City                      Varchar2             20

STUDENT_AUDIT

FIELD NAME                 TYPE           SIZE

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

Tot_Num             Number               10

Tot_Age               Number               6

OUTPUT AS RESULT :

Tot_Age(20)

1 row created.

Tot_Age(41)

1 row created.

Result: The program to create a trigger has been executed successfully.