SQLITE-触发将值从表复制到另一个表 [英] SQLITE - Trigger to copy a value from a table to another one

问题描述

我有一个名为 moneyincome的表。

I have a table called "moneyincome"

代码整数自动递增
钱交易
收入日期日期

code integer autoincrement money dobule income_date date

然后我还有一个叫做 availablecash的

then I've another called "availablecash"

代码整数
钱倍数

code integer money double

我的问题是：当我在我的值中插入一个值时表 moneyincome我需要复制此值，然后覆盖或补充可用现金中的值（进入字段 money）作为该表中的唯一值，但是在 add旁边。 availablcash表中现有值的新值...
我认为触发器会很好-我一直在整个网络中寻找此信息，但我没有得到所需的信息。

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.

我会尽力说明这一点。

我有一个表MoneyIncome，将通过从Java界面添加一些数据来填充。Java桌面应用程序。

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.

我想要的是：当他将操作保存为触发器时，会将值复制到另一个称为 availablemoney的表。

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

该表（availableMoney）没有任何链接。这是一个免费的表，并且都没有autoinc键，因为我只想要一个值..最后一个值将是用户在表moneyincome中添加的值，代替其中的旧值。

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.

[西班牙语]

Cuando ingreso un valor en la tabla dineroentrada en su campo dinero ，necesito queéstevalor se copie y reemplace y ademas和 sume。 El Valor del Campo dinero de la tabla有效。 Creo que un triggerseríalo mejor- Pero he estado buscando y no consigo dar con lo que quiero- Gracias este sitio es asombroso

Cuando ingreso un valor en la tabla dineroentrada en su campo "dinero" , necesito que éste valor se copie y reemplace y ademas "sume" el valor del campo "dinero" de la tabla efectivodisponible. Creo que un trigger sería lo mejor- Pero he estado buscando y no consigo dar con lo que quiero- Gracias este sitio es asombroso

推荐答案

将值从一个表复制到另一表与规范化相反，因为您已经可以通过原始表轻松获得该值。

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.

但是，可能是您有多个moneyincome行，并且您希望可用现金反映可用的总金额。

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

例如 moneyincome 表中的
，您可能会遇到以下问题：-

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

，并希望将余额 availablecash 设为115.00。

and want the balance aka availablecash that would be 115.00.

您不需要其他表格，可以通过查询来确定（您可以'd无论如何都要查询另一个表以获取商店余额），例如

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;

但是，如果您想触发；考虑到您的需求似乎是矛盾的（您想在该表中替换为唯一值），下面的代码将创建一个TRIGGER，当新行出现时，它将在 availablecash 表中插入一个新条目插入 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将被称为触发器



根据以上3行插入 moneyincome 表的结果将是 availablecash 表将是：-

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



• 注意！我意识到这可能不是您要解释的内容，但我无法真正理解您想要的内容。也许您应该展示自己想要提供的示例（之前和之后）。我也相信您的设计/结构很可能不适合您想要的。



根据更新后的需求，您实际上将需要两个触发器（除非您最初在availablecash表中创建了1个一行）。

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.

这里有两个触发器：-

-- 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;

这篇关于SQLITE-触发将值从表复制到另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！