3

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.

4 Answers 4

9

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.

3

Answering a 10 year old question may seem useless. But as the web doesn't forget, I - facing the same challenge as Paolo - found this thread. Others in the same situation may appreciate an update, containing the result I came up after finishing searching the web.

The OP in 2012 set the tag oracle-10g. Oracle 11 (released in 2007) introduced the compound trigger. To prevent an insert to manifest itself in the table after the commit, you only need a single compound trigger.


CREATE TABLE MY_TEST (
  id   number(3,0)   not null
);

CREATE OR REPLACE TRIGGER prevent_certain_inserts
FOR INSERT
ON MY_TEST
-- Restrict the trigger to react only to specific ids to be inserted
-- Could instead be done within one of the AFTER blocks (restriction
-- to store only specific ids or to delete only datasets with specific
-- ids), but then the trigger will get active more often without the
-- need to do anything if a non-specific id is inserted.
WHEN ( NEW.ID between 203 and 206 )
COMPOUND TRIGGER

  -- Set up data structure for storing the id(s) of the inserted dataset(s)
  TYPE r_mytest_type IS RECORD ( id my_test.id%TYPE );
  TYPE t_mytest_type IS TABLE OF r_mytest_type INDEX BY PLS_INTEGER;    
  t_mytest t_mytest_type; 
  
  AFTER EACH ROW IS
  BEGIN
    -- Output for demonstration purposes only
    dbms_output.put_line('--|Trigger activated - AER part|----------------');
    dbms_output.put_line('Storing id ' || :NEW.id);
    -- Store the id of the inserted dataset(s)
    t_mytest(t_mytest.COUNT + 1).id := :NEW.id;
  END AFTER EACH ROW;
  
  AFTER STATEMENT IS    
  BEGIN      
    FOR indx IN 1 .. t_mytest.COUNT
    LOOP                                      
      -- Output for demonstration purposes only
      dbms_output.put_line('--|Trigger activated - AS part|-----------------');
      dbms_output.put_line('Deleting dataset(s) containing the stored id ' || t_mytest(indx).id);
      -- Be aware, that also datasets with the specific ids that were inserted
      -- into the table __before__ the trigger being active will be deleted. 
      -- If needed, make sure (i.e. by using a matching primary key constraint
      -- also represented in the triggers data structure, and a delete statement
      -- addressing the pk-based index) to delete only the rows inserted by the
      -- insert-statement.
      delete from my_test where id = t_mytest(indx).id;
    END LOOP;    
  END AFTER STATEMENT;    

END prevent_certain_inserts;
/

Now let's insert data

set serveroutput on

-- Test with independant inserts
truncate table my_test;
BEGIN
  FOR a in 200..209
  LOOP
    dbms_output.put_line('================================================');
    dbms_output.put_line('Inserting my_test-dataset with id ' || a);
    insert into my_test values(a);
  END LOOP;
END;
/


Output

================================================
Inserting my_test-dataset with id 200
================================================
Inserting my_test-dataset with id 201
================================================
Inserting my_test-dataset with id 202
================================================
Inserting my_test-dataset with id 203
--|Trigger activated - AER part|----------------
Storing id 203
--|Trigger activated - AS part|-----------------
Deleting dataset(s) containing the stored id 203
================================================
Inserting my_test-dataset with id 204
--|Trigger activated - AER part|----------------
Storing id 204
--|Trigger activated - AS part|-----------------
Deleting dataset(s) containing the stored id 204
================================================
Inserting my_test-dataset with id 205
--|Trigger activated - AER part|----------------
Storing id 205
--|Trigger activated - AS part|-----------------
Deleting dataset(s) containing the stored id 205
================================================
Inserting my_test-dataset with id 206
--|Trigger activated - AER part|----------------
Storing id 206
--|Trigger activated - AS part|-----------------
Deleting dataset(s) containing the stored id 206
================================================
Inserting my_test-dataset with id 207
================================================
Inserting my_test-dataset with id 208
================================================
Inserting my_test-dataset with id 209

PL/SQL procedure successfully completed.



select * from my_test;

        ID
----------
       200
       201
       202
       207
       208
       209

6 rows selected.

The datasets with the ids 203 to 206 were still inserted, but were deleted immediatly afterward in the transaction.

Another way of inserting

-- Test with an single 'insert into'-statement from a multi-dataset-source
truncate table my_test;

insert into my_test
WITH data as (
select 200 id from dual union
select 201 id from dual union
select 202 id from dual union
select 203 id from dual union
select 204 id from dual union
select 205 id from dual union
select 206 id from dual union
select 207 id from dual union
select 208 id from dual union
select 209 id from dual )
select id from data;

--|Trigger activated - AER part|----------------
Storing id 203
--|Trigger activated - AER part|----------------
Storing id 204
--|Trigger activated - AER part|----------------
Storing id 205
--|Trigger activated - AER part|----------------
Storing id 206
--|Trigger activated - AS part|-----------------
Deleting dataset(s) containing the stored id 203
--|Trigger activated - AS part|-----------------
Deleting dataset(s) containing the stored id 204
--|Trigger activated - AS part|-----------------
Deleting dataset(s) containing the stored id 205
--|Trigger activated - AS part|-----------------
Deleting dataset(s) containing the stored id 206

10 rows created.


select * from my_test;

        ID
----------
       200
       201
       202
       207
       208
       209

6 rows selected.
0

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;
0

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

Oracle Virtual Private Database (VPD) enables you to create security policies to control database access at the row and column level. Essentially, Oracle Virtual Private Database adds a dynamic WHERE clause to a SQL statement that is issued against the table, view, or synonym to which an Oracle Virtual Private Database security policy was applied.

Oracle Virtual Private Database enforces security, to a fine level of granularity, directly on database tables, views, or synonyms. Because you attach security policies directly to these database objects, and the policies are automatically applied whenever a user accesses data, there is no way to bypass security.

0

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.