SQL> create or replace trigger bi_employees
2 before insert on employees
3 for each row
5 if :new.salary + :new.commission_pct*:new.salary > 5000 then
6 raise_application_error(-20100, 'Wrong salary value, cant insert record');
7 end if;
SQL> show errors;
2 i number;
4 for i in 100000..150000 loop
5 insert into employees
6 ( emp_id, salary, commission_pct)
7 values (i, mod(i,20)*mod(i,10), 0.2);
8 end loop;
PL/SQL procedure successfully completed.
without any constraints and triggers: 00:00:04.05
with constraint : 00:00:05.00 (+ ~20%)
with trigger : 00:00:06.03 (+ ~34%)
U can compare all three results.
this result valid only for this test.
in real situations results maybe another, but common rule will be the same.
08-10-2002, 03:33 AM
You have to make an individual assessment for each system to see what the performance issues are. I would suggest using the most aggressive constraint checking you can as data integrity is worth the extra processing. That said, I've worked on realtime control systems where performance issues meant the database had virtually no constraints. It all depends...