asked on

Oracle Trigger

I'm trying to update the values in one table from a trigger in another table. What am I doing wrong?

CREATE OR REPLACE TRIGGER TRG_MEM_BAL
AFTER UPDATE OF DETAIL_DUEDATE, DETAIL_RETURNDATE ON DETAILRENTAL

BEGIN

UPDATE MEMBERSHIP
SET MEMBERSHIP.MEM_BALANCE = DETAILRENTAL.DETAIL_DAILYLATEFEE * DETAILRENTAL.DETAIL_DAYSLATE;

END;

awking00

Can you post a describe of the membership and detailrental tables?

mfinocc

ASKER

MEMBERSHIP
MEM_NUM        NUMBER(8,0)
MEM_FNAME        VARCHAR2(30 BYTE)
MEM_LNAME        VARCHAR2(30 BYTE)
MEM_STREET        VARCHAR2(120 BYTE)
MEM_CITY        VARCHAR2(50 BYTE)
MEM_STATE        CHAR(2 BYTE)
MEM_ZIP        CHAR(5 BYTE)
MEM_BALANCE      NUMBER(10,2)

DETAILRENTAL
RENT_NUM        NUMBER(8,0)
VID_NUM        NUMBER(8,0)
DETAIL_FEE        NUMBER(5,2)
DETAIL_DUEDATE        DATE
DETAIL_RETURNDATE      DATE
DETAIL_DAILYLATEFEE      NUMBER(5,2)
DETAIL_DAYSLATE        NUMBER(3,0)

awking00

There doesn't appear to be any relationship (i.e. no foreign keys) between the two tables, so how do you know which membership account needs an update of the balance?

mfinocc

ASKER

Here's all the table information...
TinyVideo.sql

ASKER CERTIFIED SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mfinocc

ASKER

Excellent!

mfinocc

ASKER

I have posted one more question using this same database. Thanks.