將 OLD & NEW 傳遞給函式

我有2個觸發器。triggerA並分別triggerB作用於tableA和的變化tableB。在這兩個觸發器中，我想使用與OLD& NEWvariables相同的函式。我不知道該怎麼做，因為它會拋出錯誤：

OLD hasn't been assigned yet.
The tuple structure of a not-yet-assigned record is indeterminate.

然後我嘗試將 null 分配給一個變數來解決這個問題，它是：

old_v hasn't been assigned yet.

觸發器 1 和 2（這部分在兩個觸發器中都很常見）：

DECLARE 
   added added_scores;
   old_v record;
   new_v record;
BEGIN
   CASE TG_OP
       WHEN 'INSERT' THEN
           old_v = null;
           new_v = NEW;
       WHEN 'UPDATE' THEN
           old_v = OLD;
           new_v = NEW;
       WHEN 'DELETE' THEN
           old_v = OLD;
           new_v = null;
   END CASE;

   select _i_compute_added_scores(TG_OP, old_v, new_v) into added;
       -- some computation, return result

功能 ：

CREATE OR REPLACE FUNCTION public._i_compute_added_scores(
   tg_op_p text,
   old_p anyelement,
   new_p anyelement)
 RETURNS added_scores AS
$BODY$

DECLARE
   new_score integer;
   old_score integer;
   res added_scores;
BEGIN
   CASE TG_OP_p
       WHEN 'INSERT' THEN
           new_score := new_p.score;
           old_score := 0;
       WHEN 'DELETE' THEN
           new_score := 0;
           old_score := old_p.score;
       WHEN 'UPDATE' THEN
           new_score := new_p.score;
           old_score := old_p.score;
   END CASE;

-- some more computation and return

您需要在將其作為參數發送之前分配 RECORD 類型，在您的情況下，一種非常簡單的方法是簡單地將其分配給 NEW 或 OLD，即使它不是必需的，例如：

CASE TG_OP
   WHEN 'INSERT' THEN
       old_v = NEW;
       new_v = NEW;
   WHEN 'UPDATE' THEN
       old_v = OLD;
       new_v = NEW;
   WHEN 'DELETE' THEN
       old_v = OLD;
       new_v = OLD;
END CASE;

請注意，現在使用TG_OP和控制必須讀取/使用的變數非常重要。

引用自：https://dba.stackexchange.com/questions/174146