Oracle - prevent insert of record with trigger

vandy02
vandy02 used Ask the Experts™
on
I am trying to prevent an insert of a record in Table ABC when ABC.NAME = 'BAD'.  I have looked at examples of Before Insert triggers but cannot seem to find an asnwer.  

In doing so, I cannot have it raise an error.  I just do not want it to insert.  If there is a way to delete the currently being inserted record after it has been inserted that would work as well, but I would rather it not be in the table at all.

Any help would be appreciated.

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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.
Most Valuable Expert 2011
Top Expert 2012

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

Commented:
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'
Most Valuable Expert 2011
Top Expert 2012

Commented:
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.
Most Valuable Expert 2011
Top Expert 2012

Commented:
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
Most Valuable Expert 2011
Top Expert 2012

Commented:
>>> 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
Here is a proc you can use to monitor the err$ tables, just update the sender and recipient for the email:

CREATE OR REPLACE PROCEDURE CHECK_ERROR_LOGS (p_owner VARCHAR2) IS
  l_count   pls_integer := 0;
  l_subject VARCHAR2(2048);
  l_message VARCHAR2(2048);
  l_errtbl  VARCHAR2(37);
  l_prodtbl VARCHAR2(37);  
  l_stmt    VARCHAR2(58);
  l_error   VARCHAR2(2048);
BEGIN
FOR eRec IN(
            SELECT owner, table_name
            FROM all_tables
            WHERE table_name LIKE 'ERR$\_%' ESCAPE '\'
              AND owner = upper(p_owner)
           ) loop
l_errtbl := eRec.owner||'.'||eRec.table_name;
l_stmt := 'Select count(*) From '||l_errtbl;
EXECUTE IMMEDIATE l_stmt INTO l_count;
  IF l_count > 0 THEN
    FOR pRec IN(
                SELECT owner, table_name FROM all_tables
                WHERE table_name LIKE decode(LENGTH(eRec.table_name),30,substr(eRec.table_name,6)||'%',substr(eRec.table_name,6))
                  AND owner = upper(p_owner)
                ) loop
      l_subject := 'Rejected row(s) exist in error log table: '||l_errtbl;
      l_message := 'Please perform the following steps to get rid of this email:'||utl_tcp.crlf||
                   '1. Correct the error(s) that caused the data to be rejected.'||utl_tcp.crlf||
                   '2. Reload the data into: '||pRec.owner||'.'||pRec.table_name||utl_tcp.crlf||
                   '3. Run the following command: Truncate Table '||l_errtbl;
        UTL_MAIL.SEND(
                                 sender      => '<sender>@<your_company>.com',
                                 recipients  => '<your_name>@<your_company>.com',
                                 cc          => NULL,
                                 bcc         => NULL,
                                 subject     => l_subject,
                                 message     => l_message||utl_tcp.crlf||l_error
                                 );
    END loop;
  END IF;
END loop;
EXCEPTION
WHEN others THEN
  l_subject := 'Message from PL/SQL Procedure';
  l_message := 'CHECK_ERROR_LOGS procedure has failed with the following error:';
  l_error := dbms_utility.format_error_stack||utl_tcp.crlf||dbms_utility.format_error_backtrace;
  UTL_MAIL.SEND(
                     sender      => '<sender>@<your_company>.com',
                     recipients  => '<your_name>@<your_company>.com',
                     cc          => NULL,
                     bcc         => NULL,
                     subject     => l_subject,
                     message     => l_message||utl_tcp.crlf||l_error
                     );
END check_error_logs;
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

Author

Commented:
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
Most Valuable Expert 2011
Top Expert 2012

Commented:
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

Commented:
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...

Author

Commented:
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.

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

Author

Commented:
Unfortunately, no.  The view solution will not work either.
Most Valuable Expert 2011
Top Expert 2012

Commented:
why won't views work?  Not as ggzfab mentioned, but as shown previously with the instead of trigger?
Commented:
When only read access is needed a view will work as substitute for the table.
When updates are needed, than the view can be used to move the 'GOOD' records to another table for update.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial