Oracle SQL Check Exists Constraint

Question

I have two simple tables in Oracle SQL Developer (Train and Driver) each with a common attribute (driverid). Essentially what i want to do is to not allow any updates on a particular driver tuple in the driver table if their driverid attribute exists in the train table. I have tried to add the following constraint however it throws back an error of 'subquery not allowed here.'

alter table driver add constraint drivcheck CHECK 
 (NOT EXISTS(select driverid from train))

I did a bit of digging around and the general feeling is that this condition should be checked with a trigger so I have tried to create a trigger to do the job but not having a lot of success. The below trigger is what i have come up with already.

create trigger drivcheck4
before update on driver
for each row
begin
declare
cursor dri is
select driverid from train where 'N' IN  
          (select availability 
           from driver 
           inner join train on    driver.driverid=train.driverid
           );
dri2 NUMBER;
begin
open dri;
loop
fetch dri into dri2;
exit when dri%NOTFOUND;
if check (exists (select * from dri2)) THEN
//Throw Error Section
else
//Allow update operation to take place
end if;
end loop;
close dri;
end;

I'm aware the trigger may be performing something different other than as described, but this is just the result of me experimenting. My initial description is what i am trying to achieve. If anyone has any thoughts, I would be very grateful!

Answer 1

Andrew,

Is there any error that you are facing? Something along the following lines should work. If you could post some sample data as well, it would be easy to test.

    create or replace trigger trg_bu_driver2
    before update on driver
    for each row
    declare
      l_cnt number;
    begin
    select count(*)
    into   l_cnt
    from   train
    where  driver_id = :new.driver_id;
    if (l_cnt > 0) then
       raise_application_error (-20001, 'new driver id exists in train table');
    end if;
    end trg_bu_driver2;
    /

testing:

    SQL> select * from driver;

     DRIVER_ID COLUMN2
    ---------- --------------------
            10 driver1

    SQL> select * from train;

     DRIVER_ID   TRAIN_ID COLUMN3
    ---------- ---------- --------------------
            20        100 train1

• Updating to a driver id that does not exist in train table.

    1  update driver
    2  set    driver_id = 30
    3* where  driver_id = 10
  SQL> /
  
  1 row updated.
  
  No issues.
  
  SQL> rollback;
  
  Rollback complete.
  

--updating to a driver id that exists in train table, raises an error.

    SQL> update driver
      2  set    driver_id = 20
      3  where  driver_id = 10
      4  ;
    update driver
           *
    ERROR at line 1:
    ORA-20001: new driver id exists in train table
    ORA-06512: at "DMART_ETL.TRG_BU_DRIVER2", line 9
    ORA-04088: error during execution of trigger 'DMART_ETL.TRG_BU_DRIVER2'