• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

Insert using trigger after joining a view

Hi,

We would like to have soething like the following trigger.

You can hopefully see what we are trying to achieve so any help in fixing this would be appreciated.  

Thanks.

James.

CREATE TRIGGER Prices_RefreshResults
ON dbo.Prices
FOR INSERT, UPDATE
AS
      /* IF UPDATE (column_name) ...*/
      UPDATE       R
      SET       R.ProfitOrLoss = T.ProfitOrLoss,
            R.ProfitOrLossChange = T.ProfitOrLossChange,
            R.Delta = T.Delta,
            R.Premium = T.CurrentPremium,
            R.Price = T.CurrentPrice,
            R.DeltaUp1 = T.DeltaQtyUp1,
            R.DeltaUp2 = T.DeltaQtyUp2,
            R.DeltaEven = T.DeltaQtyEven,
            R.DeltaDown1 = T.DeltaQtyDown1,
            R.DeltaDown2 = T.DeltaQtyDown2,
            
            R.ProfitOrLossUp1 = T.ProfitOrLossUp1,
            R.ProfitOrLossUp2 = T.ProfitOrLossUp2,
            R.ProfitOrLossEven = T.ProfitOrLossEven,
            R.ProfitOrLossDown1 = T.ProfitOrLossDown1,
            R.ProfitOrLossDown2 = T.ProfitOrLossDown2
            
      FROM Results R
      inner JOIN viewTickets T ON
            T.UserID = inserted.UserID AND
            T.BookName = inserted.BookName AND
            T.Contract = inserted.Contract AND
            T.Currency = inserted.Currency AND
            T.Exchange = inserted.Exchange AND
            T.PricingDate = inserted.PricingDate
0
JAMES
Asked:
JAMES
  • 2
  • 2
1 Solution
 
rafranciscoCommented:
Try this:

CREATE TRIGGER Prices_RefreshResults
ON dbo.Prices
FOR INSERT, UPDATE
AS
     /* IF UPDATE (column_name) ...*/
     UPDATE      R
     SET      R.ProfitOrLoss = T.ProfitOrLoss,
          R.ProfitOrLossChange = T.ProfitOrLossChange,
          R.Delta = T.Delta,
          R.Premium = T.CurrentPremium,
          R.Price = T.CurrentPrice,
          R.DeltaUp1 = T.DeltaQtyUp1,
          R.DeltaUp2 = T.DeltaQtyUp2,
          R.DeltaEven = T.DeltaQtyEven,
          R.DeltaDown1 = T.DeltaQtyDown1,
          R.DeltaDown2 = T.DeltaQtyDown2,
         
          R.ProfitOrLossUp1 = T.ProfitOrLossUp1,
          R.ProfitOrLossUp2 = T.ProfitOrLossUp2,
          R.ProfitOrLossEven = T.ProfitOrLossEven,
          R.ProfitOrLossDown1 = T.ProfitOrLossDown1,
          R.ProfitOrLossDown2 = T.ProfitOrLossDown2
         
     FROM Results R
     inner join inserted I ON
          R.UserID = I.UserID AND
          R.BookName = I.BookName AND
          R.Contract = I.Contract AND
          R.Currency = I.Currency AND
          R.Exchange = I.Exchange AND
          R.PricingDate = I.PricingDate
     inner JOIN viewTickets T ON
          T.UserID = I.UserID AND
          T.BookName = I.BookName AND
          T.Contract = I.Contract AND
          T.Currency = I.Currency AND
          T.Exchange = I.Exchange AND
          T.PricingDate = I.PricingDate
0
 
JAMESAuthor Commented:
I think I need to explain some more...

I want to join the viewTickets on the existing criteria mentioned but then update the Results table on a unique key that will be returned in the view for each ticket.

Both (and only these two) will have the unique column TicketNumber.

Does that help / confuse things!?

Thanks.
0
 
rafranciscoCommented:
How about this:

CREATE TRIGGER Prices_RefreshResults
ON dbo.Prices
FOR INSERT, UPDATE
AS
     /* IF UPDATE (column_name) ...*/
     UPDATE      R
     SET      R.ProfitOrLoss = T.ProfitOrLoss,
          R.ProfitOrLossChange = T.ProfitOrLossChange,
          R.Delta = T.Delta,
          R.Premium = T.CurrentPremium,
          R.Price = T.CurrentPrice,
          R.DeltaUp1 = T.DeltaQtyUp1,
          R.DeltaUp2 = T.DeltaQtyUp2,
          R.DeltaEven = T.DeltaQtyEven,
          R.DeltaDown1 = T.DeltaQtyDown1,
          R.DeltaDown2 = T.DeltaQtyDown2,
         
          R.ProfitOrLossUp1 = T.ProfitOrLossUp1,
          R.ProfitOrLossUp2 = T.ProfitOrLossUp2,
          R.ProfitOrLossEven = T.ProfitOrLossEven,
          R.ProfitOrLossDown1 = T.ProfitOrLossDown1,
          R.ProfitOrLossDown2 = T.ProfitOrLossDown2
         
     FROM Results R
     inner JOIN viewTickets T ON
          R.TicketNumber = T.TicketNumber
     INNER JOIN inserted ON
          T.UserID = inserted.UserID AND
          T.BookName = inserted.BookName AND
          T.Contract = inserted.Contract AND
          T.Currency = inserted.Currency AND
          T.Exchange = inserted.Exchange AND
          T.PricingDate = inserted.PricingDate
0
 
JAMESAuthor Commented:
I think thats perfect!

Thank you very much for your help and quick response....

James.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now