Need help with a trigger to create an "Identity" column

[Welshbird]

Hi - Im new to Oracle as I've used MS-SQL before, but I'm trying to re-write an old query to run in Oracle.

I need an identity column, and it appears there is no such thing, so I'm using a trigger to mimic an identity column.

This is my code:

begin
  execute immediate 'drop table EfexorTrack';
exception
  when others then
    if sqlcode = -942 then
      null;  -- table does not exist
    else
      raise;
    end if;
end;

Create Table EfexorTrack
(
	RecID Number (10) not null,
	CompID integer null,
	PatientGid varchar(50) not null,
	FirstDate date not null,
	NextDate date null,
	TreatmentDays integer null,
	RxDays integer null,
	Fullfil dec (9,2) null,
	FRange char(1) null,
	Quantity integer null,
	Frequency integer null,
	FirstMarket varchar(50) not null,
	NextMarket varchar(50) null,
	StatusIng char(1) null
);

then this bit to create the trigger

ALTER TABLE EfexorTrack ADD (
  CONSTRAINT dept_pk PRIMARY KEY (RecID));

CREATE SEQUENCE RecID_seq;

CREATE OR REPLACE TRIGGER Ident_bir 
BEFORE INSERT ON EfexorTrack 
FOR EACH ROW
WHEN (NEW.RecID IS NULL)
BEGIN
  SELECT RecID_seq.NEXTVAL
  INTO   :NEW.RecID
  FROM   dual;
END;
/

then I insert the records

insert into EfexorTrack (PatientGid, FirstDate, Quantity, Frequency, FirstMarket)
	select 
		Patient as PatientGid, 
		Rx_DATE as FirstDate, 
		DISPENSED_QTY as Quantity, 
		DOSAGE_FREQUENCY as Frequency,
		Market as FirstMarket
	from
		Edinburgh
	order by
		PatientGid,
		FirstDate;

Now, this does work, and my RecID column does end up with a number which increases by one each time.

My problem is I really need it to start from 1 which is what I would have expected it to do.

It seems to start from 693...

I hav no idea why - and I'm not sure how to make it do so, so any help would be enormously appreciated.

Thanks guys!

Fi.

"The question should be, is it worth trying to do, not can it be done"

 

[Welshbird]

Hurrah! I solved the problem - I needed to drop the sequence and then create it again, so that it starts from one!

So, I addded in this:

drop sequence RecID_seq;

and now it starts from one.

(I thought I'd answer my own posting incase someone else comes up against the same issue.



Fi.

"The question should be, is it worth trying to do, not can it be done"