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.
| Author |
Topic |
|
Ex
Posting Yak Master
166 Posts |
 Posted - 2005-01-16 : 23:36:05
|
| hey allok here is the latest problemCREATE TRIGGER UPDATEGRADE ON GRADEAFTER UPDATE AS UPDATE YIELD_STRESS_OFFSETS SET GRADE = i.GRADE FROM YIELD_STRESS_OFFSETS y,inserted i, deleted d WHERE y.GRADE = d.GRADE AND i.GID = d.GIDGrade PK (GRADE ) GID is a identity Column i added for simplicitiy on joins as "they" update the pk value :(YIELD_STRESS_OFFSETS FK(GRADE) REFERENCES GRADE(grade)i know this is a messy table hehe but i didnt design the buggarjust wondering how can i do the update here without stuffing the refence integrity if i update grade first then YIELD_STRESS_OFFSETS wont see the grade and throw a FK excpetionand if update YIELD_STRESS_OFFSETS the grade wont be in GRADE and a FK exception will be thrownthe only way i can think of doing this is do a instead of update trigger like soCREATE TRIGGER UPDATEGRADE ON GRADEINSTEAD OF UPDATE ASinsert into grade(GRADE,DESCRIPTION,GRADEFACTOR,GROUPNAME)select GRADE,DESCRIPTION,GRADEFACTOR,GROUPNAME from inserted UPDATE YIELD_STRESS_OFFSETS SET GRADE = i.GRADE FROM YIELD_STRESS_OFFSETS y,inserted i, deleted d WHERE y.GRADE = d.GRADE AND i.GID = d.GIDdelete from GRADE where grade in (select grade from deleted)is there a better way to do it? |
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2005-01-18 : 19:37:32
|
What about creating the foreign key constraint with nocheck? |
 |
|
|
Ex
Posting Yak Master
166 Posts |
Posted - 2005-01-18 : 19:44:53
|
| ah its all good got it worked out do insert/delete combo when they change the pk :) otherwise normal update thanks anyways |
|
|
|
|
|
|
FK integrity