Oracle Trigger

[nirajbpate]

Hello,

I am trying to use a pre_insert trigger with my oracle db:-

create table book(
id number,
name varchar2(100)
);



CREATE OR REPLACE TRIGGER book_ins
BEFORE INSERT ON book FOR EACH ROW
BEGIN
SELECT book_seq.NEXTVAL
INTO :new.id
from DUAL;
END;

Does anyone know if this is correct?

[maxhugen]

That looks ok, assuming that you have created the sequence "book_seq" of course.

[oratune]

That is one way to accomplish the task at hand. Another way, which will allow a specific ID to be inserted, would be this:

CREATE OR REPLACE TRIGGER book_ins
BEFORE INSERT ON book FOR EACH ROW
BEGIN
IF :new.id IS NULL THEN
SELECT book_seq.NEXTVAL
INTO :new.id
from DUAL;
END IF;
END;
/

If no value is supplied in the insert statement for ID then the sequence will populate the field, otherwise the supplied value will be used in the inserted record.

David Fitzjarrell
Oracle Certified DBA

[maxhugen]

David, your suggestion is very helpful where records could be imported from another source, with their own unique ids.

What would happen if a record with an ID of 500 were imported, but the sequence was only up to, say, 450?

[oratune]

I take care of that with the following, executed immediately after a successful data load:

rem
rem Rebuild the BOOK_SEQ sequence
rem
declare
--
-- Get highest ID value in the table
--
cursor get_max_id is
select max(id)
from book;

max_id number:=0;
sqlstmt varchar2(255);
begin
--
-- Retrieve maximum ID value from BOOK
--
open get_max_id;
fetch get_max_id into max_id;
close get_max_id;
--
-- Add one
--
max_id := max_id + 1;
--
-- Drop the current BOOK_SEQ
--
sqlstmt:='drop sequence book_seq';
execute immediate sqlstmt;
--
-- Create new SEQ_MSG with new starting value
--
sqlstmt:='create sequence book_seq start with '||max_id||' increment by 1 maxvalue 999999999999999999999999999 minvalue 1 nocycle noorder cache 20';
execute immediate sqlstmt;
end;
/

This recreates the sequence so that it will start at the immediately after the highest inserted ID value.