CODEWITHSUNDEEP
sqlfunctionpostgresqltriggers
runit
runit

Reputation: 61

Postgresql trigger funciton seems to prevent insertion of any record

I am a Postgresql newb.

I created a function to check if an INSERT tries to create the same value in two different tables.

    CREATE OR REPLACE FUNCTION check_pids_not_equal()
  RETURNS trigger AS
$BODY$
begin 
if exists (select *
from cards c , echecks e
where c.pid = e.pid)
then
raise NOTICE 'Must have different PID than entry in cards.pid';
end if;
return null;
end
$BODY$
  LANGUAGE plpgsql

I assigned the trigger in this manner:

CREATE TRIGGER check_cards_pids_trigger
  BEFORE INSERT OR UPDATE
  ON cards
  FOR EACH ROW
  EXECUTE PROCEDURE check_pids_not_equal();

Now, whenever I try to do any insert of record on Cards table, I always get INSERT 0 0 with no error messages -- even if the record is clearly a bad record with missing columns or wrong column types.

Any ideas as to the culprit? Also, how do show more error reports?

Upvotes: 3

Views: 3072

Answers (1)

roman
roman

Reputation: 117606

You have to return new if you want to insert record, something like this:

CREATE OR REPLACE FUNCTION check_pids_not_equal()
  RETURNS trigger AS
$BODY$
begin 
    if exists (select * from cards c, echecks e where c.pid = e.pid) then
        raise NOTICE 'Must have different PID than entry in cards.pid';
        return null;
    end if;
    return new;
end
$BODY$
LANGUAGE plpgsql

Upvotes: 2

Related Questions