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_TRIGGE R
BEFORE INSERT
ON TABLE
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT squence_name.ne xtval 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
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_TRIGGE R
BEFORE INSERT
ON TABLE
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT squence_name.ne xtval 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
Comment