News, views, tips and tricks on Oracle and other fun stuff

Triggers and Constraints

I was reading Jeff Hunter’s problem about triggers and constraints with interest. Here is a summary of the problem:

First scenario: you have a table t with a column mycol varchar2(4). You also have a “before insert for each row” trigger on that table. The trigger overwrites the column like this: :new.mycol := 'hi'. When you try to insert a string into the column and the string size is greater than the size of the column: insert into t values ('hi there'), you get this error: ORA-12899: value too large for column. The question is, why didn’t the trigger fire and replace ‘hi there’ with ‘hi’?

Second scenario: now, you add a not null constraint to the column: mycol varchar2(4) not null, and you modify the “before insert for each row” trigger to overwrite mycol if it is null: if mycol is null then mycol := 'hi'; end if;. Now, the statement “insert into t values (null)” does not generate an error and results in the value ‘hi’ stored in the column. So, why didn’t the not null constraint result in the same behavior as the first scenario?

Tom Kyte chimed in and what he basically said was that, in the first scenario, the :new record, which must be bound to, takes its shape from the definition of the underlying table. So, the “bind to the :new record of the mycol field that was too large” is what caused the error.

In the second scenario, the “before insert for each row” trigger is fired before integrity constraints are verified so that the trigger can “fix” things that would violate a constraint. This is documented in the Database Concepts Manual.

Related links: Jeff Hunter’s post 1 and post 2.

Filed in Oracle on 15 Aug 05 | Tags: ,

Comments are closed.