如何在提交插入之前编写触发器以更新同一表中的行？ [英] How can I write a trigger that updates rows in the same table, before the insert is commited?

问题描述

我的数据库中有一个名为stockpricehistory的表，该表跟踪股票的价格变化，并包含以下字段：

I have a table in my database called stockpricehistory, which tracks price changes for items of stock, and contains the following feilds:

• 股票编号


• 价格


• DateStart（当引入特定价格时）


• DateEnd（特定时间）价格已停止使用）。

• Stock ID
• Price
• DateStart (when that particular price was introduced)
• DateEnd(When that particular price stopped being used).

DateStart默认为CURRENT_TIMESTAMP，而DateEnd默认为空，就像没有DateEnd值一样，则该行的价格为

DateStart defaults to CURRENT_TIMESTAMP, and DateEnd defaults to nothing, as if there is no DateEnd value, then the price in that row is the current one, for that item of stock.

现在，如何（假设它是通过触发器）做到这一点的，以便每当我为特定行插入新行时库存项，它将更新该库存项的最新当前行（即，与该库存项匹配的行，其中DateEnd设置为NULL）以匹配刚刚为要插入的新行提供的DateStart值，如下所示：

Now, how can I (I presume it's via a trigger) make it so that whenever I insert a new row for a particular item of stock, it updates the last current row for that item of stock (i.e. the row that matches that item of stock, where DateEnd is set to NULL) to match the DateStart value just given for the new row to be inserted, as follows:

这是表格的初始状态：

1. 1,2.99 ，2013-11-19 18：49：24，NULL

然后，当为该库存商品插入新行时（ ID 1），结果如下：

Then, when the new row is inserted for that item of Stock (ID 1), this is the result:

1. 1,4.99,2013-11-20 12：00：00，NULL

1. 1,4.99,2013-11-20 12:00:00,NULL

1,2.99,2013-11-19 18：49：24,2013-11-20 12:00:00

1,2.99,2013-11-19 18:49:24,2013-11-20 12:00:00

理论上，它只需要更新一行，因为每个股票ID仅一行没有结束日期，即具有当前价格的那一行。

In theory, it should only need to update one row, as only one row for each stock ID would be without an end date, which would be the row with the current price.

我想这样做就可以了通过INSERT触发器之前，但是我很可能是错误的。

I presume it would be done via a BEFORE INSERT trigger, but I'm most likely wrong.

推荐答案

正如@Filipe Silva所说，您无法修改

As @Filipe Silva said, you can't modify a table in a trigger that is invoked on that table.

您可以通过为股票和股价历史记录使用单独的表来解决此问题，这很可能是无论如何都是个好主意。 股票表每个股票项及其当前价格保存一行，并且该表上的触发器维护股票价格历史记录插入或更新库存中的行。

You can work around this by having separate tables for the stock and for the stock price history, which is probably a good idea in any case. The stock table holds one row per stock item, along with its current price, and triggers on that table maintain the records in stockpricehistory as rows in stock are inserted or updated.

http://sqlfiddle.com/#!2/55c626/1

create table stock (
  stockId int primary key,
  price numeric(5, 2));

create table stockpricehistory (
  stockId int,
  price numeric(5,2),
  dateStart datetime,
  dateEnd datetime);

create trigger t_si before insert on stock
for each row
  insert into stockpricehistory values (new.stockId, new.price, current_timestamp, null);

create trigger t_su before update on stock
for each row begin
  update 
    stockpricehistory 
  set
    dateEnd = current_timestamp
  where
    stockId = new.stockId and
    dateEnd is null;
  insert into stockpricehistory values (new.stockId, new.price, current_timestamp, null);
end;

这篇关于如何在提交插入之前编写触发器以更新同一表中的行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！