how to build table relationships in a firebird database?

Question

I am a beginner in Delphi and I have a Firebird database with 2 tables namely masterlist and daily collection. I used Zeos 7.0.3 to access my Firebird database.

My masterlist contains the following columns:

╔══════╦══════╦═════════╦════════╗
║ name ║ date ║ balance ║ status ║
╚══════╩══════╩═════════╩════════╝

My daily collection contains the following columns:

╔══════╦══════╦═════════╦═════════╗
║ date ║ name ║ payment ║ balance ║
╚══════╩══════╩═════════╩═════════╝

I would like to build a relation in which the balance from masterlist will be copied to the balance column of the daily collection, and when I update the column in the daily collection it will also update the content of the masterlist.

Hope this will be considered a good question I have tried very hard to make a useful question.

Answer 1

While I'm pretty sure you should redesign your database at first (and probably read something on SQL basics), you can get what you want with triggers :)

This one inserts row into daily collection with appropriate balance:

CREATE trigger masterlist_ai for masterlist
active after insert position 0
AS
begin
 insert into "daily collection" ( "date", balance)
 values (new."date", new.balance);
end

And this one updates balance in masterlist after updates of daily collection (assuming date is the primary key):

CREATE trigger "daily_collection_au" for "daily collection"
active after update position 0
AS
begin
 update masterlist
 set balance = new.balance
 where "date" = new."date";
end

• Sergei Ousynin: Also you can find better names for "date" (it's a keyword and you'll have to use quotes everywhere).