sql trigger question

hi Victor, yes you can do so and create such trigger, let me know you do it your own or you want our help if help then send us list of departments, categories with their percentages. Remeber this is not recommended but we can do such thing anyway at database level.

Regards Akber

"Victor" wrote:

this is a sample of my query

--Heat pump pricing UPDATE Item SET Price = ROUND(Cost / .75, 2), -- 25% margin PriceA = ROUND(Cost / .85, 2), -- 15% margin PriceB = ROUND(Cost / .87, 2), -- 13% margin PriceC = ROUND(Cost / .90, 2) -- 10% margin FROM ITEM I INNER JOIN Category C on I.CategoryID = C.ID WHERE C.Name = 'heat Pumps'

-- Pool pump pricing UPDATE Item SET Price = ROUND(Cost / .55, 2), -- 45% margin PriceA = ROUND(Cost / .65, 2), -- 35% margin PriceB = ROUND(Cost / .75, 2), -- 25% margin PriceC = ROUND(Cost / .90, 2) -- 10% margin FROM ITEM I INNER JOIN Category C on I.CategoryID = C.ID WHERE C.Name = 'Pumps'

I want the trigger to run my query, I need help with the trigger

"Akber Alwani" wrote:

HI VICTOR, here is the sampel code for the trigger, I checked and it works upon updating or inserting the data: CREATE TRIGGER updPrices ON Item FOR INSERT, UPDATE AS IF UPDATE(CategoryID) UPDATE Item SET Price = ROUND(Item.Cost / .75, 2), -- 25% margin PriceA = ROUND(Item.Cost / .85, 2), -- 15% margin PriceB = ROUND(Item.Cost / .87, 2), -- 13% margin PriceC = ROUND(Item.Cost / .90, 2) -- 10% margin FROM Category,Inserted I WHERE I.CategoryID = Category.ID AND Category.Name = 'Airplane' AND I.ID=Item.ID GO

Please rate I appreicate you.

"Victor" wrote:

thank you, is there a way to leave the sql statement in the database to run automatically, if so how?

victor

"Akber Alwani" wrote:

that's what a trigger does. If you don't understand that, you shouldn't do this. Also, this implementation is not good for a trigger; you want to get the margins from a table rather than hard coding them. This is going to be hard to maintain and has the potential to affect performance. Every time an item is sold the item table gets updated and your trigger will fire. Even if it immediately exits because the CategoryID was not updated, it will be called.

It's much safer to use the item wizard for this type of update, although I don't remember if it allows you to update price based on cost or not...

Glenn Adams Tiber Creek C> thank you, is there a way to leave the sql statement in the database to run

hi Gleen, you are right and also for your information in the trigger below it won't run for every column get updated however the of categoryID get change. I think it is best idea to have sepearte table for category id and discounts and may be develop small process , job to run randomly or may be trigger to do such job for the updates to item.

"Glenn Adams [MVP - Retail Mgmt]" wrote:

The trigger will be called on every update, it just won't process any statements within the IF UPDATE(FieldName); granted, that is all statements in the example, but would not be in the final version.

My point is that triggers can have consequences and should only be applied by someone who is very familiar with SQL and even then only after testing.

Glenn Adams Tiber Creek C> hi Gleen, you are right and also for your information in the trigger below it