> S. Isaac Dealey wrote: >> include the entire server -- it's real easy if you just >> create a >> trigger that inserts into a table in another db on the >> same server
> only in your db session. if what you're saying is true, no > multi-user db > would ever work period. I'm just saying that if the table being inserted into contains a trigger that inserts into a table in any database on the server that contains another identity column, @@identity will return the value from that trigger-inserted table. If there aren't any triggers involved in the statement or they don't insert data into tables with identity columns, or if you don't use the @@identity variable, then there's no reason to have any problem with the db related to @@identity. That being the case, the only solution that makes sense (to me) is either to avoid @@identity without question, or to avoid triggers without question, because if you use both, you always have the potential of it becoming a problem when someone needs to insert a value with a trigger and the code is chock-full of references to @@identity which may be in CF or may be in stored procedures or may be in other places. Given that MS created an alternative to @@identity specifically because of this problem, I chose to use that alternative rather than give up the possibility of using triggers. >> question. Although by and large I avoid identity columns >> all-together >> these days. > every major db has them, these are by & large pretty > useful. Do you not consider Oracle a major db? Oracle has sequences which are not the same thing. The problem with identities using SQL Server is that they're only useful / helpful during the insert -- they're a hideous, horrible, awful nightmare if you ever have to try and migrate data from one server to another -- which I'm guessing is why SQL Server's DTS drops the identities by default when migrating data (which in my experience only causes more problems). We were dealing with that just not too long ago at my 9-5 job. Trying to find an elegant way to create copies of our prototype database when we set up a new project (which -- the prototype db should be our model db so that new db's are created with its structure automatically, but that's a whole other story). In any event, because we couldn't find a good solution to the problem with SQL Server identity columns, our current solution is a CF script that detaches the db from the sql server and then reattaching it with a new name. This means hollering through the office for everyone to disconnect from Enterprise Manager or Query Analyzer each time we create a new client project. s. isaac dealey 954.927.5117 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.sys-con.com/story/?storyid=44477&DE=1 http://www.sys-con.com/story/?storyid=45569&DE=1 http://www.fusiontap.com ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Special thanks to the CF Community Suite Silver Sponsor - RUWebby http://www.ruwebby.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:187205 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
