March 14, 2002 at 4:51 am
OK I'm an ORACLE person getting to love SQL Server. I want to use a trigger that will calculate the sum of two columns on an insert and update another column in the same inserted record with the result. I can do this fine on an update, using the following code
CREATE TRIGGER trg_stock_total ON dbo.stock
FOR UPDATE
AS
DECLARE
@current_stock int
@delivery_qty int
SELECT @current_stock = free_stock,
@delivery_qty = receipt_qty
FROM inserted
UPDATE dbo.stock
SET total_stock = @current_stock +@delivery_qty
FROM inserted
WHERE stock.stock_id = inserted.stock_id
Alas this won't work on FOR INSERT anyone know how to do this. Oh it has to be a trigger 'cos I can't guarantee that this table will always be updated by my application's stored procedures. (rats....)
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
March 14, 2002 at 5:11 am
Just to offer another possible solution. Is this column searched in a large number of queries, if not have you considered using a computed column on the table as opposed to acutally storing the data. Or even have a view with a computed amount as a column output. This IMHO is far easier to manage and you do not have to worry about storing additional data.
The comput column formula would be nothing more than
current_stock + delivery_qty
Or in the view would be
SELECT current_stock, delivery_qty, (current_stock + delivery_qty) as total_stock
FROM dbo.stock
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 14, 2002 at 7:53 am
Can you post the code for the insert trigger you are trying to get working? Is the only diffence the 'For Insert' part of the script?
When you're doing the insert, are multiple records getting inserted at once?
March 14, 2002 at 8:49 am
The inserts we're interested in hit the db singly (not part of a batch input). As to the code well I tried just adding FOR INSERT, UPDATE and leaving the actual SET syntax as you see. I suspected it wouldn't work because on an insert there wouldn't be an existing record to update. Then I tried updating the temporary INSERTED table...well I tried.... with
UPDATE inserted
SET total_stock = @current_stock +@delivery_qty
You'll notice no WHERE clause, by now I could see the hairs that wer going grey. In ORACLE (wash my mouth out) you have a keyword :NEW to prefix each column (e.g. :NEW.total_stock represents the value of the inserted total_stock column) and the trigger would look something like
:NEW.total_stock = :NEW.free_stock + :NEW.receipt_qty
Is there a SQLServer equivilant, no matter how tortuous?
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
March 14, 2002 at 9:09 am
Thank you Antares686. So Simple, So Elegant So.... SQL Server.
The more I work with this product the more I like it. Yes there are things to curse at, but then life has to have some spice in it.
the computed column works fine AND reduces code AND space. You are indeed a Guru
Many thanks
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
March 14, 2002 at 9:52 am
Not really, that is just there statement based on number of posts. Truth is I just discovered that myself a little more than a week ago and I have been at this for well over 2 years in SQL Server. Just goes to show though you can always learn something new.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
December 8, 2002 at 8:45 pm
You can create an "instead of" trigger, do your calculations there and follow it with an insert with your computed values.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply