help with a trigger....
-
noel - thank you so very kindly for your solution...unfortunately the design of the tables is so evil that after beating my brains out with this darn thing...I am stopping all attempts at being clever and going with the ifs and the cases...
i was supposed to have completed this 3 days ago and my head is still hurting - i'm going cross-eyed from the pain of pivoting and spinning it around on its' axis and cross joining et al...
there will never be a time when the client will say..."take the time to tear this down and redo it"....so there goes the perpetuation of yet another nasty beasty database...
**ASCII stupid question, get a stupid ANSI !!!** -
That's why we must start them on the right track when we have a chance
. -
Yes - but it wasn't I that won that design contest I can assure you...
**ASCII stupid question, get a stupid ANSI !!!** -
Always how it happens.
-
Let me try to dissect a bit what I posted to see if it makes sense for you:
tblReport( PicID --Picture
, 100
, 110 -- Line
, 120 -- Line
, 121 -- Line
, 122
, 123
, 130
, 200 -- Line
...)
tblBudgetRate( lineID int -- Line#
, new_rate_110 -- YOU POSTED THIS but apparently
, new_rate_120 -- those Pictures are NOT in the lookup table
...
, new_rate_210 -- I assumed this were the right PICURE IDS
, new_rate_211
, new_rate_212 -- Picture
, new_rate_213 -- Picture
, new_rate_222
, new_rate_223
, new_rate_230
, new_rate_231 -- Picture
...)
Now the code:
Update set new_rate_210 = case when T.PicNumber = 210 then T.rate_210 else new_rate_210 end
,new_rate_211 = case when T.PicNumber = 211 then T.rate_211 else new_rate_210 end
,new_rate_212 = case when T.PicNumber = 212 then T.rate_212 else new_rate_210 end
This part will check which Picture ID was selected and update the COLUMN ( all rows ) for
the pictIDs in the inserted table. If the PictID was not modified the column is updated
to itself meaning nothing change!
tblBudgetRate b
this is just the Destination Table, Right? !!
join
(
selec i.rate_210
,i.rate_211
...
-- Bring from the inserted the Rates that changed (or not) but the row was affected
, p.PicNumber
, l.LineID
-- Again PicNumber is for the COLUMN update and LineID is for joining to the Destination Budget Rate!!
from
inserted i
join
tblPicsLookup p on i.PickID = p.PicID
-- Up to here I used the PicsLookup to conver PicID to PickNumber needed on the case statements above!!
cross join
tblLineLookup l --bring all lineIDs in to Pivot on them
-- This cross join creates one row per Line# giving you either 26 or 52 records depending on how many updates were performed
-- (one or two rows ) respectively
) T
ON b.LineID = T.LineID
-- Simply For all Line Numbers Update those columns!!!
* Noel -
noel - you're a nice, kind person to persevere with this stuff that should only be my headache since i'm stuck with it...
Update set new_rate_210 = case when T.PicNumber = 210 then T.rate_210 else new_rate_210 end
,new_rate_211 = case when T.PicNumber = 211 then T.rate_211 else new_rate_210 end
,new_rate_212 = case when T.PicNumber = 212 then T.rate_212 else new_rate_210 end....
this logic is actually....
Update set new_rate_210 =
case when LineNumber = 110 then rate_110
case when LineNumber = 120 then rate_120
case when LineNumber = 110 then rate_121
case when LineNumber = 120 then rate_122
etc....
ditto for new_rate_211, new_rate_212 etc....
at any rate since there is only one row updated at any given time i'm just storing all the rates into variables and updating the budgetRate table with case statements....
anything else for this situation would be overkill and a waste of time...
in retrospect i should not even have posted this nonsense...
can we delete an entire thread if we start it ???????????
**ASCII stupid question, get a stupid ANSI !!!** -
Unfortunaltely not
. -
Ok if it works, that's fine! I just don't like the variables because triggers are meant to be always multirow (front end can and most of the time will change) so multirow triggers are unbreakable ... did I just sounded like Larry Elison?
* Noel -
you can sound like him as much as you want...just don't try to emulate his lifestyle...i think we all like you just the way you are...
**ASCII stupid question, get a stupid ANSI !!!** -
I don't really like him but wouldn't mind to make one half of what i makes
* Noel -
Who's that guy?
-
you mean half of what he makes right ?!
ultimately it's only money...along your lines of "good, fast and cheap" if there were a choice between nice and rich, i'd take nice any day...but then that's me!
**ASCII stupid question, get a stupid ANSI !!!** -
There are a few advantages of being rich too
. -
**ASCII stupid question, get a stupid ANSI !!!** -
Thanx.
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply