4

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?

7

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 */
  • is it better to return null or return OLD.* ? edit: my mistake insert does not have OLD , update has. – simplexio Sep 12 '14 at 8:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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