After update trigger in postgresql not working as expected

Question

Created below trigger in postgresql(to do same logic as of sqlserver trigger defined in below code)

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 original trigger code:-

-- 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  

The converted trigger in postgresql and even original sqlserver one has 2 parts...for first one..it seems working post conversion in postgresql however second part seems not working...please help

Answer 1

If you want to prevent certain updates and want to change the values of the update (or inserted) row, do not use an AFTER trigger. Use a BEFORE trigger and simply assign the value you want. Additionally you can't really stop the UPDATE in an AFTER trigger.

Checking for the number of affected rows is completely useless in a row level trigger. If the trigger is fired that number is always 1.

If I understand your intentions correctly, your simplified code should be:

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
$$;

Together with the following trigger definition:

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

Online example: http://rextester.com/EWILW61724

Answer 2

This is your problem:

GET DIAGNOSTICS SWV_RowCount = ROW_COUNT;
IF (SWV_RowCount = 0) THEN 
   RETURN NULL;
END IF;

Since you execute this at the beginning of the function and there was no prior SQL statement in the function, the value will always be zero, and the trigger will exit immediately.

You seem to assume that ROW_COUNT will contain the number of rows modified in the statement that triggered the function, but that is not the case. It contains the number of rows modified by the last SQL statement in that function itself.

You can just remove this check. The trigger function will be called for each row that is modified, so it will not be called at all if not a single row is modified.

Finally, it is a good habit to RETURN NEW; from a trigger function, unless you have a good reason to prevent the execution of other AFTER UPDATE triggers on that function.