none
Update trigger on table with large numbers of rows RRS feed

  • Question

  • I have two tables with a couple hundred thousand rows. I have a statement like this:

    UPDATE table2
    SET colA = t1.colB
    FROM table2 t2 join table1 t1 on t2.key = t1.key

    There is an after update trigger on table2 which checks to see the colA has changed using  UPDATE(colA), and then sets a field table2.ColAUpdateTime = GetDate().

    The problem is that in order to do this in an update trigger, I need to join the INSERTED table with table2. This seems like a huge waste of resources. I think I would rather update the INSERTED table, i.e.

    IF UPDATE(ColA)
       UPDATE INSERTED
       SET ColAUpdateTime = GetDate()

    instead of:

    IF UPDATE(ColA)
       UPDATE table2
       SET ColAUpdateTime = GetDate()
       FROM table2 t2 join INSERTED i on t2.key = i.key

    The problem is that you can't update the INSERTED table.
    Any ideas on how to do this most efficiently?

    Tuesday, March 29, 2011 5:26 PM

All replies

  • Why you are not updating ColAUpdateTime in the main Update statement. You are updateing ColA in your update statement, so Why Not update ColAUpdatTime.

    UPDATE table2
    SET colA = t1.colB, ColAUpdateTime = GetDate()
    FROM table2 t2 join table1 t1 on t2.key = t1.key
    
    
    

    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    Tuesday, March 29, 2011 5:36 PM
  • The only alternative is to do this more efficiently is to use an INSTEAD OF trigger. But this requires you to redo the UPDATE in the trigger, which is not very fun.

    If this Connect request was implemented it would be trivial and you would need to trigger:
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=355461

    Unfortunately, it seems that Microsoft intends to close this as Won't Fix.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Tuesday, March 29, 2011 10:03 PM
  • We do not keep audit data in the table being audited. We use the ANSI/ISO MERGE and not the old proprietary UPDATE..FROM syntax -- it does not find cardinality errors.  The getdate() Sybase dialect is now CURRENT_TIMESTAMP,  too. 

    This is not how to track a history. Let me use a history table for price changes.  The fact is that a price had duration.  This is the nature of time and other continuums.  So a basic history table looks like this in SQL/PSM

    CREATE TABLE PriceHistory
    (upc CHAR(13) NOT NULL   -- industry standard
      REFERENCES Inventory(upc),
     price_prev_date DATE NOT NULL,
     price_start_date DATE DEFAULT CURRENT_DATE NOT NULL,
     price_end_date DATE, -- null means current price
     CHECK(price_start_date < price_end_date),
     CHECK (price_start_date = price_prev_date + INTERVAL 1 DAY), -- prevents gaps
     PRIMARY KEY (upc, price_start_date),
     item_price DECIMAL (12,4) NOT NULL
      CHECK (item_price > 0.0000),
     etc.);

    You use a BETWEEN predicate to get the appropriate price.  You can enforce the "one null per item" with a trigger but techically this should work:

    CHECK (COUNT(*) OVER (PARTITION BY upc)
           = COUNT(price_end_date) OVER (PARTITION BY upc) +1)

    SELECT ..
      FROM PriceHistory AS H, Orders AS O
     WHERE O.sales_date BETWEEN H.price_start_date
               AND COALESCE (price_end_date, CURRENT_DATE);

    It is also a good idea to have a VIEW with the current data:

    CREATE VIEW CurrentPrices (..)
    AS
    SELECT ..
      FROM PriceHistory
     WHERE price_end_date IS NULL;

    Now your only problem is to write a stored procedure that will update the table and insert a new row.  You can do this with a single MERGE statement, or with a short block of SQL/PSM code:

    CREATE PROCEDURE UpdateItemPrice
    (IN in_upc CHAR(13), IN new_item_price DECIMAL (12,4))
    LANGUAGE SQL
    BEGIN ATOMIC
    UPDATE PriceHistory
       SET price_end_date = CURRENT_DATE
     WHERE upc = in_upc;
    INSERT INTO PriceHistory (upc, price_prev_date, price_start_date, price_end_date, item_price)
    VALUES (in_upc, CURRENT_DATE, CURRENT_DATE + INTERVAL '1' DAY, NULL, new_item_price);
    END;

    This will make the price change go into effect tomorrow.

    There is a common kludge to repair a failure to design a history table properly that you can put in a VIEW if you are not able to set things right.  Assume that every day we take a short inventory and put it in a journal. The journal is a clip board paper form that has one line per item per day, perhaps with gaps in the data.  We want to get this into the proper format, namely periods shown with a (start_date, end_date) pair for durations where each item had the same quantity on hand. This is due to Alejandro Mesa 

    CREATE TABLE InventoryJournal
    (journal_date DATETIME NOT NULL,
     item_id CHAR(2) NOT NULL,
      PRIMARY KEY (journal_date, item_id),
     onhand_qty INTEGER NOT NULL);
     
    WITH ItemGroups
    AS
    (SELECT journal_date, item_id, onhand_qty,
            ROW_NUMBER() OVER(ORDER BY item_id, journal_date, onhand_qty)
            - ROW_NUMBER() OVER(PARTITION BY item_id, onhand_qty
                                ORDER BY journal_date) AS item_grp_nbr
       FROM Journal),

    QtyByDateRanges
    AS
    (SELECT MIN(journal_date) AS start_date,
            MAX(journal_date) AS end_date,
            item_id, onhand_qty
       FROM ItemGroups
      GROUP BY item_id, onhand_qty, item_grp_nbr)

    SELECT start_date, end_date, item_id, onhand_qty
      FROM QtyByDateRanges;
     
    This might be easier to see with some data and intermediate steps

    INSERT INTO InventoryJournal
    VALUES('2013-01-01', 'AA', 100),('2013-01-01', 'BB', 200),
          ('2013-01-02', 'AA', 100),('2013-01-02', 'BB', 200),
          ('2013-01-03', 'AA', 100),('2013-01-03', 'BB', 300);

    start_date   end_date     item_id onhand_qty
    ==========================================
    '2013-01-01' '2013-01-03'  'AA'    100
    '2013-01-01' '2013-01-02'  'BB'    200
    '2013-01-03' '2013-01-03'  'BB'    300

    Now, download the Rick Snodgrass book on Temporal Queries in SQL from the University of Arizona website (it is free). 


     

     


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    Tuesday, March 29, 2011 10:35 PM
  • We don't need a full audit, we just want to know when a particular column has been updated.

    Also, this was a question about a general problem, not specifically to auditing.

    It appears that the only way to do this is with an instead of trigger.

    Friday, April 1, 2011 3:01 PM
  • The link you posted does not work. What Connect request are you talking about.
    Friday, April 1, 2011 3:02 PM
  • That's actually what I ended up doing, but I still had the question about how to change the INSERTED table if I needed to. I suppose INSTEAD OF triggers are the way to go.
    Friday, April 1, 2011 3:04 PM
  • > The link you posted does not work. What Connect request are you talking about.

    Connect does not seem to feel well this week. I can find some of my items, but others gives me an error. Right now I cannot even get to the start page for Connect...


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Friday, April 1, 2011 9:54 PM