Here i have a requirement like biddin db structor is like
Id Name Amount Bidding_Level
1 Apple 50 L3
2 Mac 30 L2
3 Nokia 10 L1
Here my Requirement is whenever user Enter amount Bidding_Level Comes dynamic Like if any user enter Amount as 5 He will Become L1 And all other Like as Nokia L2 Mac L3 Apple L4
First of all, as Damien suggested, it seems that this value shouldn't be stored but computed when needed. When you have computed columns (that don't automatically update) then you are begging for additional maintenance tasks (what happens if someone changes the product name but keeps the same ID? and when someone deletes a bid?) and pointless processing (you might calculate the bid level and recieve another bid before actually needing the current bid level).
That being said, you can reset the bid ranking with a trigger, which will have to be after inserts, updates or deletes (you don't explain your business logic much).
CREATE TRIGGER tr_SetBiddingLevel ON Bids AFTER INSERT, UPDATE, DELETE
AS
BEGIN
-- If the amount was updated, a new row was added or a row was deleted
IF UPDATE(Amount) OR (
EXISTS (SELECT 1 FROM deleted) AND
NOT EXISTS (SELECT 1 FROM inserted))
BEGIN
;WITH NewBidLevels AS
(
SELECT
B.Id,
NewBidLevel = ROW_NUMBER() OVER (ORDER BY B.Amount ASC)
FROM
Bids AS B
)
UPDATE B SET
Bidding_Level = 'L' + CONVERT(VARCHAR(10), N.NewBidLevel)
FROM
Bids AS B
INNER JOIN NewBidLevels AS N ON B.Id = N.Id
END
END
Please note that the tigger will consider all records of the table, since it has to update all records for each change on any amount (which is very bad for performance). You should calculate this ranking each time it's needed instead.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments