Brett Kaiser (x002548) Blog

2 things, 1st, it's not the margartias and 2nd, anything that reads SOX sucks...I gotta read...

Here's the post by Derrick Leggit (to quit?)

http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=5635

And his article

http://weblogs.sqlteam.com/derrickl/archive/2004/06/28/1682.aspx

OK:  and the code that always seems to be asked for.  And since rembereing things ain't a strong suite (would tyhat be clubs?)

An old trick in non identity RDBMS's (note: back then we didn't have the niceties of triggers to make sure the identity control table didn't get out of whack)

USE Northwind
GO

CREATE TABLE myTable99(Col1 int PRIMARY KEY, Col2 char(1), Col3ect int)
GO

CREATE TABLE myTable99NextID(NextId int, InUseIND int)
GO

-- Set the seed

INSERT INTO myTable99NextID(NextID, InUseIND) SELECT 0,0
GO

CREATE TRIGGER myTrigger99NextID ON myTable99NextID
FOR INSERT, DELETE
AS 
  BEGIN
 IF EXISTS (SELECT * FROM inserted)
   BEGIN
  -- Nope only want to keep 1 row
    ROLLBACK
   END
 IF EXISTS (SELECT * FROM deleted)
   BEGIN
  -- Nope only want to keep 1 row
    ROLLBACK
   END
  END
GO

-- Test the trigger

INSERT INTO myTable99NextID(NextID, InUseIND) SELECT 0,0
GO

SELECT * FROM myTable99NextID

SELECT @@TRANCOUNT

DELETE FROM myTable99NextID

SELECT * FROM myTable99NextID

SELECT @@TRANCOUNT

-- Looks like we could add some error messages though

GO
CREATE PROC mySproc99 @Col2 char(1), @Col3ect int
AS
DECLARE @NextId int
SET NOCOUNT ON
  BEGIN TRAN
 UPDATE myTable99NextID SET InUseIND = 1

 SELECT @NextId = NextID + 1 FROM myTable99NextID
   
 INSERT INTO myTable99(Col1, Col2, Col3ect) VALUES(@NextId, @Col2, @Col3ect) 
 
 UPDATE myTable99NextId SET NextID = @NextId, InUseIND = 0
  COMMIT TRAN
SET NOCOUNT OFF
GO

EXEC mySproc99 'A',1

SELECT * FROM myTable99

EXEC mySproc99 'B',2

SELECT * FROM myTable99
GO

DROP PROC mySproc99
DROP TABLE myTable99
DROP TRIGGER myTrigger99NextID
DROP TABLE myTable99NextID
GO