Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I know that this is an awful thing to do for many reasons, but I am in Get Things Done mode and we have a piece of software, which we can't modify, that writes records to a table. Some of these records, for business reasons, we don't want in this table. I was thinking I could write a trigger that "aborts" any of these rows without throwing an error (so that the application doesn't fail in the front end, we want it to think it did its thing). I am intimately familiar with this application and know it won't cause any ugly side effects, we just can't modify it to behave the way we need it to.

So, is this possible, and if not, what would be another way to go about it? I thought about having some sort of Job that routinely cleans the table for records that we don't want, but I would rather for these records to never be there to begin with so there's no degree of "dirtiness" at any one time in the table.

share|improve this question
up vote 7 down vote accepted

The only way for a trigger on a table to prevent an INSERT operation from completing is to throw an error.

It is, as you have stated, a huge hack, but you could

  • Rename the table
  • Create a view that has the same name as the original table
  • Create an instead of trigger on that view that only does an INSERT on the table if you want the row to be persisted. Otherwise, the trigger would do nothing.

That should work so long as the application isn't doing a MERGE into the table. Of course, it's a hack upon a hack so it's definitely not going to win any awards for clean code.

share|improve this answer

You could also use Virtual Private Database to hide these rows from users before they are cleaned.

share|improve this answer
2  
It would be nice if you added some detail about what Virtual Private Databases are, and how this solves the problem at hand, without the user having to visit a link. – Max Vernon Sep 13 '12 at 13:47

The most simple and easy way to accomplish this is using a temporary table and a statement trigger:

1) Make a temporary table with the Primary key of your records

create global temporary table TMP_ID_NUMBER
(
  id NUMBER(9) not null
)
on commit delete rows;

2) Row Trigger

Insert all the records that you don't want into the temporary table...

create or replace trigger MY_TEST_REMOVE_IDS_TR
  before insert
  on MY_TEST 
  for each row
begin
  if :new.Id in (100, 101, 501) then
    insert into tmp_id_number
      (id)
    values
      (:new.Id);
  end if;
end MY_TEST_REMOVE_IDS_TR;

3) Statement trigger

And remove them again from your base table in the after statement trigger...

create or replace trigger MY_TEST_REMOVE_IDS_STAT_TR
  after insert
  on MY_TEST 
begin
  delete MY_TEST d
   where d.Id in (select t.Id
                  from Tmp_Id_Number t);
end MY_TEST_REMOVE_IDS_STAT_TR;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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