Sequences in MS Server

[Quish]

Hello all,

I am having some trouble with functions and triggers within MSDE; the majority of my database experience comes from Oracle and I want to implement similar functions and trigger used in Oracle within MS Server.

I am trying to create a sequence on an ID that reference the last ID (before insert) in a table and then increments by 1 to create the next number in the sequence to act as a primary key.

The syntax in Oracle is below (if this helps clarify my problem)

DROP SEQUENCE squence_name;
CREATE SEQUENCE squence_name
START WITH 1
INCREMENT BY 1
nomaxvalue;

CREATE OR REPLACE TRIGGER TABLE_ID_TRIGGER
BEFORE INSERT
ON TABLE
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT squence_name.nextval INTO :NEW.TABLE_ID FROM dual;
END;
/

What I have been able to find so far is this “Auto-Generated Sequence” that is meant to work in MS Server.

CREATE FUNCTION FUNCTION_ID()
RETURNS NUMERIC
AS
BEGIN
DECLARE @LASTVAL NUMERIC
SET @LASTVAL = (SELECT MAX(FUNCTION_ID) from TABLE)
IF @LASTVAL IS NULL SET @LASTVAL = + 1
DECLARE @i INT
RETURN @i
END

I am able to create this function but how does this function fire without some sort of BEFORE INSERT call to input the next number as a primary key in the table?

Regards

-- Quish

[ck9663]

Hello all,

I am having some trouble with functions and triggers within MSDE; the majority of my database experience comes from Oracle and I want to implement similar functions and trigger used in Oracle within MS Server.

I am trying to create a sequence on an ID that reference the last ID (before insert) in a table and then increments by 1 to create the next number in the sequence to act as a primary key.

The syntax in Oracle is below (if this helps clarify my problem)

DROP SEQUENCE squence_name;
CREATE SEQUENCE squence_name
START WITH 1
INCREMENT BY 1
nomaxvalue;

CREATE OR REPLACE TRIGGER TABLE_ID_TRIGGER
BEFORE INSERT
ON TABLE
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT squence_name.nextval INTO :NEW.TABLE_ID FROM dual;
END;
/

What I have been able to find so far is this “Auto-Generated Sequence” that is meant to work in MS Server.

CREATE FUNCTION FUNCTION_ID()
RETURNS NUMERIC
AS
BEGIN
DECLARE @LASTVAL NUMERIC
SET @LASTVAL = (SELECT MAX(FUNCTION_ID) from TABLE)
IF @LASTVAL IS NULL SET @LASTVAL = + 1
DECLARE @i INT
RETURN @i
END

I am able to create this function but how does this function fire without some sort of BEFORE INSERT call to input the next number as a primary key in the table?

Regards

-- Quish

Would an IDENTITY\ column solve your problem?

-- CK

[Quish]

Thanks for the help,

Quish