SQL Trigger

demo.sql

create table student(
rollno number(5),
nam varchar(50));

create table audittrail(
rollldata number(5),
namedata varchar(50),
operationname varchar(60),
table_name varchar(30));

create or replace trigger trig_auditrail
after insert on student
for each row
begin
  insert into audittrail(
  rollldata,
  namedata,
  operationname,
  table_name)
  values
  (:new.rollno,
  :new.nam,
  'Insert operation',
  'student');
end trig_auditrail;


insert into student (rollno,nam) values(12,'Shrawan poudel');
update  student set nam='Shrawan' where rollno=12;
delete student where rollno =12;
select * from audittrail;



create or replace trigger trig_auditrail
after insert or update or delete on student
for each row
begin
  case when inserting then 
  insert into audittrail(
  rollldata,
  namedata,
  operationname,
  table_name)
  values
  (:new.rollno,
  :new.nam,
  'Insert operation',
  'student');
  
 when updating then
    insert into audittrail(
  rollldata,
  namedata,
  operationname,
  table_name)
  values
  (:old.rollno,
  :old.nam,
  'Update operation',
  'student');
  
 when deleting then
    insert into audittrail(
  rollldata,
  namedata,
  operationname,
  table_name)
  values
  (:old.rollno,
  :old.nam,
  'Delete operation',
  'student');
  end case;
end trig_auditrail;