How to create a trigger on an object (Table), so it can automatically insert primary key value and other information
Few simple as this:
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_IDNUMBER NOT NULL,
MOMVARCHAR2(100 BYTE), CHILD VARCHAR2(100 BYTE),
ALTERTABLE 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
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
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.