March 3, 2010 at 7:35 am
This is what customer wants, because of their politics, standards and other past and future systems etc...
In every table there should be ID-column (int). It should have identity values, new record has always bigger (next) value than previously entered, no matter to which table record is entered.
eg.
insert into tbl1 --> tbl1.id=1
Insert into tbl2 --> tbl2.id=2
insert into tbl2 --> tbl2.id=3
Insert into tbl3 --> tbl3.id=4
Insert into tbl2 --> tbl2.id=5
Insert into tbl1 --> tbl1.id=6
Insert into tbl4 --> tbl4.id=7
Insert into tbl4 --> tbl4.id=8
Insert into tbl4 --> tbl4.id=9
Insert into tbl3 --> tbl3.id=10
etc.
And this should work no matter if records are inserted by an application or from SSMS or any other tool. Also inserts can happen one at a time or multiple records at the same time.
Is there any other way than build triggers to read and update separate identity-table which holds information about last used id? It will have many issues regarding locking, mutiple inserts at same time etc.
Someone said that sequence in Oracle is something like this, I don't know oracle at all. Probably this has given customer the whole idea... At least please provide me with a good explanation why not to build this 🙂
TIA
Kari
March 3, 2010 at 7:44 am
There's no way to do this automatically. You would have to write code, either with triggers that handle things, or with some other management from your application. Even with triggers, you need fully qualified insert statements to ensure this works correctly.
If you are working with single rows, this works well. When you start to send in multiple rows, or do some type of bulk insert, you could have issues with your code. That's where you'd really have to write something that is very well coded.
I might ask the client what the point is of doing this. If it's to know the order of records, I'd question the value, but an audit of some sort would do this as well. Having separate tables somehow linked with a global identity sounds like ignorance on the part of the client.
March 3, 2010 at 7:59 am
Steve Jones - Editor (3/3/2010)
There's no way to do this automatically. You would have to write code, either with triggers that handle things, or with some other management from your application. Even with triggers, you need fully qualified insert statements to ensure this works correctly.If you are working with single rows, this works well. When you start to send in multiple rows, or do some type of bulk insert, you could have issues with your code. That's where you'd really have to write something that is very well coded.
I might ask the client what the point is of doing this. If it's to know the order of records, I'd question the value, but an audit of some sort would do this as well. Having separate tables somehow linked with a global identity sounds like ignorance on the part of the client.
This sounds like fun!!!
I agree with Steve. I thinking about 2 bulk loads happening at the same time. Which one gets which id could be fun. I don't want to referee that match, too much potential for locking and blocking. Performance would definitely suffer. :w00t:
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
March 3, 2010 at 8:03 am
just to clarify the whole Oracle thing, Oracle doesn't have a native identity() function like SQL does; instead, you use a SEQUENCE and a TRIGGER to duplicate the functionality, and the trigger gets the SEQUENCE_NAME.NEXTVAL. to put in the desired field.
so if you were to make every trigger in the database point to the same SEQUENCE, you'd get the desired affect. Since oracle does multi-row inserts as row-by-row, there'd be no issues, just contention for the same SEQUENCE if multi tables were doing multi-inserts.
I don't know if i'd call using the same SEQUENCE a bad practice, but it certainly does not follow any industry standards I've tripped over so far.
I've always created one sequence for each table/trigger combination, as that emulates the identity/autonumber I'm used to.
I'm with Steve on the requirement...sounds like they are not aware of how SQL works, and are adding a requirement that is a carryover from familiar code, and not necessarily a good practice/best practice.
here's my question: how would foreign keys get handled in that scenario? it'd be ugly and not inuitive to have PK's jumping values, and not knowing intuitively that the next PK in a table isn't what you'd expect.
Lowell
March 3, 2010 at 8:11 am
to actually implement this, I think each trigger would have to insert into a mastertable with a bigint identity() column, and use an OUTPUT clause for the collection of values, and then use THAT to data insert in the table the trigger is handling.
does that sound right?
Lowell
March 3, 2010 at 8:20 am
I was thinking of the same thing as Lowell, a central table with an identity used to seed the other tables.
March 3, 2010 at 8:28 am
Steve Jones - Editor (3/3/2010)
I was thinking of the same thing as Lowell, a central table with an identity used to seed the other tables.
I think that's a given. I can also see ways to do this that work for single row inserts. I'm not sure you could do bulk inserts without turning them into individual row inserts, unless you did bulk inserts into temporary tables before moving these to the final table.
It's doable, but could easily suffer major performance penalties.
I'd keep on asking: "Why do you want to do this?"
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
March 3, 2010 at 8:48 am
i got curious on this and built a prototype.
man what a pain to have to code a zillion triggers, just to have a master id someplace.
i could
I ended up cheating and also including an identity anyway, as it made it much easier to tie the values to the table .
if the "master key" were the only PK of the table, i think you have to swithc to INSTEAD OF triggers and a row_number() function based on the INSERTED table; couldn't quite get it that to work, and reverted to an indeitity() cheat instead.
CREATE TABLE MASTERKEYS(KEYID BIGINT IDENTITY(1,1) PRIMARY KEY,ROWNUM BIGINT,TABLENAME SYSNAME )
CREATE TABLE EXAMPLE(EXAMPLEID BIGINT IDENTITY(1,1) NOT NULL,
KEYID BIGINT REFERENCES MASTERKEYS(KEYID),
EXAMPLETEXT VARCHAR(30) )
GO
CREATE TRIGGER TR_EXAMPLE ON EXAMPLE
FOR INSERT
AS
BEGIN
--create a results table
DECLARE @RESULTS TABLE(KEYID BIGINT,EXAMPLEID BIGINT,TABLENAME SYSNAME)
--get some master keys
INSERT INTO MASTERKEYS(ROWNUM,TABLENAME)
OUTPUT INSERTED.KEYID,INSERTED.ROWNUM,INSERTED.TABLENAME INTO @RESULTS
SELECT EXAMPLEID,'EXAMPLE' FROM INSERTED
--update the keys
UPDATE EXAMPLE
SET EXAMPLE.KEYID = R.KEYID
FROM @RESULTS R
WHERE EXAMPLE.EXAMPLEID = R.EXAMPLEID
END
insert into EXAMPLE(EXAMPLETEXT)
SELECT 'one' union all
select 'two'
select * from EXAMPLE
--my results have a value, seem to be inverted from the order inserted though:
EXAMPLEID KEYID EXAMPLETEXT
1 2 one
2 1 two
to get the values back, with either row_nubmer()...bec
Lowell
March 3, 2010 at 9:04 am
here's a second table with an instead of trigger:
CREATE TABLE EXAMPLE2(KEYID BIGINT REFERENCES MASTERKEYS(KEYID),
EXAMPLETEXT VARCHAR(30) )
GO
CREATE TRIGGER TR_EXAMPLE2 ON EXAMPLE2
INSTEAD OF INSERT
AS
BEGIN
--create a results table
DECLARE @RESULTS TABLE(KEYID BIGINT,EXAMPLEID BIGINT,TABLENAME SYSNAME)
--get some master keys
INSERT INTO MASTERKEYS(ROWNUM,TABLENAME)
OUTPUT INSERTED.KEYID,INSERTED.ROWNUM,INSERTED.TABLENAME INTO @RESULTS
SELECT row_number() OVER(ORDER BY EXAMPLETEXT),'EXAMPLE' FROM INSERTED
--update the keys
INSERT INTO EXAMPLE2(KEYID,EXAMPLETEXT)
SELECT X.KEYID,X.EXAMPLETEXT
FROM(SELECT R.KEYID, R.EXAMPLEID,I.*
FROM @RESULTS R
INNER JOIN ( SELECT row_number() OVER(ORDER BY EXAMPLETEXT) AS ROWNUM, INSERTED.EXAMPLETEXT FROM INSERTED ) I
ON R.EXAMPLEID = I.ROWNUM) X
END
insert into EXAMPLE2(EXAMPLETEXT)
SELECT 'three' union all
select 'four'
select * from EXAMPLE
select * from EXAMPLE2
Lowell
March 3, 2010 at 10:48 pm
Personally, I think it would be a much better thing to convince the user of the error of their ways.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 4, 2010 at 2:05 am
I am coming into this conversation late..but..if the intent is to establish a unique, increasing, ID for every row in the database, or at least global to specific tables, you could entertain the use of NEWSEQUENTIALID() as a constraint on a UNIQUEIDENTIFIER column ..the result is an increasing GUID. You would have to test this to be sure the GUID is unique across all tables, but, given the definition of a GUID, the probability is very high.
I have to agree with everyone else though..I can think of no reason for creating a global identity from the statements given.
March 17, 2010 at 6:36 am
Thank you very much all you wise men out there!
At least we are now convinced that our customer's aims are not sensible, we'll start on working to explain that to them 🙂
Kari
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply