First let us Drop the table family from the database:

DROP TABLE HR.FAMILY CASCADE CONSTRAINTS;

Execute this and you will see table dropped.

Now let us create the same table with five additional columns -

FAMILY_ID column is going to be the primary column.

INSERT_DATE,INSERT_USER,UPDATE_DATE, UPDATE_USER are the other four columns that will let us know

who inserted or updated the row.

Run the following statements (learn about constraints such as NOT NULL, Unique etc)

CREATE TABLE HR.FAMILY

(

FAMILY_ID NUMBER NOT NULL,

DAD VARCHAR2(100 BYTE),

MOM VARCHAR2(100 BYTE),

CHILD VARCHAR2(100 BYTE),

CHILD_AGE NUMBER,

EXAM_DATE DATE,

INSERT_DATE DATE,

INSERT_USER VARCHAR2(50 BYTE),

UPDATE_DATE DATE,

UPDATE_USER VARCHAR2(50 BYTE)

ALTER TABLE HR.FAMILY ADD (

CONSTRAINT FAMILY_PK

PRIMARY KEY

(FAMILY_ID));

Now it will show table created.

Let us start creating the trigger. This trigger will fire whenever a row is inserted or updated

in table family. Open a new tab - select pl/sql and use the following trigger and compile.

CREATE OR REPLACE TRIGGER HR.family

BEFORE INSERT OR UPDATE

ON HR.FAMILY

REFERENCING NEW AS New OLD AS Old

FOR EACH ROW

DECLARE

lnum_family_id NUMBER;

lnum_family_id:= 0;

IF INSERTING THEN

SELECT FAMILY_ID_SEQ.NEXTVAL INTO lnum_family_id FROM dual;

:NEW.family_id := lnum_family_id;

:NEW.insert_date := SYSDATE;

:NEW.insert_user := USER;

ELSIF UPDATING THEN

:NEW.update_date := SYSDATE;

:NEW.update_user := USER;

END IF;

EXCEPTION

WHEN OTHERS THEN

-- You can log the error and then re-raise

RAISE;

You should get Compiled with 0 errors and 0 warnings.

This means trigger is created successfully.

Now do the insert statement again. Family_id and who inserted will get populated automatically.

Do the update statement, Now you will see who updated and what time the record was updated.