CODEWITHSUNDEEP
triggerssql-updatepostgresql-9.5
Ruby
Ruby

Reputation: 23

Trigger prevent update query to affect it's update in postgreSQL

I have written simple update statement in PostgreSQL:

    UPDATE <schema-name>.<table-name> 
    set column1 = y 
    where column2 = x;

I checked that a select statement with same condition return a row, but when I run it I got the message:

"UPDATE 0

Query returned successfully in 26 msec"

what can be wrong?

EDIT:

I notice that the table has trigger on update, When I disabled this trigger, the update statement worked.

Here is the trigger code:

    BEFORE DELETE OR UPDATE 
    ON <schema-name>.<table-name> 
    FOR EACH ROW
    EXECUTE FUNCTION <schema-name>._isk_to_h_proc();

and the function code:

CREATE OR REPLACE FUNCTION <schema-name>._isk_to_h_proc()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
declare
    v_taarich date;    
begin
    if (TG_OP = 'DELETE') then 
      begin
             v_date = now();
      end;
      else
        v_date = NEW.edit_date;
    end if;
    return null;
  insert into <schema-name>.<table2-name>
  (
   column1,
   column2,
   column3
  )
  values(
    nextval('table2_sequence'),  
    OLD.coulmn1,
    NEW.column2,
    v_date,
  );
end;
$BODY$;

ALTER FUNCTION <schema-name>._isk_to_h_proc
    OWNER TO postgres;

Upvotes: 1

Views: 394

Answers (2)

Ruby
Ruby

Reputation: 23

finally I found the issue. It was because of the "return" clause in the function. return null prevent the insert or the delete. see PostgreSQL documentation

Upvotes: 1

Matthias Cartel
Matthias Cartel

Reputation: 1

Check values compatibility on the where, I join @ a_horse_with_no_name. It's not a PostgreSQL issue.

Upvotes: 0

Related Questions