Q
Problem solve Get help with specific problems with your technologies, process and projects.

Trigger won't insert into table

The following are the tables, sequence and trigger I have created, but the trigger does not insert values into my table, and I don't understand why.

 
 create table login (ID number, USERNAME 
 VARCHAR2(10), PASSWORD
 VARCHAR2(10);
 ----------------------------------------create
 sequence autoid;
 ---------------------------------------- create or
 replace trigger
 id_increment before insert on login
  for each row
  when (new.id is null)
  begin
 	 select autoid.nextval into :new.id from dual; 
 	 insert into members(ID) values(:new.id);
 
  end;
 ----------------------------------------
  insert into login(id,username,password)
 values(autoid.nextval,'san','vajre');
----------------------------------------
 create table members(Id number, ISBN number, Author
 varchar2(50); 
 ----------------------------------------
 
Is it more efficient to use:
 
 where date_col=to_date ('20020202', 'YYYYMMDD')
instead of:
 where to_char to_date, 'YYYYMMDD') = '20020202')

Try the following trigger instead:

CREATE OR REPLACE TRIGGER id_increment
BEFORE INSERT ON login FOR EACH ROW WHEN (:new.id IS
NULL)
DECLARE
  seq_value NUMBER;
BEGIN
  SELECT autoid.NEXTVAL INTO seq_value FROM dual;
  :new.id := seq_value;
END;
/

It is generally more efficient to use:

WHERE date_col=TO_DATE('20020202','YYYYMMDD')
over:
WHERE TO_CHAR(date_col,'YYYYMMDD') = '20020202'
The second statement naturally supresses any index on the date column. The first statement does not supress this index. This assumes that an index lookup is faster than a full table scan. You can override this behavior by creating a function-based index so that the second statement performs just as fast.

For More Information


Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close