I’ve dragged this little demo up from the past, because friend Toon (http://thehelsinkideclaration.blogspot.com) has been blogging about triggers. This demo shows the overhead of triggers in terms of doing things that can be easily done without triggers. But I do stress – I’m with Toon on this one – this is not the same as having a blanket ban on triggers.
Someone asked why having triggers to populate primary keys with a sequence was a bad idea…so a simple demo is called for.
SQL> create table T ( x number ); Table created. SQL> create sequence S cache 1000; Sequence created. SQL> create or replace 2 trigger TRG before insert on T 3 for each row 4 begin 5 :new.x := s.nextval; 6 end; 7 / Trigger created. SQL> set timing on SQL> insert into T 2 select rownum from dual 3 connect by level <= 100000; 100000 rows created. Elapsed: 00:00:08.79
So it takes 9 seconds to load 100,000 rows – and everyone starts asking for more CPU, more RAM, faster servers, etc etc etc….
But lets try it without the trigger, and just code the insert using the sequence directly
SQL> insert into T 2 select s.nextval from dual 3 connect by level <= 100000; 100000 rows created. Elapsed: 00:00:01.81
Bottom line – if you want a 400% performance penalty on insert, then triggers for sequences are just what you are looking for 🙂