|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Return random number from triggerI have a trigger on a table that creates a random number for the Primary
Key. I want to be able to return that random number to my program somehow. I tried the RETURN but it is not allowed in a trigger. My trigger code is below. Any help is appreciated. David CREATE TRIGGER T_People_ITrig ON dbo.People FOR INSERT AS SET NOCOUNT ON DECLARE @randc int, @newc int /* FOR AUTONUMBER-EMULATION CODE */ /* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'PersonID' */ SELECT @randc = (SELECT convert(int, rand() * power(2, 30))) SELECT @newc = (SELECT PersonID FROM inserted) UPDATE People SET PersonID = @randc WHERE PersonID = @newc INSERT INTO dbo.ClientInfo (ClientID) VALUES (@randc) 2000
Show quote "Alexander Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote in message news:1137682621.531767.66310@g49g2000cwa.googlegroups.com... > Is it SQL 2005 or 2000? > Hi
Triggers can't return values. You could , for example, use SCOPE_IDENTITY from the stored procedure -- Show quoteJack Vamvas __________________________________________________________________ Receive free SQL tips - register at www.ciquery.com/sqlserver.htm SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm New article by Jack Vamvas - SQL and Markov Chains - www.ciquery.com/articles/art_04.asp "David Chase" <dlch***@lifetimeinc.com> wrote in message news:eIjZCXQHGHA.3532@TK2MSFTNGP14.phx.gbl... > I have a trigger on a table that creates a random number for the Primary > Key. I want to be able to return that random number to my program somehow. > I tried the RETURN but it is not allowed in a trigger. My trigger code is > below. Any help is appreciated. > > David > > CREATE TRIGGER T_People_ITrig ON dbo.People FOR INSERT AS > SET NOCOUNT ON > DECLARE @randc int, @newc int /* FOR AUTONUMBER-EMULATION CODE */ > /* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'PersonID' */ > SELECT @randc = (SELECT convert(int, rand() * power(2, 30))) > SELECT @newc = (SELECT PersonID FROM inserted) > UPDATE People SET PersonID = @randc WHERE PersonID = @newc > > INSERT INTO dbo.ClientInfo (ClientID) VALUES (@randc) > > You could return it as a result set out of the trigger.
Show quote "Jack Vamvas" <DELETE_BEFORE_REPLY_j***@ciquery.com> wrote in message news:dqo9r4$ep9$1@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com... > Hi > > Triggers can't return values. You could , for example, > use SCOPE_IDENTITY from the stored procedure > > -- > Jack Vamvas > __________________________________________________________________ > Receive free SQL tips - register at www.ciquery.com/sqlserver.htm > SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm > New article by Jack Vamvas - SQL and Markov Chains - > www.ciquery.com/articles/art_04.asp > "David Chase" <dlch***@lifetimeinc.com> wrote in message > news:eIjZCXQHGHA.3532@TK2MSFTNGP14.phx.gbl... >> I have a trigger on a table that creates a random number for the Primary >> Key. I want to be able to return that random number to my program > somehow. >> I tried the RETURN but it is not allowed in a trigger. My trigger code >> is >> below. Any help is appreciated. >> >> David >> >> CREATE TRIGGER T_People_ITrig ON dbo.People FOR INSERT AS >> SET NOCOUNT ON >> DECLARE @randc int, @newc int /* FOR AUTONUMBER-EMULATION CODE */ >> /* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'PersonID' */ >> SELECT @randc = (SELECT convert(int, rand() * power(2, 30))) >> SELECT @newc = (SELECT PersonID FROM inserted) >> UPDATE People SET PersonID = @randc WHERE PersonID = @newc >> >> INSERT INTO dbo.ClientInfo (ClientID) VALUES (@randc) >> >> > > I'd suggest moving this logic to the INSERT procedure. Triggers can't return
results to the caller because the caller would be the server itself. ML --- http://milambda.blogspot.com/ A trigger can indeed return a result set, it will be passed back out to the
client. Tony. Show quote "ML" <M*@discussions.microsoft.com> wrote in message news:02DD91C6-0E01-4648-BC83-37AAAEC56390@microsoft.com... > I'd suggest moving this logic to the INSERT procedure. Triggers can't > return > results to the caller because the caller would be the server itself. > > > ML > > --- > http://milambda.blogspot.com/ Hi David,
Just a quick question, why a random number? If its random then chances are that it will have been used before so you will get a duplicate key on insert. Is there a reason you aren't using the natural key or the identity property if there isn't one? You can return a result set from the trigger that you can pick up in your clien application. Tony. Show quote "David Chase" <dlch***@lifetimeinc.com> wrote in message news:eIjZCXQHGHA.3532@TK2MSFTNGP14.phx.gbl... >I have a trigger on a table that creates a random number for the Primary >Key. I want to be able to return that random number to my program somehow. >I tried the RETURN but it is not allowed in a trigger. My trigger code is >below. Any help is appreciated. > > David > > CREATE TRIGGER T_People_ITrig ON dbo.People FOR INSERT AS > SET NOCOUNT ON > DECLARE @randc int, @newc int /* FOR AUTONUMBER-EMULATION CODE */ > /* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'PersonID' */ > SELECT @randc = (SELECT convert(int, rand() * power(2, 30))) > SELECT @newc = (SELECT PersonID FROM inserted) > UPDATE People SET PersonID = @randc WHERE PersonID = @newc > > INSERT INTO dbo.ClientInfo (ClientID) VALUES (@randc) > > You could try creating the random number before calling your insert, rather
than doing it after the fact with your trigger. This will alleviat ethe need for a return value, because you will already have the value. Just curious, why are you using a random number as a PK? Why not just use an identity field? Show quote "David Chase" <dlch***@lifetimeinc.com> wrote in message news:eIjZCXQHGHA.3532@TK2MSFTNGP14.phx.gbl... > I have a trigger on a table that creates a random number for the Primary > Key. I want to be able to return that random number to my program somehow. > I tried the RETURN but it is not allowed in a trigger. My trigger code is > below. Any help is appreciated. > > David > > CREATE TRIGGER T_People_ITrig ON dbo.People FOR INSERT AS > SET NOCOUNT ON > DECLARE @randc int, @newc int /* FOR AUTONUMBER-EMULATION CODE */ > /* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'PersonID' */ > SELECT @randc = (SELECT convert(int, rand() * power(2, 30))) > SELECT @newc = (SELECT PersonID FROM inserted) > UPDATE People SET PersonID = @randc WHERE PersonID = @newc > > INSERT INTO dbo.ClientInfo (ClientID) VALUES (@randc) > > It is random because it used to be a table on an Access database that
was used for replication. When it was uploaded to SQL Server, then SQL server created this random number trigger. This table (and several others) is used in many applications and inserts are done in a lot of places. I would prefer to stick the randomizing into a stored proc, but currently don't have time to do that. Can someone explain how I would return a "result set" in my trigger that I published? I have tried some CASE and IF statements to send back the @randc variable but my syntax errors out. Also, the reason I need IF or CASE is because after the insert I may need to insert into 1 of 3 different tables based on the value of a field named CategoryCode on the People table record inserted. Any help would be greatly appreciated. David *** Sent via Developersdex http://www.developersdex.com *** Hi David,
You'll need a before and after trigger, something like below. You you pick the result set up in your client application as a normal result set. I'm interested - what are you using that would create that trigger, i've not come across anything in SQL Server that does that. create table tony ( rndnumb int not null, othercol int not null ) go create trigger xyz_before on tony instead of insert as begin insert tony ( rndnumb, othercol ) select rndnumb = rand() * 10000, othercol from inserted end go create trigger xyz_after on tony after insert as begin select * from inserted end go insert tony ( rndnumb, othercol ) values ( 0, 1234 ) Show quote "David" <da***@lifetime.com> wrote in message news:OredsBRHGHA.3700@TK2MSFTNGP15.phx.gbl... > It is random because it used to be a table on an Access database that > was used for replication. When it was uploaded to SQL Server, then SQL > server created this random number trigger. This table (and several > others) is used in many applications and inserts are done in a lot of > places. I would prefer to stick the randomizing into a stored proc, but > currently don't have time to do that. > > Can someone explain how I would return a "result set" in my trigger that > I published? I have tried some CASE and IF statements to send back the > @randc variable but my syntax errors out. Also, the reason I need IF or > CASE is because after the insert I may need to insert into 1 of 3 > different tables based on the value of a field named CategoryCode on the > People table record inserted. Any help would be greatly appreciated. > > David > > > > *** Sent via Developersdex http://www.developersdex.com *** Tony,
The Access Upsizing Wizard created these. David Show quote "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message news:%23rtKKJRHGHA.3200@tk2msftngp13.phx.gbl... > Hi David, > > You'll need a before and after trigger, something like below. > > You you pick the result set up in your client application as a normal > result set. > > I'm interested - what are you using that would create that trigger, i've > not come across anything in SQL Server that does that. > > create table tony ( > rndnumb int not null, > othercol int not null > ) > go > > create trigger xyz_before on tony instead of insert > as > begin > insert tony ( rndnumb, othercol ) > select rndnumb = rand() * 10000, othercol > from inserted > > end > go > > create trigger xyz_after on tony after insert > as > begin > select * > from inserted > > end > go > > insert tony ( rndnumb, othercol ) values ( 0, 1234 ) > > -- > Tony Rogerson > SQL Server MVP > http://sqlserverfaq.com - free video tutorials > > > "David" <da***@lifetime.com> wrote in message > news:OredsBRHGHA.3700@TK2MSFTNGP15.phx.gbl... >> It is random because it used to be a table on an Access database that >> was used for replication. When it was uploaded to SQL Server, then SQL >> server created this random number trigger. This table (and several >> others) is used in many applications and inserts are done in a lot of >> places. I would prefer to stick the randomizing into a stored proc, but >> currently don't have time to do that. >> >> Can someone explain how I would return a "result set" in my trigger that >> I published? I have tried some CASE and IF statements to send back the >> @randc variable but my syntax errors out. Also, the reason I need IF or >> CASE is because after the insert I may need to insert into 1 of 3 >> different tables based on the value of a field named CategoryCode on the >> People table record inserted. Any help would be greatly appreciated. >> >> David >> >> >> >> *** Sent via Developersdex http://www.developersdex.com *** > > How bizzarre, i wonder what the idea behind it is.
Tony. Show quote "David Chase" <dlch***@lifetimeinc.com> wrote in message news:%23eWvHYRHGHA.3144@TK2MSFTNGP11.phx.gbl... > Tony, > The Access Upsizing Wizard created these. > > David > > "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message > news:%23rtKKJRHGHA.3200@tk2msftngp13.phx.gbl... >> Hi David, >> >> You'll need a before and after trigger, something like below. >> >> You you pick the result set up in your client application as a normal >> result set. >> >> I'm interested - what are you using that would create that trigger, i've >> not come across anything in SQL Server that does that. >> >> create table tony ( >> rndnumb int not null, >> othercol int not null >> ) >> go >> >> create trigger xyz_before on tony instead of insert >> as >> begin >> insert tony ( rndnumb, othercol ) >> select rndnumb = rand() * 10000, othercol >> from inserted >> >> end >> go >> >> create trigger xyz_after on tony after insert >> as >> begin >> select * >> from inserted >> >> end >> go >> >> insert tony ( rndnumb, othercol ) values ( 0, 1234 ) >> >> -- >> Tony Rogerson >> SQL Server MVP >> http://sqlserverfaq.com - free video tutorials >> >> >> "David" <da***@lifetime.com> wrote in message >> news:OredsBRHGHA.3700@TK2MSFTNGP15.phx.gbl... >>> It is random because it used to be a table on an Access database that >>> was used for replication. When it was uploaded to SQL Server, then SQL >>> server created this random number trigger. This table (and several >>> others) is used in many applications and inserts are done in a lot of >>> places. I would prefer to stick the randomizing into a stored proc, but >>> currently don't have time to do that. >>> >>> Can someone explain how I would return a "result set" in my trigger that >>> I published? I have tried some CASE and IF statements to send back the >>> @randc variable but my syntax errors out. Also, the reason I need IF or >>> CASE is because after the insert I may need to insert into 1 of 3 >>> different tables based on the value of a field named CategoryCode on the >>> People table record inserted. Any help would be greatly appreciated. >>> >>> David >>> >>> >>> >>> *** Sent via Developersdex http://www.developersdex.com *** >> >> > > Just a way of creating a random PK so that replication will not cause
problems with duplicates. Identities don't work well in replication. p.s. How is your random number generation different from the one in my trigger now? David Show quote "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message news:utp6BeRHGHA.3120@TK2MSFTNGP10.phx.gbl... > How bizzarre, i wonder what the idea behind it is. > > Tony. > > -- > Tony Rogerson > SQL Server MVP > http://sqlserverfaq.com - free video tutorials > > > "David Chase" <dlch***@lifetimeinc.com> wrote in message > news:%23eWvHYRHGHA.3144@TK2MSFTNGP11.phx.gbl... >> Tony, >> The Access Upsizing Wizard created these. >> >> David >> >> "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message >> news:%23rtKKJRHGHA.3200@tk2msftngp13.phx.gbl... >>> Hi David, >>> >>> You'll need a before and after trigger, something like below. >>> >>> You you pick the result set up in your client application as a normal >>> result set. >>> >>> I'm interested - what are you using that would create that trigger, i've >>> not come across anything in SQL Server that does that. >>> >>> create table tony ( >>> rndnumb int not null, >>> othercol int not null >>> ) >>> go >>> >>> create trigger xyz_before on tony instead of insert >>> as >>> begin >>> insert tony ( rndnumb, othercol ) >>> select rndnumb = rand() * 10000, othercol >>> from inserted >>> >>> end >>> go >>> >>> create trigger xyz_after on tony after insert >>> as >>> begin >>> select * >>> from inserted >>> >>> end >>> go >>> >>> insert tony ( rndnumb, othercol ) values ( 0, 1234 ) >>> >>> -- >>> Tony Rogerson >>> SQL Server MVP >>> http://sqlserverfaq.com - free video tutorials >>> >>> >>> "David" <da***@lifetime.com> wrote in message >>> news:OredsBRHGHA.3700@TK2MSFTNGP15.phx.gbl... >>>> It is random because it used to be a table on an Access database that >>>> was used for replication. When it was uploaded to SQL Server, then SQL >>>> server created this random number trigger. This table (and several >>>> others) is used in many applications and inserts are done in a lot of >>>> places. I would prefer to stick the randomizing into a stored proc, >>>> but >>>> currently don't have time to do that. >>>> >>>> Can someone explain how I would return a "result set" in my trigger >>>> that >>>> I published? I have tried some CASE and IF statements to send back the >>>> @randc variable but my syntax errors out. Also, the reason I need IF >>>> or >>>> CASE is because after the insert I may need to insert into 1 of 3 >>>> different tables based on the value of a field named CategoryCode on >>>> the >>>> People table record inserted. Any help would be greatly appreciated. >>>> >>>> David >>>> >>>> >>>> >>>> *** Sent via Developersdex http://www.developersdex.com *** >>> >>> >> >> > > Mine was just an example David, use your own, but I wouldn't rely on that
rand to get unique values if I where you because the chances are you'll get the same one eventually. Have you thought about newid() or if you don't like uniqueidentifier, converting it to char(36). SQL Server using uniqueidentifier for replication in merge replication. Tony. Show quote "David" <dlch***@lifetimeinc.com> wrote in message news:%23efrs2RHGHA.596@TK2MSFTNGP10.phx.gbl... > Just a way of creating a random PK so that replication will not cause > problems with duplicates. Identities don't work well in replication. > > p.s. How is your random number generation different from the one in my > trigger now? > > David > > "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message > news:utp6BeRHGHA.3120@TK2MSFTNGP10.phx.gbl... >> How bizzarre, i wonder what the idea behind it is. >> >> Tony. >> >> -- >> Tony Rogerson >> SQL Server MVP >> http://sqlserverfaq.com - free video tutorials >> >> >> "David Chase" <dlch***@lifetimeinc.com> wrote in message >> news:%23eWvHYRHGHA.3144@TK2MSFTNGP11.phx.gbl... >>> Tony, >>> The Access Upsizing Wizard created these. >>> >>> David >>> >>> "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message >>> news:%23rtKKJRHGHA.3200@tk2msftngp13.phx.gbl... >>>> Hi David, >>>> >>>> You'll need a before and after trigger, something like below. >>>> >>>> You you pick the result set up in your client application as a normal >>>> result set. >>>> >>>> I'm interested - what are you using that would create that trigger, >>>> i've not come across anything in SQL Server that does that. >>>> >>>> create table tony ( >>>> rndnumb int not null, >>>> othercol int not null >>>> ) >>>> go >>>> >>>> create trigger xyz_before on tony instead of insert >>>> as >>>> begin >>>> insert tony ( rndnumb, othercol ) >>>> select rndnumb = rand() * 10000, othercol >>>> from inserted >>>> >>>> end >>>> go >>>> >>>> create trigger xyz_after on tony after insert >>>> as >>>> begin >>>> select * >>>> from inserted >>>> >>>> end >>>> go >>>> >>>> insert tony ( rndnumb, othercol ) values ( 0, 1234 ) >>>> >>>> -- >>>> Tony Rogerson >>>> SQL Server MVP >>>> http://sqlserverfaq.com - free video tutorials >>>> >>>> >>>> "David" <da***@lifetime.com> wrote in message >>>> news:OredsBRHGHA.3700@TK2MSFTNGP15.phx.gbl... >>>>> It is random because it used to be a table on an Access database that >>>>> was used for replication. When it was uploaded to SQL Server, then SQL >>>>> server created this random number trigger. This table (and several >>>>> others) is used in many applications and inserts are done in a lot of >>>>> places. I would prefer to stick the randomizing into a stored proc, >>>>> but >>>>> currently don't have time to do that. >>>>> >>>>> Can someone explain how I would return a "result set" in my trigger >>>>> that >>>>> I published? I have tried some CASE and IF statements to send back >>>>> the >>>>> @randc variable but my syntax errors out. Also, the reason I need IF >>>>> or >>>>> CASE is because after the insert I may need to insert into 1 of 3 >>>>> different tables based on the value of a field named CategoryCode on >>>>> the >>>>> People table record inserted. Any help would be greatly appreciated. >>>>> >>>>> David >>>>> >>>>> >>>>> >>>>> *** Sent via Developersdex http://www.developersdex.com *** >>>> >>>> >>> >>> >> >> > > This is a table whos PK is FK in many other tables. Right now it it too
much work to change the data type. I would just like to have a way to assure uniqueness. Also, this table only has 8,000 records and grows by less than 1,000 per year. The data type is int. David Show quote "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message news:ed5IFBSHGHA.3408@TK2MSFTNGP12.phx.gbl... > Mine was just an example David, use your own, but I wouldn't rely on that > rand to get unique values if I where you because the chances are you'll > get the same one eventually. > > Have you thought about newid() or if you don't like uniqueidentifier, > converting it to char(36). > > SQL Server using uniqueidentifier for replication in merge replication. > > Tony. > > -- > Tony Rogerson > SQL Server MVP > http://sqlserverfaq.com - free video tutorials > > > "David" <dlch***@lifetimeinc.com> wrote in message > news:%23efrs2RHGHA.596@TK2MSFTNGP10.phx.gbl... >> Just a way of creating a random PK so that replication will not cause >> problems with duplicates. Identities don't work well in replication. >> >> p.s. How is your random number generation different from the one in my >> trigger now? >> >> David >> >> "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message >> news:utp6BeRHGHA.3120@TK2MSFTNGP10.phx.gbl... >>> How bizzarre, i wonder what the idea behind it is. >>> >>> Tony. >>> >>> -- >>> Tony Rogerson >>> SQL Server MVP >>> http://sqlserverfaq.com - free video tutorials >>> >>> >>> "David Chase" <dlch***@lifetimeinc.com> wrote in message >>> news:%23eWvHYRHGHA.3144@TK2MSFTNGP11.phx.gbl... >>>> Tony, >>>> The Access Upsizing Wizard created these. >>>> >>>> David >>>> >>>> "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message >>>> news:%23rtKKJRHGHA.3200@tk2msftngp13.phx.gbl... >>>>> Hi David, >>>>> >>>>> You'll need a before and after trigger, something like below. >>>>> >>>>> You you pick the result set up in your client application as a normal >>>>> result set. >>>>> >>>>> I'm interested - what are you using that would create that trigger, >>>>> i've not come across anything in SQL Server that does that. >>>>> >>>>> create table tony ( >>>>> rndnumb int not null, >>>>> othercol int not null >>>>> ) >>>>> go >>>>> >>>>> create trigger xyz_before on tony instead of insert >>>>> as >>>>> begin >>>>> insert tony ( rndnumb, othercol ) >>>>> select rndnumb = rand() * 10000, othercol >>>>> from inserted >>>>> >>>>> end >>>>> go >>>>> >>>>> create trigger xyz_after on tony after insert >>>>> as >>>>> begin >>>>> select * >>>>> from inserted >>>>> >>>>> end >>>>> go >>>>> >>>>> insert tony ( rndnumb, othercol ) values ( 0, 1234 ) >>>>> >>>>> -- >>>>> Tony Rogerson >>>>> SQL Server MVP >>>>> http://sqlserverfaq.com - free video tutorials >>>>> >>>>> >>>>> "David" <da***@lifetime.com> wrote in message >>>>> news:OredsBRHGHA.3700@TK2MSFTNGP15.phx.gbl... >>>>>> It is random because it used to be a table on an Access database that >>>>>> was used for replication. When it was uploaded to SQL Server, then >>>>>> SQL >>>>>> server created this random number trigger. This table (and several >>>>>> others) is used in many applications and inserts are done in a lot of >>>>>> places. I would prefer to stick the randomizing into a stored proc, >>>>>> but >>>>>> currently don't have time to do that. >>>>>> >>>>>> Can someone explain how I would return a "result set" in my trigger >>>>>> that >>>>>> I published? I have tried some CASE and IF statements to send back >>>>>> the >>>>>> @randc variable but my syntax errors out. Also, the reason I need IF >>>>>> or >>>>>> CASE is because after the insert I may need to insert into 1 of 3 >>>>>> different tables based on the value of a field named CategoryCode on >>>>>> the >>>>>> People table record inserted. Any help would be greatly appreciated. >>>>>> >>>>>> David >>>>>> >>>>>> >>>>>> >>>>>> *** Sent via Developersdex http://www.developersdex.com *** >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > I think you said you where or wanted to use replication, if that is the case
you could still use the IDENTITY property. You just need to consider your replication environment and for each node set a range you want to use. You can then use DBCC CHECKIDENT to reseed the table's identity to the start of the range; also, you could write a check constraint (add the NOT FOR REPLICATION property) to keep the ranges correct at each replication node. Tony. Show quote "David Chase" <dlch***@lifetimeinc.com> wrote in message news:uqnc3wUHGHA.208@tk2msftngp13.phx.gbl... > This is a table whos PK is FK in many other tables. Right now it it too > much work to change the data type. I would just like to have a way to > assure uniqueness. Also, this table only has 8,000 records and grows by > less than 1,000 per year. > > The data type is int. > > David > > "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message > news:ed5IFBSHGHA.3408@TK2MSFTNGP12.phx.gbl... >> Mine was just an example David, use your own, but I wouldn't rely on that >> rand to get unique values if I where you because the chances are you'll >> get the same one eventually. >> >> Have you thought about newid() or if you don't like uniqueidentifier, >> converting it to char(36). >> >> SQL Server using uniqueidentifier for replication in merge replication. >> >> Tony. >> >> -- >> Tony Rogerson >> SQL Server MVP >> http://sqlserverfaq.com - free video tutorials >> >> >> "David" <dlch***@lifetimeinc.com> wrote in message >> news:%23efrs2RHGHA.596@TK2MSFTNGP10.phx.gbl... >>> Just a way of creating a random PK so that replication will not cause >>> problems with duplicates. Identities don't work well in replication. >>> >>> p.s. How is your random number generation different from the one in my >>> trigger now? >>> >>> David >>> >>> "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message >>> news:utp6BeRHGHA.3120@TK2MSFTNGP10.phx.gbl... >>>> How bizzarre, i wonder what the idea behind it is. >>>> >>>> Tony. >>>> >>>> -- >>>> Tony Rogerson >>>> SQL Server MVP >>>> http://sqlserverfaq.com - free video tutorials >>>> >>>> >>>> "David Chase" <dlch***@lifetimeinc.com> wrote in message >>>> news:%23eWvHYRHGHA.3144@TK2MSFTNGP11.phx.gbl... >>>>> Tony, >>>>> The Access Upsizing Wizard created these. >>>>> >>>>> David >>>>> >>>>> "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message >>>>> news:%23rtKKJRHGHA.3200@tk2msftngp13.phx.gbl... >>>>>> Hi David, >>>>>> >>>>>> You'll need a before and after trigger, something like below. >>>>>> >>>>>> You you pick the result set up in your client application as a normal >>>>>> result set. >>>>>> >>>>>> I'm interested - what are you using that would create that trigger, >>>>>> i've not come across anything in SQL Server that does that. >>>>>> >>>>>> create table tony ( >>>>>> rndnumb int not null, >>>>>> othercol int not null >>>>>> ) >>>>>> go >>>>>> >>>>>> create trigger xyz_before on tony instead of insert >>>>>> as >>>>>> begin >>>>>> insert tony ( rndnumb, othercol ) >>>>>> select rndnumb = rand() * 10000, othercol >>>>>> from inserted >>>>>> >>>>>> end >>>>>> go >>>>>> >>>>>> create trigger xyz_after on tony after insert >>>>>> as >>>>>> begin >>>>>> select * >>>>>> from inserted >>>>>> >>>>>> end >>>>>> go >>>>>> >>>>>> insert tony ( rndnumb, othercol ) values ( 0, 1234 ) >>>>>> >>>>>> -- >>>>>> Tony Rogerson >>>>>> SQL Server MVP >>>>>> http://sqlserverfaq.com - free video tutorials >>>>>> >>>>>> >>>>>> "David" <da***@lifetime.com> wrote in message >>>>>> news:OredsBRHGHA.3700@TK2MSFTNGP15.phx.gbl... >>>>>>> It is random because it used to be a table on an Access database >>>>>>> that >>>>>>> was used for replication. When it was uploaded to SQL Server, then >>>>>>> SQL >>>>>>> server created this random number trigger. This table (and several >>>>>>> others) is used in many applications and inserts are done in a lot >>>>>>> of >>>>>>> places. I would prefer to stick the randomizing into a stored proc, >>>>>>> but >>>>>>> currently don't have time to do that. >>>>>>> >>>>>>> Can someone explain how I would return a "result set" in my trigger >>>>>>> that >>>>>>> I published? I have tried some CASE and IF statements to send back >>>>>>> the >>>>>>> @randc variable but my syntax errors out. Also, the reason I need >>>>>>> IF or >>>>>>> CASE is because after the insert I may need to insert into 1 of 3 >>>>>>> different tables based on the value of a field named CategoryCode on >>>>>>> the >>>>>>> People table record inserted. Any help would be greatly >>>>>>> appreciated. >>>>>>> >>>>>>> David >>>>>>> >>>>>>> >>>>>>> >>>>>>> *** Sent via Developersdex http://www.developersdex.com *** >>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > Yes, we are currently using merge replication with 4-6 anonymous laptops as
subscribers. I may go to an identity when the company goes to SQL 2005 as we will have to re-create all subscriptions anyway. Do you still think that your suggestion of "rand() * 10000" is superior to what I have now as "convert(int, rand() * power(2, 30))"? Thanks. David Show quote "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message news:OUMhy9ZHGHA.2444@TK2MSFTNGP11.phx.gbl... >I think you said you where or wanted to use replication, if that is the >case you could still use the IDENTITY property. > > You just need to consider your replication environment and for each node > set a range you want to use. > > You can then use DBCC CHECKIDENT to reseed the table's identity to the > start of the range; also, you could write a check constraint (add the NOT > FOR REPLICATION property) to keep the ranges correct at each replication > node. > > Tony. > > -- > Tony Rogerson > SQL Server MVP > http://sqlserverfaq.com - free video tutorials > > > "David Chase" <dlch***@lifetimeinc.com> wrote in message > news:uqnc3wUHGHA.208@tk2msftngp13.phx.gbl... >> This is a table whos PK is FK in many other tables. Right now it it too >> much work to change the data type. I would just like to have a way to >> assure uniqueness. Also, this table only has 8,000 records and grows by >> less than 1,000 per year. >> >> The data type is int. >> >> David >> >> "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message >> news:ed5IFBSHGHA.3408@TK2MSFTNGP12.phx.gbl... >>> Mine was just an example David, use your own, but I wouldn't rely on >>> that rand to get unique values if I where you because the chances are >>> you'll get the same one eventually. >>> >>> Have you thought about newid() or if you don't like uniqueidentifier, >>> converting it to char(36). >>> >>> SQL Server using uniqueidentifier for replication in merge replication. >>> >>> Tony. >>> >>> -- >>> Tony Rogerson >>> SQL Server MVP >>> http://sqlserverfaq.com - free video tutorials >>> >>> >>> "David" <dlch***@lifetimeinc.com> wrote in message >>> news:%23efrs2RHGHA.596@TK2MSFTNGP10.phx.gbl... >>>> Just a way of creating a random PK so that replication will not cause >>>> problems with duplicates. Identities don't work well in replication. >>>> >>>> p.s. How is your random number generation different from the one in my >>>> trigger now? >>>> >>>> David >>>> >>>> "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message >>>> news:utp6BeRHGHA.3120@TK2MSFTNGP10.phx.gbl... >>>>> How bizzarre, i wonder what the idea behind it is. >>>>> >>>>> Tony. >>>>> >>>>> -- >>>>> Tony Rogerson >>>>> SQL Server MVP >>>>> http://sqlserverfaq.com - free video tutorials >>>>> >>>>> >>>>> "David Chase" <dlch***@lifetimeinc.com> wrote in message >>>>> news:%23eWvHYRHGHA.3144@TK2MSFTNGP11.phx.gbl... >>>>>> Tony, >>>>>> The Access Upsizing Wizard created these. >>>>>> >>>>>> David >>>>>> >>>>>> "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message >>>>>> news:%23rtKKJRHGHA.3200@tk2msftngp13.phx.gbl... >>>>>>> Hi David, >>>>>>> >>>>>>> You'll need a before and after trigger, something like below. >>>>>>> >>>>>>> You you pick the result set up in your client application as a >>>>>>> normal result set. >>>>>>> >>>>>>> I'm interested - what are you using that would create that trigger, >>>>>>> i've not come across anything in SQL Server that does that. >>>>>>> >>>>>>> create table tony ( >>>>>>> rndnumb int not null, >>>>>>> othercol int not null >>>>>>> ) >>>>>>> go >>>>>>> >>>>>>> create trigger xyz_before on tony instead of insert >>>>>>> as >>>>>>> begin >>>>>>> insert tony ( rndnumb, othercol ) >>>>>>> select rndnumb = rand() * 10000, othercol >>>>>>> from inserted >>>>>>> >>>>>>> end >>>>>>> go >>>>>>> >>>>>>> create trigger xyz_after on tony after insert >>>>>>> as >>>>>>> begin >>>>>>> select * >>>>>>> from inserted >>>>>>> >>>>>>> end >>>>>>> go >>>>>>> >>>>>>> insert tony ( rndnumb, othercol ) values ( 0, 1234 ) >>>>>>> >>>>>>> -- >>>>>>> Tony Rogerson >>>>>>> SQL Server MVP >>>>>>> http://sqlserverfaq.com - free video tutorials >>>>>>> >>>>>>> >>>>>>> "David" <da***@lifetime.com> wrote in message >>>>>>> news:OredsBRHGHA.3700@TK2MSFTNGP15.phx.gbl... >>>>>>>> It is random because it used to be a table on an Access database >>>>>>>> that >>>>>>>> was used for replication. When it was uploaded to SQL Server, then >>>>>>>> SQL >>>>>>>> server created this random number trigger. This table (and several >>>>>>>> others) is used in many applications and inserts are done in a lot >>>>>>>> of >>>>>>>> places. I would prefer to stick the randomizing into a stored >>>>>>>> proc, but >>>>>>>> currently don't have time to do that. >>>>>>>> >>>>>>>> Can someone explain how I would return a "result set" in my trigger >>>>>>>> that >>>>>>>> I published? I have tried some CASE and IF statements to send back >>>>>>>> the >>>>>>>> @randc variable but my syntax errors out. Also, the reason I need >>>>>>>> IF or >>>>>>>> CASE is because after the insert I may need to insert into 1 of 3 >>>>>>>> different tables based on the value of a field named CategoryCode >>>>>>>> on the >>>>>>>> People table record inserted. Any help would be greatly >>>>>>>> appreciated. >>>>>>>> >>>>>>>> David >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> *** Sent via Developersdex http://www.developersdex.com *** >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > Nope, your's is probably better, however, i'd still not use RAND() if it
where me. Tony. Show quote "David Chase" <dlch***@lifetimeinc.com> wrote in message news:%23F80iqeHGHA.140@TK2MSFTNGP12.phx.gbl... > Yes, we are currently using merge replication with 4-6 anonymous laptops > as subscribers. I may go to an identity when the company goes to SQL 2005 > as we will have to re-create all subscriptions anyway. > Do you still think that your suggestion of "rand() * 10000" is superior to > what I have now as "convert(int, rand() * power(2, 30))"? > Thanks. > > David > > "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message > news:OUMhy9ZHGHA.2444@TK2MSFTNGP11.phx.gbl... >>I think you said you where or wanted to use replication, if that is the >>case you could still use the IDENTITY property. >> >> You just need to consider your replication environment and for each node >> set a range you want to use. >> >> You can then use DBCC CHECKIDENT to reseed the table's identity to the >> start of the range; also, you could write a check constraint (add the NOT >> FOR REPLICATION property) to keep the ranges correct at each replication >> node. >> >> Tony. >> >> -- >> Tony Rogerson >> SQL Server MVP >> http://sqlserverfaq.com - free video tutorials >> >> >> "David Chase" <dlch***@lifetimeinc.com> wrote in message >> news:uqnc3wUHGHA.208@tk2msftngp13.phx.gbl... >>> This is a table whos PK is FK in many other tables. Right now it it too >>> much work to change the data type. I would just like to have a way to >>> assure uniqueness. Also, this table only has 8,000 records and grows by >>> less than 1,000 per year. >>> >>> The data type is int. >>> >>> David >>> >>> "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message >>> news:ed5IFBSHGHA.3408@TK2MSFTNGP12.phx.gbl... >>>> Mine was just an example David, use your own, but I wouldn't rely on >>>> that rand to get unique values if I where you because the chances are >>>> you'll get the same one eventually. >>>> >>>> Have you thought about newid() or if you don't like uniqueidentifier, >>>> converting it to char(36). >>>> >>>> SQL Server using uniqueidentifier for replication in merge replication. >>>> >>>> Tony. >>>> >>>> -- >>>> Tony Rogerson >>>> SQL Server MVP >>>> http://sqlserverfaq.com - free video tutorials >>>> >>>> >>>> "David" <dlch***@lifetimeinc.com> wrote in message >>>> news:%23efrs2RHGHA.596@TK2MSFTNGP10.phx.gbl... >>>>> Just a way of creating a random PK so that replication will not cause >>>>> problems with duplicates. Identities don't work well in replication. >>>>> >>>>> p.s. How is your random number generation different from the one in my >>>>> trigger now? >>>>> >>>>> David >>>>> >>>>> "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message >>>>> news:utp6BeRHGHA.3120@TK2MSFTNGP10.phx.gbl... >>>>>> How bizzarre, i wonder what the idea behind it is. >>>>>> >>>>>> Tony. >>>>>> >>>>>> -- >>>>>> Tony Rogerson >>>>>> SQL Server MVP >>>>>> http://sqlserverfaq.com - free video tutorials >>>>>> >>>>>> >>>>>> "David Chase" <dlch***@lifetimeinc.com> wrote in message >>>>>> news:%23eWvHYRHGHA.3144@TK2MSFTNGP11.phx.gbl... >>>>>>> Tony, >>>>>>> The Access Upsizing Wizard created these. >>>>>>> >>>>>>> David >>>>>>> >>>>>>> "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message >>>>>>> news:%23rtKKJRHGHA.3200@tk2msftngp13.phx.gbl... >>>>>>>> Hi David, >>>>>>>> >>>>>>>> You'll need a before and after trigger, something like below. >>>>>>>> >>>>>>>> You you pick the result set up in your client application as a >>>>>>>> normal result set. >>>>>>>> >>>>>>>> I'm interested - what are you using that would create that trigger, >>>>>>>> i've not come across anything in SQL Server that does that. >>>>>>>> >>>>>>>> create table tony ( >>>>>>>> rndnumb int not null, >>>>>>>> othercol int not null >>>>>>>> ) >>>>>>>> go >>>>>>>> >>>>>>>> create trigger xyz_before on tony instead of insert >>>>>>>> as >>>>>>>> begin >>>>>>>> insert tony ( rndnumb, othercol ) >>>>>>>> select rndnumb = rand() * 10000, othercol >>>>>>>> from inserted >>>>>>>> >>>>>>>> end >>>>>>>> go >>>>>>>> >>>>>>>> create trigger xyz_after on tony after insert >>>>>>>> as >>>>>>>> begin >>>>>>>> select * >>>>>>>> from inserted >>>>>>>> >>>>>>>> end >>>>>>>> go >>>>>>>> >>>>>>>> insert tony ( rndnumb, othercol ) values ( 0, 1234 ) >>>>>>>> >>>>>>>> -- >>>>>>>> Tony Rogerson >>>>>>>> SQL Server MVP >>>>>>>> http://sqlserverfaq.com - free video tutorials >>>>>>>> >>>>>>>> >>>>>>>> "David" <da***@lifetime.com> wrote in message >>>>>>>> news:OredsBRHGHA.3700@TK2MSFTNGP15.phx.gbl... >>>>>>>>> It is random because it used to be a table on an Access database >>>>>>>>> that >>>>>>>>> was used for replication. When it was uploaded to SQL Server, then >>>>>>>>> SQL >>>>>>>>> server created this random number trigger. This table (and >>>>>>>>> several >>>>>>>>> others) is used in many applications and inserts are done in a lot >>>>>>>>> of >>>>>>>>> places. I would prefer to stick the randomizing into a stored >>>>>>>>> proc, but >>>>>>>>> currently don't have time to do that. >>>>>>>>> >>>>>>>>> Can someone explain how I would return a "result set" in my >>>>>>>>> trigger that >>>>>>>>> I published? I have tried some CASE and IF statements to send >>>>>>>>> back the >>>>>>>>> @randc variable but my syntax errors out. Also, the reason I need >>>>>>>>> IF or >>>>>>>>> CASE is because after the insert I may need to insert into 1 of 3 >>>>>>>>> different tables based on the value of a field named CategoryCode >>>>>>>>> on the >>>>>>>>> People table record inserted. Any help would be greatly >>>>>>>>> appreciated. >>>>>>>>> >>>>>>>>> David >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> *** Sent via Developersdex http://www.developersdex.com *** >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > David (da***@lifetime.com) writes:
> Can someone explain how I would return a "result set" in my trigger that That's just a SELECT statement like anywhere else.> I published? > I have tried some CASE and IF statements to send back the I suspect, though, that with only snippets of information of what you> @randc variable but my syntax errors out. Also, the reason I need IF or > CASE is because after the insert I may need to insert into 1 of 3 > different tables based on the value of a field named CategoryCode on the > People table record inserted. Any help would be greatly appreciated. are doing, that the suggestions here may not be wholly on target. I would probably help, if you gave us the full pictore. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.seBooks Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >> I have a trigger on a table that creates a random number for the Primary Key. << Not a good idea because you can get duplicate values and you aredealing with floating point numbers. Here is an implementation of the additive congruential method of generating values in pseudo-random order and is due to Roy Hann of Rational Commerce Limited, a CA-Ingres consulting firm. It is based on a shift-register and an XOR-gate, and it has its origins in cryptography. While there are other ways to do this, this code is nice because: 1) The algorithm can be written in C or another low level language for speed. But math is fairly simple even in base ten. 2) The algorithm tends to generate successive values that are (usually) "far apart", which is handy for improving the performance of tree indexes. You will tend to put data on separate physical data pages in storage. 3) The algorithm does not cycle until it has generated every possible value, so we don't have to worry about duplicates. Just count how many calls have been made to the generator. 4) The algorithm produces uniformly distributed values, which is a nice mathematical property to have. It also does not include zero. Generalizing the algorithm to arbitrary binary word sizes, and therefore longer number sequences, is not as easy as you might think. Finding the "tap" positions where bits are extracted for feedback varies according to the word-size in an extremely non-obvious way. Choosing incorrect tap positions results in an incomplete and usually very short cycle, which is unusable. If you want the details and tap positions for words of one to 100 bits, see E. J. Watson, "Primitive Polynomials (Mod 2)", Mathematics of Computation, v.16, 1962, p.368-369. We need to tap bits 0 and 3 to construct the 31-bit random-order generated value Generator (which is the one most people would want to use in practice): UPDATE Generator31 SET keyval = keyval/2 + MOD(MOD(keyval, 2) + MOD(keyval/8, 2), 2)*2^30; Or if you prefer, the algorithm in C: int Generator31 () {static int n = 1; n = n >> 1 | ((n^n >> 3) & 1) << 30; return n; } Couple of problems...
1) There is no MOD function in SQL Server so your SQL as it stands won't work, you need to write it like this instead.... UPDATE #t SET keyval = keyval/2 + ( ( keyval % 2) + (keyval/8 % 2) % 2)*2^30; 2) The value of keyval needs to be unique to start with so whats the value of doing this again? Show quote "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1137693338.725041.167860@f14g2000cwb.googlegroups.com... >>> I have a trigger on a table that creates a random number for the >>> Primary Key. << > > Not a good idea because you can get duplicate values and you are > dealing with floating point numbers. Here is an implementation of the > additive congruential method of generating values in pseudo-random > order and is due to Roy Hann of Rational Commerce Limited, a CA-Ingres > consulting firm. It is based on a shift-register and an XOR-gate, and > it has its origins in cryptography. While there are other ways to do > this, this code is nice because: > > 1) The algorithm can be written in C or another low level language for > speed. But math is fairly simple even in base ten. > > 2) The algorithm tends to generate successive values that are (usually) > "far apart", which is handy for improving the performance of tree > indexes. You will tend to put data on separate physical data pages in > storage. > > 3) The algorithm does not cycle until it has generated every possible > value, so we don't have to worry about duplicates. Just count how many > calls have been made to the generator. > > 4) The algorithm produces uniformly distributed values, which is a nice > mathematical property to have. It also does not include zero. > > Generalizing the algorithm to arbitrary binary word sizes, and > therefore longer number sequences, is not as easy as you might think. > Finding the "tap" positions where bits are extracted for feedback > varies according to the word-size in an extremely non-obvious way. > Choosing incorrect tap positions results in an incomplete and usually > very short cycle, which is unusable. If you want the details and tap > positions for words of one to 100 bits, see E. J. Watson, "Primitive > Polynomials (Mod 2)", Mathematics of Computation, v.16, 1962, > p.368-369. > > We need to tap bits 0 and 3 to construct the 31-bit random-order > generated value Generator (which is the one most people would want to > use in practice): > > UPDATE Generator31 > SET keyval = > keyval/2 + MOD(MOD(keyval, 2) + MOD(keyval/8, 2), 2)*2^30; > > Or if you prefer, the algorithm in C: > > int Generator31 () > {static int n = 1; > n = n >> 1 | ((n^n >> 3) & 1) << 30; > return n; > } >  |
|||||||||||||||||||||||
Other interesting topics