Oracle - prevent insert of record with trigger

The triger fires when the row is 'inserted'.  There really isn't a way to pretend the insert didn't happen.  You have some control over this if you created a view on the table and used an instead of trigger.

I'm not sure why you would want to fake the system into pretending an insert happened.

If you do not want the calling application to 'fail', what I've done is create an insert_row procedure and not let the applications perform their own insert.  They can only call the procedure.

Then I can control whatever I want.

you could wrap the table inside a view and use an "instead of trigger" where the replacement action is simply NULL;

Like slightwv I would also prevent the INSERT from happening in the first place.

When the data is inserted from a file, than just add a WHERE ABC.NAME <> 'BAD' clause to filter the bad records out of the set, when using a form, Than test the field and issue a pop-up to warn that the record can't be inserted with the name = 'bad'

the instead of trigger might look something like this..

first you would rename the abc table to abc_hidden so no operations would occur directly on the table


CREATE VIEW abc
AS
    SELECT * FROM abc_hidden;


CREATE OR REPLACE TRIGGER trg_abc_insert
    INSTEAD OF INSERT
    ON abc_hidden
    FOR EACH ROW
BEGIN
    IF :new.name = 'BAD'
    THEN
        NULL;
    ELSE
        INSERT INTO abc_hidden(name, col2, col3, col4)
             VALUES (:new.name, :new.col2, :new.col3, :new.col4);
    END IF;
END trg_abc_insert;

remember to remove grants from abc_hidden table and apply them to abc view

Dude, do this:

create table abc( name varchar2(3));

alter table abc add constraint name_check check(upper(name) != 'BAD');

insert into abc values('bad');

exec dbms_errlog.create_error_log('ABC');

insert into abc values('bad') log errors into err$_abc reject limit unlimited;

select * from err$_abc;  

Do whatever you wish to do with your error log table "err$_abc". You can keep the rejected records there for a certain period of time for auditing purposes or you can schedule a job to run at a specific interval to grab the rejected row(s) and send you an email or you can simply purge the freaking table -the possibilities are endless.

I like the log errors, but it has some caveats

using the constraint will affect updates as well as inserts.

also, the log errors clause will need to be applied to every insert statement performed anywhere in the application

>> using the constraint will affect updates as well as inserts.

It works with updates as well as with merge statements:

update abc set name = 'bad' log errors into err$_abc reject limit unlimited;

merge into abc using( select 1 id,'ban' name from dual) x
on(abc.id = x.id)
when matched then
update set
abc.name = x.name
when not matched then
insert
values(x.id,x.name)
log errors into err$_abc reject limit unlimited;

>> also, the log errors clause will need to be applied to every insert statement performed anywhere in the application

Oh well...that's the price to pay for this workaround

>>> It works with updates as well as with merge statements:

right, that's the problem

the restriction stated in the question is inserts

>> the restriction stated in the question is inserts

Yes, I agree with you that the author stated that he/she wants to prevent the word 'BAD' to get inserted in the table, but since he/she also said "I would rather it not be in the table at all" I assumed that he/she doesn't want a record to get updated to 'BAD' either.

Anyway, if you want to update and not insert then create a trigger:

create or replace trigger abc_trigger before insert on abc for each row
begin
if upper(:new.name) = 'BAD' then
raise_application_error(-20100,'too BAD');
end if;
end;
/

insert into abc values(1,'bad') log errors into err$_abc reject limit unlimited;

0 rows inserted.

insert into abc values(1,'ok');

1 rows inserted.

update abc set name = 'bad' log errors into err$_abc reject limit unlimited;

1 rows updated.

select * from abc;

ID NAME
-- ----
 1 bad

Then schedule it:

BEGIN
  BEGIN
    DBMS_SCHEDULER.DROP_PROGRAM('P_CHECK_ERROR_LOGS',force => TRUE);
   EXCEPTION
     WHEN OTHERS THEN
     NULL;
     END;
    -- Create a new Program in disabled status
      DBMS_SCHEDULER.CREATE_PROGRAM (
         program_name => 'P_CHECK_ERROR_LOGS'
        ,program_type => 'STORED_PROCEDURE'
        ,program_action => 'CHECK_ERROR_LOGS'
        ,number_of_arguments => 1
        ,enabled => FALSE
            ,comments => 'Checks for rejected records in the error logs for the specified schema'
    );
    -- Define the program's arguments
      DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (
         program_name => 'P_CHECK_ERROR_LOGS'
        ,argument_position => 1
        ,argument_name => 'p_owner'
        ,argument_type => 'VARCHAR2'
        ,default_value => '<your_schema_goes_here>'
    );      
    -- Enable the Program for scheduling
      DBMS_SCHEDULER.ENABLE (
         name => 'P_CHECK_ERROR_LOGS'
    );
END;
/
BEGIN
   BEGIN
    DBMS_SCHEDULER.DROP_SCHEDULE(
         schedule_name => 'S_FROM_6AM_TO_6PM'
        ,force => TRUE
    );
    EXCEPTION
     WHEN OTHERS THEN
     NULL;
     END;
      DBMS_SCHEDULER.CREATE_SCHEDULE (
         SCHEDULE_NAME => 'S_FROM_6AM_TO_6PM'
        ,START_DATE => timestamp'2012-04-17 17:05:00'
            ,REPEAT_INTERVAL => 'FREQ=MINUTELY; INTERVAL=10; BYHOUR=6,7,8,9,10,11,12,13,14,15,16,17;'
            ,COMMENTS => 'Checks for rejected records in the error logs. Frequency=every 10 minutes (between 6AM and 6PM)'
    );
END;
/
BEGIN

    BEGIN
      DBMS_SCHEDULER.DROP_JOB (
                         job_name => 'J_CHECK_ERROR_LOGS'
                        ,force => TRUE);
     EXCEPTION
     WHEN OTHERS THEN
     NULL;
     END;

      DBMS_SCHEDULER.CREATE_JOB (
             job_name => 'J_CHECK_ERROR_LOGS'
            ,program_name => 'P_CHECK_ERROR_LOGS'
            ,schedule_name => 'S_FROM_6AM_TO_6PM'
            ,enabled => TRUE
            ,comments => 'Checks for rejected records in the error logs for a specific schema'
    );
END;
/

SELECT * FROM DBA_SCHEDULER_JOBS

I was looking at the answers which were provided.

I think I may have confused everyone.  Here is how I am hoping it will work.  An application will send in inserts with the following results.

insert into abc name('GOOD'); --goes into table
insert into abc name('BAD'); --does not go into table, does not raise application, and do not care about keeping a record of the BAD record in another table

Is this possible?

Thanks

yes,  we understand and it IS possible, but only if you add some extra steps or change the object itself to hide it.

there is no way to simply have the insert to the table (without modification) just ignore errors


review the answers above and determine which would work best

A workaround could be to use in your application a view where 'BAD' is excluded instead of using the table directly.
This will allow the delete of the 'BAD' records to be scheduled e.g. once a day, or you just leave the 'BAD' records in the table, allowing you to get stats on how many 'BAD' records are inserted...

The biggest problem is that I do not have access to the application to prevent input or inserts I don't want. My only way to prevent the inserts is with a trigger. I wish I had access to the application, but that will not happen.

So my "view workaround" could be a possible solution....

Unfortunately, no.  The view solution will not work either.

why won't views work?  Not as ggzfab mentioned, but as shown previously with the instead of trigger?