主页 / user-154216

Evgeny Nozdrev's questions

Martin Hope
Evgeny Nozdrev
Asked: 2018-07-14 04:17:32 +0800 CST
  • 4

MS SQL 中的解决方案

MS SQL 触发器函数具有存储受操作影响的所有行的系统表deleted。inserted您可以计算更新的行数：

set @updatedCount = (select count(*) from deleted)

或找出最小值：

set @updatedMinimumCol1 = (select min(col1) from deleted)

PostgreSQL 的问题

对于FOR EACH ROW触发器，我可以使用 OLD 和 NEW 系统记录，但每次调用触发器时它们只存储 1 行。触发器的调用是分开的，所以如果用户更新 10 行，触发器将被调用 10 次，但每次我只能知道 1 个当前行，而不是全部 10 行。

因为FOR EACH STATEMENT我根本不知道任何访问更新行的机制。我使用 PostgreSQL v9.6，OLD TABLE并NEW TABLE在 v10 中引入。

PostgreSQL 不允许在语句级触发器中引用旧表和新表，即包含所有旧和/或新行的表，这些表由 SQL 标准中的 OLD TABLE 和 NEW TABLE 子句引用。

尝试使用 transaction_timestamp() 附加列

我可以在主表中添加特殊列，DEFAULT transaction_timestamp()然后使用它来区分刚刚更新的行与其他行，但这不是解决方案，因为多个INSERTs/UPDATEs可以在一个事务中并且它们将具有相同的事务时间戳。可能我可以在每个语句之后清除触发器中的这个时间戳列以避免这个问题，但是如果这样的清除将再次发出更新触发器，如何做到这一点 - 将是无限更新触发器调用。

所以，这次尝试失败了。

PostgreSQL 中的错误解决方案

我知道的唯一方法是：

首先，使用FOR EACH ROW触发器来收集当前统计信息（最小值和计数），例如聚合函数。我使用临时表在调用之间存储它（此触发器每行调用 1 次）。但是我们不会知道哪一行是最后一行（何时使用此统计信息）。

CREATE TEMP TABLE IF NOT EXISTS _stats (
  _current_min   int,
  _current_count int
) ON COMMIT DROP;

IF EXISTS(SELECT 1 FROM _stats LIMIT 1) THEN
  --Current row is not first, there is statistics for previous rows.
  UPDATE _stats
  SET _current_min   = (CASE WHEN NEW.col1 < _current_min THEN NEW.col1
                        ELSE _current_min END)
    , _current_count = _current_count + 1;
ELSE
  --There is no stats because current row is first for this INSERT/UPDATE
  INSERT INTO _stats (_current_min, _current_count)
  VALUES (NEW.col1, 1);
END IF;

其次，使用FOR EACH STATEMENT触发器来使用收集的统计信息。不要忘记清除临时表（如果用户将在一个事务中运行多个插入/更新，旧的统计信息将保留在临时表中并破坏所有接下来的计算！）。

对于更复杂的任务，我们可以创建临时表inserted，deleted方法与_stats.

解决方法

在 PostgreSQL 中，我们可以对 INSERT/UPDATE/DELETE 使用 RETURNING 子句来获取受操作影响的所有行的新值。然后我们可以使用它们进行操作，但是每个带有 INSERT/UPDATE 的函数都必须实现这项技术 ===> 1. 带有这种 INSERT/UPDATE 的函数中的附加代码 - RETURNING 的重复；2.我们可以忘记将此类技术应用于新功能；3. 数据将被破坏，因为不会自动调用所需的操作（就像触发器一样）。

问题

也许，您知道访问受 INSERT/UPDATE 影响的所有行的更好方法？

postgresql trigger