I'm trying to create a trigger that checks the value of a column whenever I have an insert or an update on the table, the value of columnX must be unique:

tableX(ID, ..., columnX)




CREATE or replace TRIGGER tableX_uk
BEFORE INSERT OR UPDATE ON tableX
FOR EACH ROW
BEGIN
if(:new.columnX in (select T.columnX from tableX T)) then
Raise_Application_Error(-20001, 'Already existing');
end if;
End;

It shows that the trigger is created with compiling errors. I couldn't find any error here, can someone help me please ? Thank you !

CodePudding user response：

It shows that the trigger is created with compiling errors. I couldn't find any error here

Errors for stored PL/SQL can be found in user/all/dba_errors. Desktop tools such as SQL Developer, Toad, PL/SQL Developer etc will display them automatically but that's where they get the details from.

DBFiddle

In this case the first error is from

if(:new.columnX in (select t.columnX from tableX t))

which gives

PLS-00405: subquery not allowed in this context

because if x in (select...) isn't valid PL/SQL syntax. You have to do the select as a separate step. Fixing that will give you code that at least compiles, but still isn't ideal:

create or replace trigger tablex_uk_trg
    before insert or update on tablex
    for each row
declare
    l_columnx_check number := 0;
begin
    select count(*) into l_columnx_check
    from   tablex t
    where  t.columnx = :new.columnx
    and    rownum = 1;

    if l_columnx_check > 0 then
        raise_application_error(-20001, 'Value '||:new.columnx||' already exists');
    end if;
end;

It's not ideal because firstly, a unique constraint is a far more efficient and self-documenting way to enforce uniqueness.

create table tablex (columnx number unique);

or better still

create table tablex (columnx number constraint tablex_uk unique);

or if it's the primary key

create table tablex (columnx number constraint tablex_pk primary key);

Now, anyone checking the table definition will see the unique constraint, the optimiser will use it in queries, it has a standard error code ORA-00001: unique constraint (WILLIAM.TABLEX_UK) violated and so on.

Secondly, the update part of the trigger won't work anyway. Oracle won't let a row-level update trigger query its own table:

insert into tablex (columnx) values (1);

update tablex set columnx = 1;

ORA-04091: table WILLIAM.TABLEX is mutating, trigger/function may not see it
ORA-06512: at "WILLIAM.TABLEX_UK_TRG", line 4
ORA-04088: error during execution of trigger 'WILLIAM.TABLEX_UK'

DBFiddle

And in any case the logic is missing some checks, because the update in my example should be valid. But I won't go into how you might fix that because the unique constraint is all you need.