Heath Sheehan said...
Row level triggers fire for each row that is affected by the triggering statement. That would imply that all validity checks have to be passed before the triggers fire. Any explicit or implicit data conversions have to result in valid data. Any check constraints have to pass, etc.
If the data isn't valid, the row isn't inserted and there's nothing for which the trigger should fire.
Yeah, but if that's true, why doesn't a NOT NULL constraint elicit the same behaviour?
SQL> drop table xyz
2 /
Table dropped.
SQL> create table xyz
2 (
3 x number(10),
4 y varchar2(20),
5 z varchar2(4) not null)
6 /
Table created.
SQL>
SQL> create or replace trigger xyz_bi
2 before insert on xyz
3 for each row
4 begin
5
6 IF :new.z IS NULL THEN
7 :new.z := '777';
8 END IF;
9
10 end;
11 /
Trigger created.
SQL>
SQL> insert into xyz values (1, '123456789','123');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from xyz;
X Y Z
---------- -------------------- ----
1 123456789 123
SQL>
SQL> insert into xyz values (1, '123456789',NULL);
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL> select * from xyz;
X Y Z
---------- -------------------- ----
1 123456789 123
1 123456789 777
SQL>
5 comments:
constraints are checked after the statement fires -- however, the :new record which must be bound to takes its shape from the definition of the underlying table.
that is in the server concepts guide. That is why an update of
update dept set deptno = decode( deptno, 10, 20, 20, 10 );
can work -- after the first row update, there are two departements with the same deptno (either two 10's or two 20's). Only when the statement is done can all constraints be verified.
Some are done after the row is modified (like not null or check) because they can be. But the BEFORE FOR EACH ROW trigger is fired before they are verified (that trigger can "fix" things that would violate a constraint)
So, it was sort of like the "bind to :new record of the Z field that was too large" is what blew this up in the other posting you had.
Correction about check constraints acknowledged and accepted. :)
However, back to the first part, I don't quite understand how it's the binding of the :new variable that's the issue. I'm probably missing something else, so help me out some more.
=================================
SQL*Plus: Release 10.1.0.2.0 - Production on Sat Aug 13 13:35:16 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
ME@ora10g> create table xyz(
2 x number(10),
3 y varchar2(20),
4 z varchar2(4) not null)
5 /
Table created.
ME@ora10g> create or replace trigger xyz_bi
2 before insert on xyz
3 for each row
4 begin
5 dbms_output.put_line('xyz_bi fired.');
6 end;
7 /
Trigger created.
ME@ora10g>-- Valid insert
ME@ora10g>insert into xyz values( 1, '123456789', '123' );
xyz_bi fired.
1 row created.
ME@ora10g>-- NOT NULL violation
ME@ora10g>insert into xyz values( 1, '123456789', NULL );
xyz_bi fired.
insert into xyz values( 1, '123456789', NULL )
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("ME"."XYZ"."Z")
ME@ora10g>-- Incorrect data type (implicit conversion)
ME@ora10g>insert into xyz values( '1x', '123456789', '123' );
insert into xyz values( '1x', '123456789', '123' )
*
ERROR at line 1:
ORA-01722: invalid number
ME@ora10g>-- invalid data length
ME@ora10g>insert into xyz values( 1, '123456789', '123456789' );
insert into xyz values( 1, '123456789', '123456789' )
*
ERROR at line 1:
ORA-12899: value too large for column "ME"."XYZ"."Z" (actual: 9, maximum: 4)
ME@ora10g>rollback;
Rollback complete.
ME@ora10g>
=================================
This would seem to meant that the error is raised before the trigger even fires?
thomas kyte said...
that is in the server concepts guide.
Have a link maybe?
From:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c22integ.htm#3732
Even though a constraint is defined to verify that each manager_id value matches an employee_id value, this statement is legal because Oracle effectively performs its constraint checking
after the statement completes .
Figure 21-11 shows that Oracle performs the actions of the entire SQL statement before any constraints are checked.
oraboy said...
I agree about constraints, but a before insert trigger, as I understood it (which is obviously wrong) fires before the row gets inserted. I guess the difference is the statement doesn't even execute since the type is wrong (almost like that statement failed the "execute" phase).
Post a Comment