sqlite - Trigger to copy a value from a table to another one

Question

I have a table called "moneyincome"

code integer autoincrement money dobule income_date date

then I've another called "availablecash"

code integer money double

My question is: When I insert a value in my table "moneyincome" I need copy this value and "override" or "repleace" the value in availablecash(into the field "money")as a unique value in that table but beside "add" the new value to the existing value in the availablcash table... I think a trigger would be good- I've been looking for this information in the whole web but I've couldn't got what I need.

I have a table called MoneyIncome which will be filled by adding some data from a java interface.. a java desktop app.

A user will type how much money is incoming and then he has to save that operation.

What I want is: when He save that operation "a trigger" will copy this value to another table called "availablemoney".

This table (availableMoney) it's linked with nothing. It's a free table and neither has a autoinc key because I want just a value.. the last value will be which User added in the table moneyincome replacing the old value in there.

Answer 1

Copying a value from one table to another table is contrary to normalisation as you already have the value readily available via the original table.

However, it may be that you have multiple moneyincome rows and you want the availablecash to reflect the total money available.

e.g. in the moneyincome table you may have something along the lines of :-

• 1 40.00 2018-01-01
• 2 50.00 2018-01-02
• 3 25.00 2018-01-03

and want the balance aka availablecash that would be 115.00.

You don't need another table you can determine this by a query (you'd have to query the other table anyway to get the store balance) e.g.

SELECT sum(money) AS availablecash FROM moneyincome;

However, if you want a TRIGGER; considering that your requirements appear to be contradictory, (you want to replace with a unique value in that table), the following will create a TRIGGER that will insert a new entry into the availablecash table when a new row is inserted into the moneyincome table :-

CREATE TRIGGER IF NOT EXISTS myavailablecashtrigger 
AFTER INSERT ON moneyincome 
BEGIN
    INSERT INTO availablecash VALUES(new.code,new.money);
END;

• myavailablecashtrigger is what the trigger will be called

The result based upon the above 3 rows being inserted into the moneyincome table will be that the availablecash table will be :-

• Note! I realise that this is probably not what you are trying to explain that you want but I can't really understand what you want. Perhaps you should show what you want be providing examples (before and after). I also believe that your design/structure is very likely not suitable for what you want.

Additional

As per your updated requirements then you'd actually need two triggers (unless you initially created the 1 one row in the availablecash table). One that inserts a new row if none exist and then the normal one to update the row.

Here's two triggers :-

-- Normal Trigger (when row already exists in the availablecash table)
CREATE TRIGGER IF NOT EXISTS myavailablecashtrigger 
AFTER INSERT ON moneyincome WHEN (SELECT count() FROM availablecash)
BEGIN
    --UPDATE availablecash SET money = new.money;
    UPDATE availablecash SET money = new.money, code = new.code;
END;

-- Trigger for when availablecash table is empty
CREATE TRIGGER IF NOT EXISTS myavailablecashtrigger_nodata
AFTER INSERT ON moneyincome WHEN (SELECT count() FROM availablecash) = 0
BEGIN
    INSERT INTO availablecash VALUES(new.code,new.money);
END;