CODEWITHSUNDEEP
sqloracle-databaseplsqltriggers
F. P.
F. P.

Reputation: 5086

PL/SQL DB triggers - Make insertion fail if a given condition is not met

I have a small test database I'm using for learning SQL.
There's a Duel table, which contains two Pilot foreign keys (the duelists). I want to check if the duelists haven't already "met" before insertion.

Pseudocode:

before insertion on Duel
for each row already in the table
  if ((new_row.numpilot1 = old_row.numpilot1 and new_row.numpilot2 = old_row.numpilot2) OR
      (new_row.numpilot1 = old_row.numpilot2 and new_row.numpilot2 = old_row.numpilot1)
     )
    insertion fails

One other alternative would be

tempnum integer;
select numpilot1 into tempnum from duel 
where (:NEW.numpilot1 = numpilot1 and :NEW.numpilot2 = numpilot2) OR
      (:NEW.numpilot1 = numpilot2 and :NEW.numpilot2 = numpilot1);

if tempnum == null
    fail insertion

What is the PL/SQL (Oracle DBMS) version of this?

Upvotes: 3

Views: 2092

Answers (2)

Justin Cave
Justin Cave

Reputation: 231661

Normally, you wouldn't use a trigger for this sort of requirement. Instead, you'd create a couple of constraints on the table. I would suggest a unique constraint on (numpilot1, numpilot2) along with a check constraint that ensures that numpilot1 < numpilot2.

ALTER TABLE duel
  ADD CONSTRAINT unique_pilot_combination
          UNIQUE( numpilot1, numpilot2 );

ALTER TABLE duel
  ADD CONSTRAINT chk_pilot1_lt_pilot2
           CHECK( numpilot1_fk < numpilot2_fk );

If you wanted to do this sort of thing in a trigger, it would be quite a bit more complicated. In general, a row-level trigger on DUEL cannot query the DUEL table-- doing so would create a mutating table exception. You would need to create a collection in a package, a before statement trigger that initializes the collection, a row-level trigger that inserts the new pilot keys into the collection, and an after statement trigger that reads the data in the collection and does the validation. That's quite a few moving pieces to manage in addition to the potential performance hit. If you're really stuck with the trigger solution, however, there is an example of using the three trigger solution to work around mutating table exceptions on Tim Hall's site.

Upvotes: 5

Tony Andrews
Tony Andrews

Reputation: 132570

You could use a function-based index:

create unique index duel_uk on duel
( least(numpilot1, numpilot2), greatest(numpilot1, numpilot2));

The answer you are clearly seeking is a trigger like this:

create trigger duel_trg
before insert on duel
for each row
declare
  dummy number;
begin
  select count(*)
  into dummy
  from duel
  where (numpilot1 = :new.numpilot1 and numpilot2 = :new.numpilot2)
  or (numpilot1 = :new.numpilot2 and numpilot2 = :new.numpilot1);

  if dummy > 0 then
    raise_application_error(-20001,'You lose');
  end if;
end;

However, that will fail to ensure integrity in a multi-user (or multi-session) environment, as this can happen:

User1> insert into duel (numpilot1, numpilot2) values (1,2);
-- trigger checks, all seems OK

User2> insert into duel (numpilot1, numpilot2) values (1,2);
-- trigger checks, all seems OK (can't see User1's new row
-- as it hasn't been committed)

User1> commit;
User2> commit;

Result: corrupt database. So while this trigger may satisfy the teacher, it is a bad solution and constraints should be used instead (preferably Justin's solution rather than mine!)

Upvotes: 5

Related Questions