CODEWITHSUNDEEP
postgresql
user3602115
user3602115

Reputation: 39

How to create a trigger that checks values?

I need help with my trigger. For example i have Query like this

INSERT INTO test(id_users,id_type,id_color) VALUES(1,3,4);

in my data base i have table with name: test

ghost, id, id_users, id_type, id_color

and i need before insert or update to check

Create trigger
Begin
select id from test where ghost = false and id_users = 26 and id_type = 3
if NO execute INSERT
if YEST exit with no action
END

How can i creat this trigger ?

Upvotes: 1

Views: 789

Answers (2)

Maxim Sloyko
Maxim Sloyko

Reputation: 15876

First, you need to create a trigger function and then the trigger, based on it:

CREATE OR REPLACE FUNCTION my_trigger_function() RETURNS TRIGGER AS $BODY$ BEGIN
IF EXISTS (SELECT id FROM test WHERE ghost = false AND id_users = 26 AND id_type = 3) THEN
  return NEW;
ELSE
  return NULL;
END IF; END; $BODY$ LANGUAGE 'plpgsql';

Then, you create trigger based on this function:

CREATE TRIGGER t_my_trigger BEFORE INSERT ON test FOR EACH ROW EXECUTE PROCEDURE my_trigger_function();

For more on triggers, see postgres docs.

Upvotes: 0

Denis de Bernardy
Denis de Bernardy

Reputation: 78463

There are two ways, depending on how you want to manage the problem.

If you wish to silence it, use a before trigger and return null:

create function ignore_invalid_row() returns trigger as $$
begin
  if not exists(
     select 1
       from test
      where not ghost
        and id_users = new.id_users
        and id_type = new.id_type
     )
  then
    return null;
  end if;

  return new;
end;
$$ language plpgsql;

create trigger ignore_invalid_row_tg before insert on test
for each row execute procedure ignore_invalid_row();

If you wish to raise it, use a constraint trigger and raise an exception:

create function reject_invalid_row() returns trigger as $$
begin
  if not exists(
     select 1
       from test
      where not ghost
        and id_users = new.id_users
        and id_type = new.id_type
     )
  then
    raise exception 'message';
  end if;

  return null;
end;
$$ language plpgsql;

create constraint trigger reject_invalid_row_tg after insert on test
for each row execute procedure reject_invalid_row();

http://www.postgresql.org/docs/current/static/sql-createtrigger.html

Upvotes: 1

Related Questions