auto populate common columns of all tables using triggers

Question

I have the following 4 tables:

• course
• section
• student
• enrollment

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 course_no, description, cost and 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 created_by, created_date, modified_by, modified_date in common.

Answer 1

You didn't post what you tried for a simple row level trigger so it isn't obvious how you got an infinite recursion error. My assumption is that you tried to insert into the table in your trigger rather than simply setting the column values. Creating a view and an instead of trigger is vastly overcomplicating the problem.

My assumption is that you want something like this. I'm not clear why modified_by and modified_date are not null since there is logically no modification date or user if a row has just been inserted. If you want to populate modified_by and modified_date on the initial insert, you can add that to the inserting branch.

create or replace trigger course_trg
 before insert or update on course
 for each row
declare
begin
 if( inserting )
 then
 :new.created_by := user;
 :new.created_date := sysdate;
 elsif( updating )
 then
 :new.modified_by := user;
 :new.modified_date := sysdate;
 end if;
end;

• Michael Kutz: modified_by is NOT NULL. You need to put the assignments of the modified_* columns outside if the IF .. END IF; block
• Justin Cave: @MichaelKutz - That's why I made the comment that it wasn't obvious to me why those columns were NOT NULL since it doesn't generally make sense to have a modified_date on a column that has never been modified.