Help needed in creating trigger

I have couple of questions. I need to create 3 triggers in all.

First trigger works. i've created this trigger under TABLE_1. variables, OUTDATE, INDATE, COUNT_1 already exist. The code is as follows:
--------------------------------------------------------
TRIGGER EMP."CASE1" BEFORE
INSERT
OR UPDATE ON "EMP"."TABLE_1" FOR EACH ROW
DECLARE
C NUMBER;
BEGIN
C:= EMP.case_1 (:OLD.OUTDATE, :OLD.INDATE);
if
((:old.INDATE is not null) and
(:old.OUTDATE is null)) or
(C<0)
then
:new.COUNT_1:=0;
else

:new.COUNT_1:=C;
end if;
end;
--------------------------------------------------------

As for the second trigger, i want to be able to do something very similar, except the 2 dates in the function case_2 will be coming from a TABLE_2 such as RET1, RET2, but COUNT_2 is on TABLE_1. I'm not sure how to call a function with variables from a different table.

--------------------------------------------------------
TRIGGER EMP."CASE1" BEFORE
INSERT
OR UPDATE ON "EMP"."TABLE_1" FOR EACH ROW
DECLARE
C NUMBER;
BEGIN
C:= EMP.case_2 (:OLD.RET1, :OLD.RET2); //RET1 & RET2 ARE IN TABLE_2.
if
((:old.RET1 is not null) and
(:old.RET2 is null)) or
(C<0)
then
:new.COUNT_2:=0; //COUNT_2 IS IN TABLE_1
else

:new.COUNT_2:=C;
end if;
end;
--------------------------------------------------------

As for case 3, i've created a function and called that function into a view. I would like to know if it is possible to call a view into a trigger.

Any help is appreciated.

Thank you.

When the trigger fires on the base table it will automatically affect the data in the dependent tables. If you want specifically for a view define a instead of trigger.

First get rid of all those quotes round names. They force Oracle to treat them as case sensitive and are a real "honey trap" for bugs.



You have not retrieved RET1 & RET2 form anywhere.
You will, I presume, need a select query to fetch them.

Clive

Hi all,
In order to create this trigger, i've created a function to calculate and return business days.
---------------------------------------------------------------------------
FUNCTION CME.CASE_1 (start_date IN CME_ROUT.BFDATE%TYPE, end_date IN CME_ROUT.RETDATE%TYPE)
---------------------------------------------------------------------------

then, i've created a view that calls the above function as follows
---------------------------------------------------------------------------
VIEW CME.TESTCASE_1 (DOC, BUS) AS
SELECT a.docket, SUM(CME.CASE_1(b.BFDATE, b.RETDATE)) AS BUS
from CME.CME_MASTER a, CME.CME_ROUT b
where a.masid = b.masid and
a.closed is not null and
bfdate is not null and
b.retdate > b.bfdate
group by a.docket
---------------------------------------------------------------------------

Since i can't call the above view into a trigger, i'll need to create a procedure and in turn call that procedure into the trigger.

This is what i need to do.
i will need to compare if DOC from the view is equal to the DOC from CME_MASTER table, then if so i would need to put the BUS from the view into field5 in the CME_MASTER table.

this is what i have for procedure, i'm not sure if this is correct nor how to call this procedure into the trigger and make it work.
-------------------------------
procedure CME.CREATE_PROC
AS bus1 number;
begin
select b.bus
into bus1 from CME.CME_MASTER a, CME.TESTCASE_1 b
where a.doc = b.doc;
end;
------------------------------

Please help.
Thank you.