Update an SQL View when a row is inserted into a table which the view uses

Question

I am working on an SQL View which retrieves data from a View and an SQL table LastTrade and I use a webpage to insert data into the table LastTradeand when I insert a row I want my SQL view updated.

my View is as follows

CREATE VIEW [dbo].[CanadianCrudes]
AS
  SELECT a.Id,
         a.Product,
         a.Grade,
         a.Term,
         a.Pipeline,
         a.Location,
         a.[Index],
         a.bidvolume,
         a.Bidcp,
         a.Bid,
         a.Offer,
         a.offercp,
         a.offervolume,
         a.TermID,
         a.ProductID,
         a.GradeID,
         a.Locked,
         a.Hold,
         a.offercpid,
         a.bidcpid,
         a.idbid,
         a.[sequence],
         CASE
           WHEN ROW_NUMBER()
                  OVER (
                    PARTITION BY a.ProductID, a.GradeID, a.TermID, a.Pipeline, a.[Index]
                    ORDER BY a.Bid DESC, a.Offer) = 1 THEN b.LastTradeValue
           ELSE NULL
         END AS LastTradeValue
  FROM   CanadianCrudesRaw AS a
         LEFT JOIN (SELECT Product,
                           Grade,
                           Term,
                           Pipeline,
                           [Index],
                           MIN(LastTradeValue) LastTradeValue
                    FROM   LastTrades
                    GROUP  BY Product,
                              Grade,
                              Term,
                              Pipeline,
                              [Index]) AS b
                ON b.Product = a.Product
                   AND b.Grade = a.Grade
                   AND b.Term = a.Term
                   AND ISNULL(b.Pipeline, '00') = ISNULL(a.Pipeline, '00')
                   AND ISNULL(b.[Index], '00') = ISNULL(a.[Index], '00')

How do I implement a trigger which refreshes my view to get the inserted data?

No need for a trigger, mate - just SELECT from the view, and it'll pull whatever data is currently in your table. — AHiggins, Dec 03 '14 at 19:33
This question appears to be off-topic because it is about updating a view, which is not actually a thing. — AHiggins, Dec 03 '14 at 19:35

score 0 · Answer 1 · answered Dec 03 '14 at 19:42

0

just do the following query

SELECT * FROM [dbo].[CanadianCrudes]

answered Dec 03 '14 at 19:42

Dudi Konfino

1,126
2
13
24

Update an SQL View when a row is inserted into a table which the view uses

1 Answers1