truncate table output
/
------------------------------------------------------------------------------------------
-- Creating a trigger
--   An improved version that will only log data values that changed
------------------------------------------------------------------------------------------
CREATE or replace TRIGGER log_update BEFORE INSERT OR UPDATE OR DELETE 
    ON student 
    FOR EACH ROW 
DECLARE
    v_id number;
BEGIN
    IF :OLD.student_id is not null
        THEN v_id := :OLD.student_id;
        ELSE v_id := :NEW.student_id;
    END IF;
    
    If (:OLD.student_id is null  OR :NEW.student_id is null) then
	insert into output values('id-' || v_id || ' changed from '|| :OLD.student_id ||' to '|| :NEW.student_id);
    end if;
    If (:OLD.fname != :NEW.fname OR :OLD.fname is null OR :NEW.fname is null) then
	insert into output values('id-' || v_id || ' fname changed from '|| :OLD.fname ||' to '|| :NEW.fname);
    end if;
    If (:OLD.lname != :NEW.lname OR :OLD.lname is null OR :NEW.lname is null) then
	insert into output values('id-' || v_id || ' lname changed from '|| :OLD.lname ||' to '|| :NEW.lname);
    end if;
    If (:OLD.ssn != :NEW.ssn OR :OLD.ssn is null OR :NEW.ssn is null) then
	insert into output values('id-' || v_id || ' ssn   changed from '|| :OLD.ssn   ||' to '|| :NEW.ssn);
    end if;
    If (:OLD.sex != :NEW.sex OR :OLD.sex is null OR :NEW.sex is null) then
	insert into output values('id-' || v_id || ' sex   changed from '|| :OLD.sex   ||' to '|| :NEW.sex);
    end if;
END;
/

insert into student values(33,'Potter','Harry','123456789','M')
/

update student set ssn='123-12-1235' where student_id=33
/

delete from student where student_id=33
/

select * from output
/

drop trigger log_update