June 27, 2002 at 1:55 pm
Hello experts,
Can you please help me translate this sql statement into T-SQL? Thanks.
First sql:
CREATE SEQUENCE PICK_LIST_NO_SEQ INCREMENT BY 1 MINVALUE 1
Second sql:
CREATE OR REPLACE TRIGGER NEXT_TGT_PICK_LIST
BEFORE INSERT OR UPDATE OF PICK_LIST_NO ON TGT_PICK_LIST
FOR EACH ROW
BEGIN
SELECT PICK_LIST_NO_SEQ.NEXTVAL
INTO :new.PICK_LIST_NO
FROM dual ;
End ;
The two sql works on Oracle db but syntax needs to be modified for MS SQL Server.
June 27, 2002 at 2:42 pm
the easiest way to do this is with an identity. For this, you would need to specify it when you create the table
create table tgt_pick_list
( pick_list_no int identity(1,1)
, colb char
...
)
Steve Jones
June 27, 2002 at 5:46 pm
quote:
the easiest way to do this is with an identity. For this, you would need to specify it when you create the tablecreate table tgt_pick_list
( pick_list_no int identity(1,1)
, colb char
...
)
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
June 27, 2002 at 5:49 pm
Thanks Steve. One thing I don't understand is " colb char..."
Do you or anyone else know how to translate the correct syntax for the trigger sql?
Thanks,
Phillip
the easiest way to do this is with an identity. For this, you would need to specify it when you create the table
create table tgt_pick_list
( pick_list_no int identity(1,1)
, colb char
...
)
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
[/quote]
[/quote]
June 28, 2002 at 9:28 am
Hello,
try this
CREATE PROCEDURE SELNEXTCOUNT @countername CHAR(5) AS
DECLARE @retval AS INTEGER
BEGIN
SET NOCOUNT ON
IF NOT EXISTS (SELECT VALORE FROM TB_COUNTERS (NOLOCK) WHERE COD_COUNTERS = @countername)
BEGIN
SELECT @retval = 1
INSERT INTO TB_COUNTERS (COD_COUNTERS, VALORE) VALUES (@countername, @retval)
END
ELSE
BEGIN
SELECT @retval = (VALORE + 1) FROM TB_COUNTERS UPDLOCK WHERE COD_COUNTERS= @countername
UPDATE TB_COUNTERS SET VALORE = @retval WHERE (COD_COUNTERS = @countername)
END
SET NOCOUNT OFF
SELECT @RETVAL
END
GO
bye Antonio
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply