4 Replies - 941 Views - Last Post: 02 April 2009 - 08:28 AM Rate Topic: -----

#1 sam.adams61   User is offline

  • D.I.C Regular

Reputation: 12
  • View blog
  • Posts: 283
  • Joined: 14-July 08

Using a trigger to retrieve info from a table & copy it to another

Post icon  Posted 01 April 2009 - 07:21 PM

I recently posted a question concerning the use of triggers, and although mikeblas kindly gave answer I'm still struggling with the concept, in particular the syntax of triggers.
As part of a project I'm doing I've been asked to use a trigger to retrieve the price of a product from the products table, and copy it into the Price field of the order's table whenever a new product is added to an order. I am at an utter loss as to how I can do this!! If someone can help me with this I would be very grateful indeed.
In anticipation, many thanks.

Is This A Good Question/Topic? 0
  • +

Replies To: Using a trigger to retrieve info from a table & copy it to another

#2 egof   User is offline

  • D.I.C Head
  • member icon

Reputation: 20
  • View blog
  • Posts: 132
  • Joined: 27-March 09

Re: Using a trigger to retrieve info from a table & copy it to another

Posted 02 April 2009 - 07:04 AM

Are you using MSSQL or another one? I know how in MS but not in other databases.
Was This Post Helpful? 1
  • +
  • -

#3 egof   User is offline

  • D.I.C Head
  • member icon

Reputation: 20
  • View blog
  • Posts: 132
  • Joined: 27-March 09

Re: Using a trigger to retrieve info from a table & copy it to another

Posted 02 April 2009 - 07:57 AM

MSSQL when a new row is added to a table you can access that newly recreated row by calling Inserted, then joining on that to the other table you will only pull back the required data your looking for. Youll need to some testing on it.

Select ?? from Inserted I join TheOtherTable on ?=?


No where clause is needed since only newly created data is in the inserted table.
Was This Post Helpful? 0
  • +
  • -

#4 sam.adams61   User is offline

  • D.I.C Regular

Reputation: 12
  • View blog
  • Posts: 283
  • Joined: 14-July 08

Re: Using a trigger to retrieve info from a table & copy it to another

Posted 02 April 2009 - 08:20 AM

The script for the UPDATE is:

DECLARE @newPrice money
SET @newPrice = 75.00
UPDATE ProductDetails.Products
   SET CostPrice = 75.00
 WHERE ProductCode = 'BE61HPW'


Again, many thanks!! :^:
Was This Post Helpful? 0
  • +
  • -

#5 egof   User is offline

  • D.I.C Head
  • member icon

Reputation: 20
  • View blog
  • Posts: 132
  • Joined: 27-March 09

Re: Using a trigger to retrieve info from a table & copy it to another

Posted 02 April 2009 - 08:28 AM

Try this. Inserted table syntax will only work in triggers.
Update inserted set SET CostPrice = 75.00
from Inserted O join Products P on o.ProductCode=p.productcode


Was This Post Helpful? 1
  • +
  • -

Page 1 of 1