Are column length constraints checked before the trigger executes?
I need to create a trigger that trims the input parameters to the length of the column. The particular column is defined as varchar(15).
I have a trigger:
create or replace
trigger my_table_data_before_insert
before insert on my_table_data
for each row
begin
:new.contact := substr(:new.contact,1,14);
end;
My insert statement:
declare
<..more parameter..>
v_CONTACT VARCHAR2(100) := '123456789012345678';
begin
insert into my_table_data(...,contact,...)
values(...,v_contact,...);
commit;
exception
when others then
-- print the sqlerrm
end;
My thought is - the before insert trigger will get executed prior to the insert, it will do the substr(contact,1,15) and insert the new value with the first 15 characters into the table.
Instead I get:
ORA-12899: value too large for column "my_schema"."MY_TABLE_DATA"."CONTACT" (actual: 18, maximum: 15)
This is precisely the reason why I want the trigger .. I want the correct length to be inserted.
Is there some kind of error check that kicks in prior to the insert?