|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Update Col2 based on Col1(IDENTITY) ??There is a second column "CATID" which i need to do essentially the following: <pseduocode> TRIGGER AFTER INSERT ON CAT UPDATE CAT SET CATID = 'CAT'+CONVERT(VARCHAR,ROWID) but this doesnt work, because the IDENTITIES havent been set yet. So i figured ill try just making CATID a computed column with formula of: 'CAT'+CONVERT(VARCHAR,[ROWID]) Only problem is, i need to use this column in a foreign key, which isnt possible on a computed column. So how can i do the trigger code above? I dont want to use the @@IDENTITY or any of those, because of the case where the trigger might be firing for multiple rows. Thanks in advance > - Arthur Dent. > TRIGGER AFTER INSERT ON CAT WHY DO YOU NEED TO STORE THIS?> UPDATE CAT SET CATID = 'CAT'+CONVERT(VARCHAR,ROWID) Create a VIEW. SELECT ROWID, CATID = 'CAT'+CONVERT(VARCHAR(12), ROWID) FROM CAT > Only problem is, i need to use this column in a foreign key, which isnt Why is CAT5 or CAT162 a better usage of the foreign key than 5 or 162? Why > possible on a > computed column. is this computed value based on a constant and a surrogate used as a primary key in any table? A I wanted to use this on this table. Then i had a PROD table which did the
same thing, but prepended 'PRD' instead of 'CAT'. These then are both used as foreign keys into a single table which stores ATTRIBUTES for both Categories and Products. This would be keyed on an ITEMID which would be such as: PRD1, CAT4, PRD23, CAT62, so it needs unique keys between the PRD and CAT tables, which, if they each just PK on an identity column, they wont have. Unless there is some way to have two tables SHARE an identity column's incements? so when TblA added a row, the IDENT in TblB would also get bumped up? Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:OFytqk$gFHA.2840@tk2msftngp13.phx.gbl... >> TRIGGER AFTER INSERT ON CAT >> UPDATE CAT SET CATID = 'CAT'+CONVERT(VARCHAR,ROWID) > > WHY DO YOU NEED TO STORE THIS? > > Create a VIEW. > > SELECT ROWID, CATID = 'CAT'+CONVERT(VARCHAR(12), ROWID) FROM CAT > >> Only problem is, i need to use this column in a foreign key, which isnt >> possible on a >> computed column. > > Why is CAT5 or CAT162 a better usage of the foreign key than 5 or 162? > Why is this computed value based on a constant and a surrogate used as a > primary key in any table? > > A > > This would be keyed on an ITEMID which would be such as: PRD1, CAT4, Why not have ProductID and CategoryID, each of them a foreign key to > PRD23, CAT62, so it needs unique keys > between the PRD and CAT tables, which, if they each just PK on an identity > column, they wont have. Products and Categories, where one of them has to be valid and one of them has to be NULL? Or, you could design things a bit better and store product details and category details in their own table? This scheme seems very messy and will be impossible to program around. Ugh. > Unless there is some way Well, you could simulate Oracle's SEQUENCE table, but again, ugh.> to have two tables SHARE an identity column's incements? Well, i was just trying to keep the db a little cleaner, there are at least
3 or four tables which need to cover both products and categories, with the same structure in each mode, so i was trying to keep the db to 3 or 4 tables, instead of 6 or 8 tables, it would be less to maintain, and more consistent sql statements. But, if it cant be done, well, duplication itll be i guess. Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:%23332Qs$gFHA.3608@TK2MSFTNGP12.phx.gbl... >> This would be keyed on an ITEMID which would be such as: PRD1, CAT4, >> PRD23, CAT62, so it needs unique keys >> between the PRD and CAT tables, which, if they each just PK on an >> identity column, they wont have. > > Why not have ProductID and CategoryID, each of them a foreign key to > Products and Categories, where one of them has to be valid and one of them > has to be NULL? > > Or, you could design things a bit better and store product details and > category details in their own table? > > This scheme seems very messy and will be impossible to program around. > Ugh. > >> Unless there is some way >> to have two tables SHARE an identity column's incements? > > Well, you could simulate Oracle's SEQUENCE table, but again, ugh. > > Well, i was just trying to keep the db a little cleaner, there are at Why?> least 3 or four tables which need to cover both products and categories, > same structure in each mode, so i was trying to keep the db to 3 or 4 If you have different types of entities, you should have different tables. > tables, instead of 6 or 8 tables, it would be less to maintain, and more > consistent sql statements. If they're identical as you suggest, why would they be harder to maintain? So your product table has the exact same attributes as your category table?
Then you probably aren't making full use of the database then. The more tables the better if one table could represent multiple things and you have to decide at usage time what kind of thing you are representing. Plus SQL works better the smaller things are and with better indexes. An index with a couple of values is far worse generally that multiple tables partitioned on the couple of values. -- Show quote---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP "Arthur Dent" <hitchhikersguideto-n***@yahoo.com> wrote in message news:Okn9hx$gFHA.3300@TK2MSFTNGP15.phx.gbl... > Well, i was just trying to keep the db a little cleaner, there are at > least 3 or four tables which need to cover both products and categories, > with the same structure in each mode, so i was trying to keep the db to 3 > or 4 tables, instead of 6 or 8 tables, it would be less to maintain, and > more consistent sql statements. > > But, if it cant be done, well, duplication itll be i guess. > > "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in > message news:%23332Qs$gFHA.3608@TK2MSFTNGP12.phx.gbl... >>> This would be keyed on an ITEMID which would be such as: PRD1, CAT4, >>> PRD23, CAT62, so it needs unique keys >>> between the PRD and CAT tables, which, if they each just PK on an >>> identity column, they wont have. >> >> Why not have ProductID and CategoryID, each of them a foreign key to >> Products and Categories, where one of them has to be valid and one of >> them has to be NULL? >> >> Or, you could design things a bit better and store product details and >> category details in their own table? >> >> This scheme seems very messy and will be impossible to program around. >> Ugh. >> >>> Unless there is some way >>> to have two tables SHARE an identity column's incements? >> >> Well, you could simulate Oracle's SEQUENCE table, but again, ugh. >> > >  |
|||||||||||||||||||||||
Other interesting topics