Trigger Examples in Oracle

create or replace trigger upsert before  insert or delete or update
on trans_emp for each row

DECLARE
  osuser        VARCHAR2(8);
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  --
  -- get who is making the DML change from the session
  --
  osuser := user;
  -- get who is making the DML change from the session
  --
  osuser := NVL(SUBSTR(user,1,8),'oracle');

--
  IF INSERTING THEN
    :new.created_by := osuser;
    :new.creation_date := sysdate;
    :new.last_updated_by := osuser;
    :new.last_updated_date := sysdate;
    --insert into audit_trail values(:new.created_by,:new.creation_date,:new.last_updated_by,:new.last_updated_date,'Insert');
    commit;
  END IF;
  IF UPDATING THEN
    :new.last_updated_by := osuser;
    :new.last_updated_date := sysdate;
    --update audit_trail set last_updated_by=:new.last_updated_by,and last_updated_date=:new.last_updated_date,and action='update';
    commit;
  END IF;
  IF DELETING THEN
  NULL;
    --:new.last_updated_by := osuser;
    --:new.last_updated_date := sysdate;
    --:old_val := old.mgr_id;
    --:new_val := new.mgr_id;
    --update audit_trail set last_updated_by=:new.last_updated_by, and last_updated_date=:new.last_updated_date,and action='Insert';
    --insert into audit_trail values(osuser,sysdate,'','','Delete',:old_val,:new_val);
    commit;
    END IF;
EXCEPTION
  WHEN OTHERS THEN
    -- raise error if we cannot set auditing fields
    raise_application_error(-20505, 'audit_crud_coll_action_log trigger
    failed.  Rolling back.'  || SQLERRM);
    rollback;
end upsert;
/

create table anand(id number, name varchar2(100));

create table logging_anand(id number, old_name varchar2(100),new_name varchar2(100), modified date , operation varchar2(10), user_name varchar2(50));


create or replace trigger log_anand
before insert or update or delete on anand FOR EACH ROW
DECLARE
USER_NAME VARCHAR2(20);
begin

SELECT USER INTO USER_NAME FROM DUAL;

if INSERTING THEN
INSERT INTO logging_anand VALUES(:NEW.id,'',:NEW.name,sysdate,'INSERT',USER_NAME);
elsif UPDATING THEN
INSERT INTO logging_anand VALUES(:NEW.id,:OLD.name,:NEW.name,sysdate,'UPDATE',USER_NAME);
elsif DELETING THEN
INSERT INTO logging_anand VALUES(:OLD.id,:OLD.name,'',sysdate,'DELETE',USER_NAME);
END IF;
end;
/

SHOW ERRORS;


INSERT INTO ANAND VALUES(1,'Anand');


update anand set name='Ved' where id=1;

delete from anand;

select * from anand;

select * from logging_ANAND

commit;