postgresql - postgresql中的更新触发器后无法按预期工作

在postgresql中创建了下面的触发器（执行与下面代码中定义的sqlserver触发器相同的逻辑）

CREATE TABLE IF NOT EXISTS lookup_dbo.finlstatassetdesignation(
    finlstatassetdesignation CHAR(10) NOT NULL,
    finlstatassetdesignationdesc VARCHAR(50) NOT NULL,
    updoperation NUMERIC(5,0) NOT NULL DEFAULT (0),
    upddate TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT CLOCK_TIMESTAMP()
);

CREATE OR REPLACE FUNCTION TR_FinlStatAssetDesignation_U_TrFunc()
RETURNS TRIGGER LANGUAGE  plpgsql
   AS $$
   DECLARE
   AtDateTime  TIMESTAMP;
   SWV_error INTEGER;
   SWV_RowCount INTEGER;
BEGIN
   SWV_error := 0;
   GET DIAGNOSTICS SWV_RowCount = ROW_COUNT;
   IF (SWV_RowCount = 0) then
      RETURN NULL;
   end if;
   AtDateTime := LOCALTIMESTAMP;

   if OLD.FinlStatAssetDesignation IS DISTINCT FROM NEW.FinlStatAssetDesignation then

      RAISE EXCEPTION 'Invalid attempt to update OID FinlStatAssetDesignation in FinlStatAssetDesignation';
      -- Rollback
      RETURN NULL;
   end if;

   if not OLD.UpdDate IS DISTINCT FROM NEW.UpdDate then

      SWV_error := 0;
      begin
         UPDATE lookup_dbo.finlstatassetdesignation
         SET UpdDate = AtDateTime
         WHERE a.FinlStatAssetDesignation = NEW.FinlStatAssetDesignation;
         EXCEPTION
         WHEN OTHERS
         THEN
            SWV_error := -1;
            RETURN NULL;
      end;
      if SWV_error <> 0 then

         -- RollBack
         RETURN NULL;
      end if;

      SWV_error := 0;
   end if;

   RETURN NULL;
   END; $$;

CREATE Trigger tr_finlstatassetdesignation_u
    AFTER Update on lookup_dbo.finlstatassetdesignation FOR EACH ROW
    EXECUTE PROCEDURE lookup_dbo.tr_finlstatassetdesignation_u_trfunc();

SQL Server原始触发器代码：-

-- Add Update Trigger to FinlStatAssetDesignation
CREATE Trigger TR_FinlStatAssetDesignation_U on FinlStatAssetDesignation for Update NOT FOR REPLICATION as 
    IF (@@RowCount = 0) return
    DECLARE @AtDateTime datetime

    SELECT @AtDateTime = GETDATE()

if Update(FinlStatAssetDesignation)
Begin
    RaisError( 'Invalid attempt to update OID FinlStatAssetDesignation in FinlStatAssetDesignation', 16, 1 )
    Rollback Tran
    return
end


if not Update(UpdDate)
begin
    Update a
    set UpdDate = @AtDateTime
    from FinlStatAssetDesignation a, Inserted i
    where a.FinlStatAssetDesignation = i.FinlStatAssetDesignation
    if @@ERROR<>0
    begin
        RollBack tran
        return/* Execution stops here! */
    end
end
go

在postgresql中转换的触发器，甚至是原来的sqlserver触发器都有两部分…第一部分…在postgresql中转换后似乎可以工作，但是第二部分似乎不工作…请帮助

最佳答案

如果要阻止某些更新并更改更新（或插入）行的值，请不要使用AFTER触发器。使用BEFORE触发器并简单地分配所需的值。另外，不能在AFTER触发器中真正停止更新。
在行级触发器中，检查受影响的行数是完全无用的。如果触发了触发器，则该数字总是1。
如果我正确理解您的意图，您的简化代码应该是：

CREATE OR REPLACE FUNCTION tr_finlstatassetdesignation_u_trfunc()
   RETURNS TRIGGER
   LANGUAGE plpgsql
AS
$$
BEGIN
   if old.finlstatassetdesignation IS DISTINCT FROM new.finlstatassetdesignation then
      RAISE EXCEPTION 'Invalid attempt to update FinlStatAssetDesignation in FinlStatAssetDesignation';
      -- Rollback
      RETURN NULL;
   end if;

   if not old.upddate IS DISTINCT FROM new.upddate then
     new.upddate := clock_timestamp();
   end if;

   -- this is important in a BEFORE trigger!
   RETURN new;
END
$$;

以及以下触发器定义：

CREATE Trigger tr_finlstatassetdesignation_u
    BEFORE Update on lookup_dbo.finlstatassetdesignation
    FOR EACH ROW
    EXECUTE PROCEDURE lookup_dbo.tr_finlstatassetdesignation_u_trfunc();

在线示例：http://rextester.com/EWILW61724

关于postgresql - postgresql中的更新触发器后无法按预期工作，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/52381113/