I have the following 4 tables:
All of them have 4 columns in common that I want to auto-populate before an insert or update.
At first the simple
insert trigger was giving me an infinite recursion error.
After researching for a while I found out that using views and an
instead of trigger could be a better solution. Now when I insert a row with
prerequisite it gives an error that
CREATED_BY can't be null. However, the values should have been automatically inserted by the trigger.
I've been working on it for a few hours now, but had no luck.
The course table is defined as
CREATE TABLE COURSE ( COURSE_NO NUMBER(8, 0) NOT NULL , DESCRIPTION VARCHAR2(50 BYTE) NOT NULL , COST NUMBER(9, 2) , PREREQUISITE NUMBER(8, 0) , CREATED_BY VARCHAR2(30 BYTE) NOT NULL , CREATED_DATE DATE NOT NULL , MODIFIED_BY VARCHAR2(30 BYTE) NOT NULL , MODIFIED_DATE DATE NOT NULL )
The view for course is defined as
CREATE VIEW v_course AS SELECT CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE FROM COURSE
The instead of trigger is as follows
create or replace TRIGGER course_trRg INSTEAD OF INSERT ON v_course FOR EACH ROW BEGIN INSERT INTO course ( created_by, created_date, modified_by, modified_date ) VALUES ( user, sysdate, user, sysdate ); END;
This is how I'm trying to insert
INSERT INTO v_course ( course_no, description, cost, prerequisite ) VALUES ( 1231, 'desc', 1, 1 );
All the tables have the columns
modified_date in common.