13

I've got a situation where there are dozens of various triggers in our database that trigger a record being inserted into another (single) table within the database.

I was hoping that while testing our replacement server, that there was some easy way that I could set up a trigger on that single table to effectively ignore all of the other triggers. (rather than haing to remove dozens of triggers and then re-enable them when we go into production).

I was hoping to be able to use an INSTEAD OF INSERT trigger set to a function that did nothing, but you can only use those on views, not tables. I tried a BEFORE INSERT trigger that called RAISE EXCEPTION, but that results in the whole transaction failing, so the modifications to the other tables get rejected as well.

Is there some easy way to ignore inserts to that table, or should I just do an AFTER INSERT that clears the record out afterwards?

1 Answer 1

15

Yes, use a BEFORE INSERT trigger, and return null.

Live example here.

create table stuff (
  stuff_id int primary key,
  thing text
);

create or replace function stuff_inserting() returns trigger language plpgsql as $$
begin

  return null;

end $$;

insert into stuff values (1, 'asdf');

select * from stuff; /* returns 1 row */

create trigger inserting before insert on stuff for each row execute procedure stuff_inserting();

insert into stuff values (2, 'fdsa');

select * from stuff; /* still returns only 1 row */
3
  • is it better to return null or return OLD.* ? edit: my mistake insert does not have OLD , update has. Commented Sep 12, 2014 at 8:09
  • 1
    Will that skip the insert altogether or will that insert null into the column? Commented Apr 6, 2022 at 22:11
  • 3
    @light24bulbs it will skip the insert altogether Commented Aug 2, 2022 at 17:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.