lesser of 2 evils - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

lesser of 2 evils

Author

Topic

Ex
Posting Yak Master

166 Posts

Posted - 2005-01-06 : 22:34:18

hey all

i got a slight problem

unforuntaly this is really bad database design but i am just maintaining it and need to really keep it the same

ok i have a table
DIMENSION (DimensionNo,EntityItemID,MajorBuildNo,MinorBuildNo,DefineID) with PK (DimensionNo,EntityItemID,MajorBuildNo,MinorBuildNo )
in the code there is a update statment that updates
DefineID = :DefineID,
DimensionNo = :DimensionNo,
EntityItemID = :EntityItemID

now i realise it is updating the pk which is not a thing to do and trust me if i had a choice i wouldn't

now i have a trigger instead of update that does
this

UPDATE DIMENSION SET
DefineID = i.DefineID
FROM DIMENSION dim
JOIN inserted i on
dim.EntityItemID= i.EntityItemID AND dim.MAJORBUILDNO = i.MAJORBUILDNO AND
dim.MINORBUILDNO = i.MINORBUILDNO AND dim.DimensionNo = i.DimensionNo

among other things of cource

so i was wondering is there anyway to do this update statment within the trigger withouth using a cursor or creating a identity coloum?
or are they the only 2 ways?

could i union or join the deleted and inserted tables somehow to get this update to work?

any ideas

Ex
Posting Yak Master

166 Posts

Posted - 2005-01-06 : 22:42:12

dont worrie i think i might just do it the correct way and put a identity col in there :) bit more work but oh well better to do it the right way

just out of interest is there anyway to do what i asked before?

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-07 : 01:30:28

>> could i union or join the deleted and inserted tables somehow to get this update to work?

>> so i was wondering is there anyway to do this update statment within the trigger withouth using a cursor or creating a identity coloum?
or are they the only 2 ways?
A cursor wouldn't be guaranteed to work. You need the PK to be non-updateable.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-01-07 : 10:53:54

Maybe I'm missing something, but why not do an INSERT and then a DELETE?
Save the state of the existing record, change the PK values, insert the new row into the table, delete the original.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources