Update values of two tables with use of trigger?

[mrasad]

Can you create a trigger that when an item on one table in updated, then values in another table are also updated.
For example, in a table called loans where I have a field called due date and return date. In another table called member, I have a 2 fields called late and bared. When the loan table is updated and the return date is greater then the due date, I want the late field in member to be incremented by one (with the max of 3). Once the late field in member reaches 3, then the field should = yes.

Can that be done with a trigger? Can all be done in one trigger on the loan table? Or will the last bit (Once the late field in member reaches 3, then the field should = yes) be done differently?

Any help would be most greatly received

Asad

 

[SantaMufasa]

Mr. Asad,

A single trigger on one table can insert, update, or delete against as many other tables as you wish, without special considerations, provided that the insert/update/delete does not occur against the same table either directly or recursively (meaning, for example, that trigger on Table_A inserts into Table_B, which has a trigger that inserts into Table_A).

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:55 (01Jan04) GMT, 12:55 (01Jan04) Mountain Time)

 

[mrasad]

If I want to create a trigger that updates a field in anoother table, how do I ref that?

eg. my two tables are below;

create table loan
(LoanID varchar2(6) PRIMARY KEY,
Issue_Date date not null,
Due_Date date not null,
Return_Date date,
Item_ID REFERENCES AV_Item,
Bookno REFERENCES Book,
MemberID REFERENCES Member);

&

CREATE TABLE MEMBER
(MemberID number(7) primary key,
no_of_late number(1)
Bared varchar(3),
); ....

The trigger needs to be placed on the loan table, on insert or update. If the return date is greater then the due date the trigger should cause the values of no_of_late to be increased by one. Then once no_of_late reaches 3 the bared field should = yes.

I am able to create simple triggers that effect one table but I'm not sure how I would create a trigger to update another table. Do you have any sample code I could work with?

Asad