Oracle - should I use exceptions or triggers?

Question

When trying to insert data into tables, it could fail for various reasons like unique key violated or foreign key violated.

I could use DUP_VAL_ON_INDEX exception to know unique key is violated but how do I know which key if there are multiple columns with unique marked? Should I use triggers in this case?

(I'm using exceptions to drive the flow of stored procedures instead of querying tables to make sure data being inserted is okay.)

Answer 1

Exceptions (from constraints) or triggers? I say neither.

I would strongly recommend using application code and queries to drive the logic of your application, as opposed to triggers and constraints. Separating the logic from the error condition handling will only make the behaviour of your code appear more magical.

Although constraints are extremely useful as backup plan (in the case of application logic errors or changes), you are correct in that they do not assist with runtime error handling in the way that you want (i.e. do not help you expose the duplicate keys that the INSERT statement attempted to use).

As you have no doubt determined, you could very easily use a trigger to do what you describe, but ultimately all that means is that you're simply hiding the same logical test ("does this record already exist?") in a trigger as opposed to in the application code where your INSERT statement will exist. If multiple triggers exist on the table, you may also be going through the overhead of executing them needlessly, as if one will raise an error, any others which ran first would have been fruitless.

If you don't want to write additional statements to pre-check the condition of your data before running new INSERT statements, then I would suggest using something like this:

  INSERT INTO my_table (col1, col2)
  SELECT l_val1, l_val2
  FROM dual
  WHERE NOT EXISTS (
    SELECT 1
    FROM my_table t
    WHERE t.col1 = l_val1
    AND t.col2 = l_val2
  )

At least then you will not violate the key, but you'll know if a row already existed by testing whether SQL%ROWCOUNT is 0 (no row inserted, so must already exist) or 1.

However, this still does not give you the key-violating values. You will have to write queries to find out which will violate the constraint.

Answer 2

This is not what triggers are for. Remember that you can not query the target table inside a trigger, so you can not really check for uniqueness violations. I would say the best reason for a trigger is to make sure that rows are populated with default data, like an id from a sequence. Triggers also obfuscate code because it's not clear why the row that got inserted is different from your statement.

Constraints perform a useful function other than the obvious: the optimizer can use information in the constraints to judge an execution plan. A more efficient route can be calculated if the optimizer knows if a column is NOT NULL, for example.

The INSERT statement given by @Ben above is a good one, you can also wrap your statements inside an anonymous block:

BEGIN
    ... application code ... 
    BEGIN
        INSERT INTO mytable (col1, col2) VALUES (val1, val2);
    EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
        ... error handling code ...
    END;
    ... application continues...
END;

Answer 3

For what it's worth, I suggest that you do not rely solely on application code to enforce uniqueness. One key (pun intended) reason is that it is difficult to write code that does this correctly, particularly for cases where data is inserted or updated concurrently in multiple transactions. It is comparatively simple to declare a unique constraint that the database will enforce correctly and efficiently.

If you want to know which columns have violated a unique constraint, one approach (and I realize it's not "pretty") is to:

1. Parse the constraint name from the error message (for example, as returned by SQLERRM).
2. Use some mechanism (like the data dictionary or a table of your own) to "look up" which columns correspond to the constraint.

If you want to know which rows have violated a unique constraint, you can use the LOG ERRORS INTO clause of the INSERT statement to log the offending rows into an error logging table, as outlined in this example.

If you want to know which rows and columns have violated a unique constraint, you can combine both methods -- you log the rows using the LOG ERRORS INTO clause, and then you parse the constaint name from the error message (which is also logged in the error logging table) and look up the corresponding columns.

Having said that, you don't necessarily need to choose between: a) checking the data first and only inserting it if it passes the checks, and, b) trying to insert the data and then reporting and/or acting on any errors.