触发将值从表复制到另一个表 [英] 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"

代码整数钱翻倍

我的问题是:当我在表"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.

推荐答案

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

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

，并希望余额可用现金为115.00.

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;

但是，如果您想要触发；考虑到您的需求似乎是矛盾的(您想在该表中替换为唯一值)，下面的代码将创建一个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;

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