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 |
|
yamol
Starting Member
5 Posts |
 Posted - 2004-05-14 : 14:08:38
|
| I have a history table which records the final selling price of various goods, with a primary key on GoodsID and Date. There is a state column that should be set to 1 if the price increased from the last reported selling price, 0 if it was the same and -1 if it decreased in price.I need to set state column in a trigger for inserted rows, which might be inserted more than one row at a time, meanwhile there is update and delete problem or inserting a row between existing rows.any help would be appreciated. |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-15 : 16:37:37
|
| You don't really need a trigger for this. You need to have three stored procedures for INSERT, UPDATE, and DELETE. In these procedures, you need to have the logic to make sure the state column is maintained correctly. If you do decide to use triggers:1. The insert trigger is going to be fun if you have multiple sales of the same GoodsID at different prices on one insert. You're going to have to look at the MIN(sale) of GoodsID and compare to the last recorded, then compare each one to the one before it until you get to the MAX. Hopefully you won't have this scenario. :) If they are all the same price, it's pretty easy. You just have to compare the MIN(sale) of the inserted to table to the MAX(sale) of your sale table and set all that bit accordingly. The rest would all be 0, right?2. Why would you have update, delete, or insert between existing rows in a history table. Can you post the ddl and explain that a little more?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
|
|
|
|
|
update in a trigger