Not null constraint using triggers in SQL

Question

I want to implement a not-null constraint on an attribute using a trigger. Here's my code:

create table mytable2(id int);

create or replace function p_fn() returns trigger as $prim_key$
begin
  if (tg_op='insert') then
    if (id is null) then
      raise notice 'ID cannot be null';
      return null;
    end if;
    return new;
  end if;
end;
$prim_key$ language plpgsql;

create trigger prim_key
before insert on mytable2
for each row execute procedure p_fn();

But I get an error saying "control reached end of trigger procedure without RETURN" whenever I try to insert a null value. I tried placing the "return new" statement in the inner IF, but it still gave me the same error. What am I doing wrong?

Why are you trying to use a trigger for this rather than declaring the column datatype as NOT NULL? create table mytable2(id int not null); — Martin Smith
– Martin Smith, Commented Apr 14, 2013 at 8:32
This is part of an assignment. And the question says that it must be implemented using triggers. :) — Naxical
– Naxical, Commented Apr 14, 2013 at 8:34
Wow... what a stupid thing to teach. They could've suggested so many more sensible things to enforce using triggers that wouldn't be much more complex but aren't easily implemented using CHECK or NOT NULL constraints. This is teaching students very bad habits, triggers are what you use when none of the declarative options are capable of solving the problem, not the tool you reach for first. — Craig Ringer
– Craig Ringer, Commented Apr 14, 2013 at 10:44
Not the best thing to teach... but anyhow, both for your clarity and for Postgres, make sure you have a return statement at the bottom of the function. — Wolph
– Wolph, Commented Apr 14, 2013 at 10:47
As for docs about this: postgresql.org/docs/9.1/static/… "The return value of a function cannot be left undefined. If control reaches the end of the top-level block of the function without hitting a RETURN statement, a run-time error will occur. This restriction does not apply to functions with output parameters and functions returning void, however. In those cases a RETURN statement is automatically executed if the top-level block finishes." — Wolph
– Wolph, Commented Apr 14, 2013 at 10:48

Craig Ringer · Accepted Answer · 2013-04-14 12:09:43Z

2

The immediate cause of the problem is that PostgreSQL string comparisons are case sensitive. INSERT is not the same as insert. Try:

IF tg_op = 'INSERT' THEN

Advice

You're only raising a notice. This allows flow of control to continue to the next line in the procedure. You should generally RAISE EXCEPTION to abort execution and roll the transaction back. See RAISE. As it stands, the trigger will cause inserts that do not satisfy the requirement to silently fail, which is generally not what you want.

Additionally, your triggers should usually end with a RAISE EXCEPTION if they're always supposed to return before end of function. That would've helped you see what was going wrong sooner.

edited Apr 14, 2013 at 12:09

answered Apr 14, 2013 at 10:47

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Daniel Vérité Over a year ago

And change if (id is null) to if (NEW.id is null)... otherwise that's also going to fail when that code is actually reached.

Naxical Over a year ago

That did it for me. Changed the case, raised an exception and changed id to new.id - and it worked. Thank you.

marcj · Accepted Answer · 2013-04-15 00:23:42Z

1

I'd add just a couple of things to the good suggestions already made:

make sure you handle the UPDATE case as well
RAISE using the proper error condition. I've given a basic example below - for more formatting options see the docs here.

for clarity, I like to include at least the table name in my trigger name

CREATE OR REPLACE FUNCTION fn_validate_id_trigger() RETURNS TRIGGER AS
$BODY$
BEGIN
  IF (TG_OP IN ('INSERT', 'UPDATE')) THEN
    IF (NEW.id IS NULL) THEN
      RAISE not_null_violation;
    END IF;
  END IF;
  RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER tr_mytable_validate_id
AFTER INSERT OR UPDATE
ON mytable2
FOR EACH ROW EXECUTE PROCEDURE fn_validate_id_trigger();

Update: this is now a CONSTRAINT trigger and fires AFTER insert or update. In first edit, I presented a column-specific trigger (UPDATE OF id). This was problematic as it would not fire if another trigger executed on the table changed column 'id' to null.

Again, this isn't the most efficient way to handle constraints but it's good to know.

edited Apr 15, 2013 at 0:23

answered Apr 14, 2013 at 14:23

marcj

3461 silver badge5 bronze badges

3 Comments

Naxical Over a year ago

And in the trigger, the "OF id" part isn't allowed.

marcj Over a year ago

Thanks Naxical. My original example did not take into account possible changes to the id column by other triggers executing on the table. By changing to an AFTER trigger and dropping 'OF id' it will run after the operation has completed.

Scott Marlowe Over a year ago

If you define the trigger as "on update or insert" then there's no need to check if it's update or insert in the trigger, as that's the only way the trigger gets called.

Collectives™ on Stack Overflow

Not null constraint using triggers in SQL

2 Answers 2

Advice

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Advice

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related