CODEWITHSUNDEEP
sqlpostgresqlplpgsql
user3733929
user3733929

Reputation: 47

How to make update on all records before insert new?

I want when i put new record to table, before insert i would like update old records as ghost (some like disable) and finally add this new one. So I prepared simple trigger function

CREATE OR REPLACE FUNCTION trg_ghost_game_seed_ins_bef()
  RETURNS trigger AS
$$
BEGIN
    UPDATE dice_game_seed SET ghost = true WHERE ghost = false;
RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER ins_up_bef
BEFORE INSERT OR UPDATE ON dice_game_seed
FOR EACH ROW
EXECUTE PROCEDURE trg_ghost_game_seed_ins_bef();

When i tried insert new record i have info

SQL statement "UPDATE dice_game_seed SET ghost = true WHERE ghost = false"
PL/pgSQL function "trg_ghost_game_seed_ins_bef" line 3 at SQL statement

But whats wrong with line 3 ???

Upvotes: 0

Views: 98

Answers (1)

Laurence
Laurence

Reputation: 10976

You can use the pg_trigger_depth() function to work around the infinite recursion:

create or replace function trg_ghost_game_seed_ins_bef()
  returns trigger as
$$
begin
    if (pg_trigger_depth() = 1) then
        update dice_game_seed set ghost = true where ghost = false;
    end if;
    return null;
end
$$ language plpgsql;

create trigger ins_up_bef
before insert or update on dice_game_seed
for each statement
execute procedure trg_ghost_game_seed_ins_bef();

You can also use a statement trigger instead of a row trigger.

Example SQLFiddle

Upvotes: 1

Related Questions