wood burning stoves 2.0*
The moose likes JDBC and the fly likes Trigger performance Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Trigger performance" Watch "Trigger performance" New topic
Author

Trigger performance

Mamatha Kv
Greenhorn

Joined: Apr 01, 2010
Posts: 5
Please let me klnow which method is more efficient in terms of performance to generate an auto increment number in oracle.

Step1

CREATE TABLE test
(id NUMBER PRIMARY KEY,
name VARCHAR2(30));

Step2
CREATE SEQUENCE test_sequence
START WITH 1
INCREMENT BY 1;

Method1

Follow Step1 and Step2 and create a Trigger as below :

CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT
ON test
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT test_sequence.nextval INTO :NEW.ID FROM dual;
END;
/


Method 2

Follow Step1 and Step2 and directly have an insert statement as below:

INSERT INTO test (id, name) VALUES (test_sequence.nextval , 'Jon343');


Thanks
Mamatha

Matthew Brown
Bartender

Joined: Apr 06, 2010
Posts: 4339
    
    7

I'm not sure which one has the best performance, but Method 1 seems superior to me in terms of maintainability. It removes the responsibility of maintaining database integrity from the application developer, so reducing the impact of application errors on the database.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Trigger performance
 
Similar Threads
Auto Increment ID Field Table in the Oracle Database
why my oracle sequence id increases by 2 when I use hibernate to insert
session persistance from server perspective
jpa persist increment id twice
how to write triggers