April 25, 2016 at 7:32 pm
Hi,
I have a table with the following fields.
productId,LatestPrice,PriceChangeSequenceId
for each new product enterted into the table a row will be inserted into this table with PriceChangeSquenceID of 1.
If the price is changed after that it will have PriceChangeSequnceId 2,3,4,5 and so on.
I need to create a trigger that will be fired when there is a new product entered into the system with PriceChangeSequenceId=1
I need to use a function and calculate some discount and update this discount value on another table.
How can I achieve this?
Thanks in advance.
April 25, 2016 at 8:12 pm
See Books Online for information on creating an INSERT trigger. I strongly recommend you do NOT use a Scalar UDF to calculate whatever you need. Try your best to do it with an Inline Table Valued Function or some other set-based mechanism.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 27, 2016 at 8:37 am
sql_2005_fan (4/25/2016)
I need to create a trigger that will be fired when there is a new product entered into the system with PriceChangeSequenceId=1I need to use a function and calculate some discount and update this discount value on another table.
Forget about the function. Just code the logic directly in the trigger.
For the trigger, remember that it fires once per statement even if multiple rows are affected. So you are looking for a query that uses the inserted pseudo-table to find new rows, filter them on PriceChangeSequenceId=1 to find the rows that need the calculation done, then join that to the base table to store the result.
April 27, 2016 at 9:11 am
Is this function used elsewhere? In non-trigger code? If so, then I'd follow Kevin's advice. If not, I'd follow Hugo.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply