在提交插入之前，如何编写更新同一表中的行的触发器？

Question

我有一个表在我的数据库称为股票价格历史，它跟踪价格变化的股票项目，并包含以下feilds：

• 股票ID

Datestart默认为CURRENT_TIMESTAMP，Dateend默认为无，就像没有Dateend值一样，那么该行中的价格就是该股票的当前价格。

现在，我如何（我认为这是通过触发器）使它在我为特定股票项目插入新行时，更新该股票项目的最后一行（即与该股票项目匹配的行，其中DateEnd设置为NULL），以匹配刚刚为插入新行提供的DateStart值，如下所示：

这是表格的初始状态：

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

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

>

1,4.992013-11-20 12:00:00，空

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

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

我认为这将通过插入前触发器完成，但我很可能错了。

Answer 1

根据文件：

在存储的函数或触发器中，不允许修改调用该函数或触发器的语句已在使用（用于读取或写入）的表。

在插入后，您始终可以从插入位置进行更新。

要更新它，你可以做：

UPDATE stockpricehistory s
INNER JOIN (
  SELECT stockID,
    MAX(dateStart) AS maxDate
  FROM stockpricehistory
  WHERE StockID = 1
    AND DateEnd IS NULL
  ) a ON s.StockID = a.StockID    
SET s.dateEnd = a.maxDate
WHERE s.DateEnd IS NULL
  AND s.dateStart != a.maxDate

这是为了让您更新其中一个dateEnd为NULL的行，而不是最大值（dateStart）的行。

希望有帮助。

Answer 2

正如@Filipe Silva所说，您不能修改在该表上调用的触发器中的表。

你可以通过为股票和股票价格历史设置单独的表格来解决这个问题，这在任何情况下都可能是一个好主意。stock表为每个库存项目保存一行及其当前价格，当插入或更新stock中的行时，该表上的触发器将维护stockpricehistory中的记录。

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;