help with a trigger....
-
I need to set a trigger on a table that with each transaction will update either 1 or max 2 IDs...
I need the values of these 2 IDs so I can use them to update another table...
I need to first find out...
1) if count(ID) from inserted is one or two
2) if count(ID) = 1 then @ID1 = ID
3) if count(ID) = 2 then @ID1 = min(ID) from inserted
and @ID2 = max(ID) from inserted
Any/all help much appreciated!
**ASCII stupid question, get a stupid ANSI !!!** -
How about :
Select @ID1 = -1, @ID2 = -1
Select @ID1 = min(id), @ID2 = max(id) from inserted
Update whatever set col = '??' where id in (@ID1, @ID2)
-
hmm - so if it's 1 ID then min and max would both be the same right ?!
maybe i don't need the Select @ID1 = -1, @ID2 = -1 defaults at all..?!?!
anyway...at least you have me headed in the right direction...wasn't thinking straight - thanks remi!
**ASCII stupid question, get a stupid ANSI !!!** -
in (null) usually fails, that's why I ALWAYS set my variables... but I agree it's not really necessary here.
yes if there's only 1 row then you get the same value in each variable, then the in would actually have a single valid value.
Why are you trying to do this exactly?
-
Why are you trying to do this exactly...long story...legacy database..have to take rows from one table and update columns in another...will probably be back with some more stupid "how to" questions...
it's something like ...where ID in Inserted is 211, then update table where the column name is new_rate_211, if ID is 212 then update where column name is new_rate_212...and so on...think I'm just going to go with a whole bunch of "if ID = ###" etc...
hey...i just noticed something funny - your post count is 4401 and mine is reverse(4401)...
**ASCII stupid question, get a stupid ANSI !!!** -
You guys are mirror images of each other?
-
Hey mike...wouldn't mind being a mirror image of remi's brain...
- otherwise - not that i have anything against him - but thanks but no thanks!haven't "seen" you for a while...do you know that I am on the verge of becoming "extinct" on this site ?! I (stupidly) pronounced a death sentence on myself (another long story)...and soon I am to emerge as a new gender neutral persona...
**ASCII stupid question, get a stupid ANSI !!!** -
I have to admit much to my dismay I have been pursing an honest dollar, a new contract in a different time zone is driving me nuts and costing me sleep. I have done a little lurking but have not had the time to do more than hang in the background and watch.
Yes I saw mention of your assuming another identity I guess the superwomen suite is getting a little heavy. Going around all day with the big red S on your chest must be tough.
But even with a new hairstyle, glasses and a fake mustache I think that it will take little more than a day to unmask you. A bright smile and a good heart are hard to hide.
Mike
-
What's that supposed to mean??
-
Remi if You had a body like her's you would look well a little strange.
Ask one of the guys at work to explain the difference between big boys and big girls. 
Mike
{edit} With a little luck one of your co-workers will have a picture book and will point out some of the finner details
-
Reminds me of a story I read the other day. The guy wanted to order parts from a company. So without searching for it he just typed the url : http://www.companyName.com. The real kicker is that he wanted to order stuff from Hustler. He said that he must be the only guy in USA that didn't know that other company
. Maybe I'll go there and check out the details of those finer points 
. -
okay remi...now that we've gotten all the funny stories out of the way...do you feel like looking at some really lousy t-sql that i have to write to make this trigger work ?! i know how to make it work...just can't think of a "better way" right now because everytime i look at all these stupid tables i'm stuck with, my head just spins...???
in fact i wouldn't even know how to spell out the details because it is so darn convoluted (& you know i'm never at a loss for words...)
**ASCII stupid question, get a stupid ANSI !!!** -
That would be a first for you
.Sure start a new thread and I'll check it out.
-
remi...am using same thread...it is about the same subject....
I have 5 tables in all that're involved in this trigger:
1) 2 tables are lookup tables that have the following columns and values:
a) tblPicLookup(picID int, picNumber char(3))
values:
1210
2211
3212
4213
5222
6223
7230
8231.....and so on (36 rows in all)
b) tblLineLookup(lineID int, lineNumber char(3))
values:
1100
2110
3120
4121
5122
6123
7130
8200...and so on (26 rows in all)
2) In the application there're 12 spreadsheets that are distinguished by picNumber and lineNumber:
a) Some spreadsheets deal with 2 picNumbers at a time and some with just 1..
so there are Pics 210/211; Pic 212; Pic 213;Pics 240/241....etc...
b) ALL spreadsheets have rates for lines 1....26
3) Each PicNumber has a corresponding row in - tblReport -
tblReport(PicID int, rate_100 decimal(18, 2), rate_110 decimal(18, 2)...etc...)
when picSheet 210/211 is saved, 2 rows are updated
when picSheet 212 is saved, 1 row is updated
4) Now comes the 4th table - the one that needs to be updated based on tblReport Update...let's call this tblBudgetRate.....
tblBudgetRate(lineID int, new_rate_110 decimal(18, 2), new_rate_120 decimal(18, 2)...etc....)
This table would have to be updated 26 times...once for each lineID.....
even though the char(3) values in the 2 tables look the same, (they're confusing because the Pic numbers and the line Numbers look the same)...they're actually not....
if picSheet 212 is updated....I would take:
rate_110 and update tblBudgetRate new_rate_212 with rate_212 from tblReport (aka Inserted) WHERE lineID = (SELECT lineID FROM tblLineLookup WHERE lineNumber = '110');
rate_120 and update tblBudgetRate new_rate_212 with rate_212 from tblReport (aka Inserted) WHERE lineID = (SELECT lineID FROM tblLineLookup WHERE lineNumber = '120');....etc...
...WHERE PicID = @PicID1/212....would use this only if 2 picSheets were updated at the same time, else would have only one picID in my Inserted....aka "magic table"
.....I could hardcode all the lineIDs and the PicIDs for convenience & readability since they come from lookup tables that've been used for centuries and are not going to change.....
5) The 5th table I mentioned is irrelevant because it is used to obtain the budgetRateID.....and I included it only for providing the complete picture!
I want to know if there is a "better way" to do this than write 26 updates per picID ?!?!?!
stupid, stupid (talking to myself here)...only one row gets updated each time...duh!...at any rate..it's always going to be one picID... sorry... think I'm going brain dead from staring at all these darn numbers....not wrapped in a transaction...but i didn't write this....
**ASCII stupid question, get a stupid ANSI !!!** -
Not sure I followed all the way because DDL of ALL tables was not completed but here is a go:
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
...
from
tblBudgetRate b
join
(
selec i.rate_210
,i.rate_211
...
, p.PicNumber
, l.LineID
from
inserted i
join
tblPicsLookup p on i.PickID = p.PicID
cross join
tblLineLookup l --bring all lineIDs in to Pivot on them
) T
ON b.LineID = T.LineID -- Use all Lines ?
btw: who won that "design" contest?
* Noel
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply