如何在SQLite中使用触发器自动输入经常性金额

Question

关于在SQLite中使用触发器进行自动更新的一个棘手问题。。。

我在这里有一种情况，我需要从特定日期开始，以特定的间隔在表中进行条目。"类别"表中的每个类别都有一个"RecurAmount"，设置为按4个固定间隔之一重复：7天、14天、1个月、1年。（我用整数1,2,3,4表示这些）每个类别都有一个RecurDate，它最初由用户选择。一旦这个日期过去，我需要让应用程序执行以下操作（例如，假设应用程序已经几个月没有打开了，需要刷新）：

1） 从Category中选择所有类别，其中RecurDate为<=日期（"现在"）。。。这些是要刷新的类别。

2） 将从"类别"表中提取的"Costs经常金额、RecurDate，CatKey和经常性值插入以下成本列中：金额ostDateatFK*，AutoExp

3） 然后，我需要能够让SQLite按照"Recur"值确定的间隔，自动将定期间隔日期的金额插入Costs表中。因此，如果其Recur值为"1"，我需要在Costs表中每隔7天输入一系列条目，从RecurDate开始，一直到今天，但不能再继续。

4） 一旦所有类别都应用了截至当前日期的经常性成本单位。。。我需要将一个未来日期添加到Category表中的RecurDate字段中。这将作为下一个自动输入发生之前需要匹配或通过的日期。

以下是我使用的表格："类别"表：

RecurDate       Key      RecurAmount    Recur   RecurInterval
2012-06-26      20       100            1            Weekly
2012-07-18       21        200          1            Weekly
2012-07-10       22        250          3            Monthly
2012-08-10       22        300          3            Monthly
2012-07-16       23        200          2            Fortnightly
2012-06-20       24       100           4             Yearly

"成本"表。这是一个以固定日期间隔接收插入金额的表。假设今天的日期是2012-07-18，当我在很长一段时间后没有启动应用程序时…我需要成本表更新为这样的内容：

CostDate    CatFK       Amount      AutoExp
2012-06-26    20          100       1       
2012-07-03    20          100       1
2012-07-10    20          100       1
2012-07-17    20          100       1
2012-07-18    21          200       1
2012-07-10    22          250       3
2012-07-16    23          200       2
2012-06-20    24          100       4

这是迄今为止我的代码的开始，它试图只完成每周的经常性开支。它显然不起作用。。。

PRAGMA recursive_triggers = on
CREATE TEMPORARY TRIGGER MyTrigger
BEFORE INSERT ON Costs
WHEN new.RecurDate < (select date('now','localtime')) BEGIN
INSERT INTO Costs (CostDate, Amount, CatFK, AutoExp)
VALUES (date(new.RecurDate,'+'||7||' days'), Category.RecurAmount, Category.Key,    Category.Recur;
END
INSERT INTO Costs (CostDate, Amount, CatFK, AutoExp)
SELECT RecurDate, RecurAmount,Key, Recur from Category
WHERE
Recur =1 ;

正如你所看到的，我正在以某种方式使用触发器来实现这一目标，但我离完成这一目标还有很长的路要走。复杂性来自于我正在处理的不同的重复间隔，以及基于这些不同的间隔识别并将未来的"RecurDate"插入Categories表中。我几乎在想，我使用AS3和一个循环在SQLIte之外构建一个自定义语句来计算日期，然后在SQLIte中插入我需要的内容。

我想知道是否有人能给我一些建议，因为这很棘手，而且我不是很好：）

感谢

Marco

Answer 1

我认为主要问题是，在CostDate和今天日期之间，给定每行的RecurDate值，如何生成必要的CostDate值。

我对SQLlite一无所知，但我可以解决SQL server中的问题，所以我将尝试解释所需的逻辑。

我建议分4次进行，每次刷新一次。因此，我将在下面的叙述中假设，我们正在创建新的行，其中重复周期为每7天。您应该能够根据需要为其他周期复制逻辑。

您需要一个从0（=最早的RecurDate）到今天（包括今天）的整数值（天数）临时表。因此，例如，如果最早的日期是142天前，则该表将需要包含值0到142；或者，如果我们可以假设最早可能的RecurDate是1000天前或更短的时间（即接近3年前），我们可以说该表总是需要包含0到1000的值。

为了创建这个，我不确定你需要什么语法，但我会这样做：

    create table Days (interval int);
    insert Days values (0);
    insert Days values (1);
    declare @offset int, @max int;
    select @offset = 2, @max = 1000  /*(1000 per the assumption of a maximum value)*/
    while @offset < @max begin
      insert Days 
      select interval + @offset 
      from Days 
      where interval + @offset <= @max
    end

该表中的数据需要与创建新行的insert语句中的类别交叉连接（即创建插入符号乘积，其中第一个表中的所有行与另一个表中所有行匹配）：

    insert costs (Amount, CostDate, CatFK, AutoExp)
    select c.RecurAmount, c.RecurDate + d.interval, c.Catkey, c.Recur
    from Categories c cross join Days d
    where c.RecurDate < getdate()      /*'now'*/
      and c.Recur = 1
      and c.RecurDate + d.interval <= getdate()
      and d.interval > 0
      and d.interval mod 7 = 0;

因此，我们在成本表中插入类别表列+期间，用于所有类别，其中RecurDate在今天的日期之前，重复间隔为1（每周），并且RecurDate+使用的期间小于或等于今天的日期，并且周期不是0（所以您不会创建日期为=RecurDate的记录），并且用于每个插入的周期可以被7整除。

我假设（或者我读错了）在这个过程运行后，RecurDate将根据每个类别成本中的最新CostDate更新。

希望这有帮助：-）JB