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.
Best Answer
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
andmodified_date
arenot null
since there is logically no modification date or user if a row has just been inserted. If you want to populatemodified_by
andmodified_date
on the initial insert, you can add that to theinserting
branch.