CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsqltriggers
Chris Hadfield
Chris Hadfield

Reputation: 524

How to call a trigger when constraint violation occurs?

I have a situation where users are not allowed to enter a duplicate value. If user tries to add duplicate value, the system saves a details of user in a audit table. Trigger is used for that. My code is below

create or replace trigger tr_add_on_audit_table
before insert on lds_consultant
for each row
declare
uname varchar2(30);
begin
 select username into uname from lds_consultant where username = :NEW.USERNAME;

 if uname <> '' or uname <> null then
  insert into audit_table values(null, null, 'nishan', 'insert', null, null, 'cmd', null, 'LDS_CONSULTANT', 'CONSULTANT_ID',null, null, null);
 end if;
end;

but this code doesn't insert data into audit table.

How can I achieve that?

Upvotes: 2

Views: 596

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

NULL isn't equal to nor different from anything. You should use IS NULL or IS NOT NULL, not <> nor =.

Something like this:

create or replace trigger tr_add_on_audit_table
  before insert on lds_consultant
  for each row
declare
  uname varchar2(30);
begin
 select username 
   into uname 
  from lds_consultant 
  where username = :NEW.USERNAME;

  if uname is not null then          --> this!
    insert into audit_table 
      values(null, null, 'nishan', 'insert', null, null, 'cmd', null, 'LDS_CONSULTANT', 'CONSULTANT_ID',null, null, null);
  end if;
exception
  when no_data_found then
    null;
end;

I included exception handler section in case that SELECT doesn't return anything; if it isn't probable, remove it (or handle it properly; I'm doing nothing (NULL;). Also, handle other exceptions, if necessary.

Also, I'd suggest you to name all columns you're inserting into. Today, you know what value goes where, but in a matter of a month or two you'll forget what is the third NULL value supposed to mean.

Furthermore, you said that user isn't allowed to enter a duplicate value - well, this code won't make it happen.

The simplest option is to create a unique key constraint on the USERNAME column and let Oracle handle duplicates.

If you want to do that yourself, you should e.g.

raise_application_error(-20000, 'Duplicate username is not allowed);

However, that won't save your INSERT into the table as everything will be rolled back. In order to fix that, create a procedure that uses pragma autonomous_transaction and commits insert into the audit table.

Everything would look like this:

create or replace procedure p_audit as
  pragma autonomous_transaction;
begin
  insert into audit_table 
    values(null, null, 'nishan', 'insert', null, null, 'cmd', null, 'LDS_CONSULTANT', 'CONSULTANT_ID',null, null, null);
  commit;
end;
/
create or replace trigger tr_add_on_audit_table
  before insert on lds_consultant
  for each row
declare
  uname varchar2(30);
begin
 select username 
   into uname 
  from lds_consultant 
  where username = :NEW.USERNAME;

  if uname is not null then
    p_audit;
    raise_application_error(-20000, 'Duplicates are not allowed')
  end if;
exception
  when no_data_found then
    null;
end;
/

But, once again, why bother? Uniqueness is the keyword here.

Upvotes: 1

Related Questions