CODEWITHSUNDEEP
sqldatabaseoracle-databasetriggersconstraints
henry.oswald
henry.oswald

Reputation: 5434

Oracle Unique Constraint - Trigger to check value of property in new relation

Hi I'm having trouble getting my sql syntax correct. I want to create a unique constraint that looks at the newly added foreign key, looks at some properties of the newly related entity to decided if the relationship is allowed.

CREATE or replace TRIGGER "New_Trigger"
AFTER INSERT OR UPDATE ON "Table_1" 
FOR EACH ROW
BEGIN
Select "Table_2"."number" 
(CASE "Table_2"."number" > 0
  THEN RAISE_APPLICATION_ERROR(-20000, 'this is not allowed');
END)
from "Table_1"
WHERE "Table_2"."ID" = :new.FK_Table_2_ID 
END;

Edit: APC answer is wonderfully comprehensive, however leads me to think im doing it in the wrong way.

The situation is I have a table of people with different privilege levels, and I want to check these privilege levels, e.g. A user, 'Bob', has low level privileges and he tries to become head of department which requires requires high privileges so the system prevents this happening.

There is a follow-up question which poses a related scenario but with a different data model. Find it here.

Upvotes: 1

Views: 5497

Answers (2)

APC
APC

Reputation: 146239

So the rule you want to enforce is that TABLE_1 can only reference TABLE_2 if some column in TABLE_2 is zero or less. Hmmm.... Let's sort out the trigger logic and then we'll discuss the rule.

The trigger should look like this:

CREATE or replace TRIGGER "New_Trigger"
AFTER INSERT OR UPDATE ON "Table_1" 
FOR EACH ROW
declare
  n "Table_2"."number".type%;
BEGIN

    Select "Table_2"."number" 
    into n
    from "Table_2"
    WHERE "Table_2"."ID" = :new.FK_Table_2_ID; 

    if n > 0
    THEN RAISE_APPLICATION_ERROR(-20000, 'this is not allowed');
    end if;

END;

Note that your error message should include some helpful information such as the value of the TABLE_1 primary key, for when you are inserting or updating multiple rows on the table.

What you are trying to do here is to enforce a type of constraint known as an ASSERTION. Assertions are specified in the ANSI standard but Oracle has not implemented them. Nor has any other RDBMS, come to that.

Assertions are problematic because they are symmetrical. That is, the rule also needs to be enforced on TABLE_2. At the moment you check the rule when a record is created in TABLE_1. Suppose at some later time a user updates TABLE_2.NUMBER so it is greater than zero: your rule is now broken, but you won't know that it is broken until somebody issues a completely unrelated UPDATE on TABLE_1, which will then fail. Yuck.

So, what to do?

If the rule is actually

TABLE_1 can only reference TABLE_2 if TABLE_2.NUMBER is zero

then you can enforce it without triggers.

  1. Add a UNIQUE constraint on TABLE_2 for (ID, NUMBER); you need an additional constraint because ID remains the primary key for TABLE_2.
  2. Add a dummy column on TABLE_1 called TABLE_2_NUMBER. Default it to zero and have a check constraint to ensure it is always zero. (If you are on 11g you should consider using a virtual column for this.)
  3. Change the foreign key on TABLE_1 so (FK_Table_2_ID, TABLE_2_NUMBER) references the unique constraint rather than TABLE_2's primary key.
  4. Drop the "New_Trigger" trigger; you don't need it anymore as the foreign key will prevent anybody updating TABLE_2.NUMBER to a value other than zero.

But if the rule is really as I formulated it at the top i.e.

TABLE_1 can only reference TABLE_2 if TABLE_2.NUMBER is not greater than zero (i.e. negative values are okay)

then you need another trigger, this time on TABLE_2, to enforce it the other side of the rule. 

CREATE or replace TRIGGER "Assertion_Trigger"
BEFORE UPDATE of "number" ON "Table_2" 
FOR EACH ROW
declare
  x pls_integer;
BEGIN

    if :new."number"  > 0
    then
        begin
            Select 1 
            into x
            from "Table_1"
            WHERE "Table_1"."FK_Table_2_ID" = :new.ID
            and rownum = 1;

           RAISE_APPLICATION_ERROR(-20001, :new.ID
                 ||' has dependent records in Table_1');
        exception
           when no_data_found then 
               null; -- this is what we want
        end;

END;

This trigger will not allow you to update TABLE_2.NUMBER to a value greater than zero if it is referenced by records in TABLE_2. It only fires if the UPDATE statement touches TABLE_2.NUMBER to minimise the performance impact of executing the lookup.

Upvotes: 5

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60272

Don't use a trigger to create a unique constraint or a foreign key constraint. Oracle has declarative support for unique and foreign keys, e.g.:

Add a unique constraint on a column:

ALTER TABLE "Table_1" ADD (
  CONSTRAINT table_1_uk UNIQUE (column_name)
);

Add a foreign key relationship:

ALTER TABLE "ChildTable" ADD (
  CONSTRAINT my_fk FOREIGN KEY (parent_id)
    REFERENCES "ParentTable" (id)
);

I'm not clear on exactly what you're trying to achieve with your trigger - it's a bit of a mess of SQL and PL/SQL munged together which will not work, and seems to refer to a column on "Table_2" which is not actually queried.

A good rule of thumb is, if your trigger is querying the same table that the trigger is on, it's probably wrong.

I'm not sure, but are you after some kind of conditional foreign key relationship? i.e. "only allow child rows where the parent satisfies condition x"? If so, the problem is in the data model and should be fixed there. If you provide more explanation of what you're trying to achieve we should be able to help you.

Upvotes: 3

Related Questions