Voice of the DBA

Tag: syndicated

  • Common SQL Server Mistakes – Multi Row DML Triggers

    How often have you seen someone write a DML trigger like this:

    create trigger mytrigger on Mytable for insert as

    declare @id int
    select @id = id from inserted

    update xx set yyy = zz
    where id = @id

    return

    There seems to be this common misconception that a trigger fires for each change in a row (insert/update/delete), and that’s not true. As noted in Books Online, triggers fire once for the insert/update/delete. Typically this is an implicit transaction for the statement. If you have multiple statements inside an explicit transaction, the trigger fires once for each insert/update/delete statement in the transaction.

    That means that if I change two rows and I have the trigger above, I won’t get the behavior I expect from the trigger. Let’s say that I want to update my inventory table each time I change an order. Imagine that I have this in my orders table:

    orders_1

    and this in my inventory table.

    trigger_2

    If I now write this trigger:

    alter trigger orders_update_inventory on orders
      for update
      as

      declare @qty int
         ,    @product int
       
      select @qty = a.qty – b.qty
        from inserted a
           inner join deleted b
             on a.orderid = b.orderid
      select @product = productid from inserted

      update inventory
       set onhand = onhand – @qty
       where productid = @product

    return

    and execute this:

    update orders
      set qty = 2
       where orderid = ’59CD85CE-984C-4D33-9E23-5F6159848277′

    I will find that my inventory table looks like this:

    trigger_3

    That appears to work, but what happens if we execute this?

    update orders
      set qty = qty+1
      where[CustomerID int] = 2

    Then we find that the inventory is

    trigger_4

    In this case productID 1 has had its inventory reduced by 1, but not product ID 2. Why not?
    When the trigger fires, there are actually this data in the tables:

    Inserted
    OrderID  OrderDate  CustomerID int qty         productid
    ——– ———- ————– ———– ———–
    59CD…    2010-09-22 3              3           1
    01B6…    2010-09-22 2              2           2

    and deleted

    OrderID  OrderDate  CustomerID int qty         productid
    ——– ———- ————– ———– ———–
    59CD85…  2010-09-22 2              2           1
    01B66E…  2010-09-22 2              1           2

    However the trigger, in setting the variables to the result of a query could have picked either of the rows, but only one row. SQL Server doesn’t guarantee order without an ORDER BY, so either product ID could have been chosen. As a result, only one of the products had the inventory updated.

    A proper trigger would look like this

    alter trigger orders_update_inventory on orders
      for update
      as
         
      select @qty = a.qty – b.qty
        from inserted a
           inner join deleted b
             on a.orderid = b.orderid
      select @product = productid from inserted

      update inventory
       set onhand = onhand – ( a.qty – b.qty)
       from inserted a
           inner join deleted b
             on a.orderid = b.orderid
       where inventory.productid = i.productid

    return

    Triggers should always be written to handle multiple rows, using the inserted and deleted tables for joins instead of variables. Even if you always just update single rows, coding this way will prevent issues if there is a multiple row change.

  • Braindumps and Certification

    I saw a post recently that said that braindumps were the best way to prepare for certifications. It was posted from a certification vendor, so take it with a grain of salt, but I think a lot of people think this.

    That somewhat torques me off. I understand that certification can help someone get a better job, and it is good for a career. That’s fine, and I understand that if you are out of a job, or searching for a better job, that the price of the exam seems a little steep, and if you fail, you are out a decent amount of money. An exam costs $125 in the US, and that’s not an insignificant amount of money.

    However.

    We have enough people that don’t know what they’re doing. We have lots of people that struggle in their jobs, and often then don’t know what to do. Their bosses and co-workers aren’t happy. The struggle and get stressed, and they complain about their jobs. Stability is lower, software quality slides along the floor, and it’s a bad fit.

    Not everyone falls into this category, but having taken quite a few exams, I’d say that if you have some knowledge of SQL Server, and you work through a lot of exercises from any certification book, you’ll be fine. It’s hard, but it’s supposed to be. You’re supposed to be competent if you pass the exam.

    Finding ways to pass without being competent, or searching for a guarantee, isn’t good for your career. And it’s certainly not good for mine. Every person that passes who doesn’t really understand what they’re doing makes certifications that much more of a joke, and that much less valuable to employers.

    Which is the point for most people. They get the certification so that employers will be *more* likely to pay them more.

    Do yourself a favor. Study for the exam, learn how to handle the objectives, and then take your chances on the exam.

    There are a number of deals for second takes as well, so be on the lookout for those.

  • Table Variables and Transactions

    I actually had a question of the day submitted on SQLServerCentral about table variables and transactions, but the person didn’t have a reference for it. So I had to go digging around to find one. It doesn’t seem to be documented in BOL, but numerous MVPs and MS employees have posted about this behavior (by design) in places.

    Here’s the code I saw:

    DECLARE @MyTable TABLE 

      ( MyIdentityColumn INT IDENTITY(1,1),
            MyCity NVARCHAR(50))
    INSERT INTO @MyTable (MyCity) VALUES (N'Boston');
    BEGIN TRANSACTION IdentityTest
    INSERT INTO @MyTable (MyCity) VALUES (N'London')
    ROLLBACK TRANSACTION IdentityTest
    INSERT INTO @MyTable (MyCity) VALUES (N'New Delhi');
    SELECT * FROM @MyTable mt

    What do you expect from that? What about this code?

    CREATE TABLE TranTest 
     ( MyIdentityColumn INT IDENTITY(1,1),
           MyCity NVARCHAR(50))
    GO
    INSERT INTO     TranTest (MyCity) VALUES (N'Boston');
    BEGIN TRANSACTION IdentityTest
    INSERT INTO Trantest (MyCity) VALUES (N'London')
    ROLLBACK TRANSACTION IdentityTest
    INSERT INTO TranTest (MyCity) VALUES (N'New Delhi');
    SELECT * FROM TranTest mt

    In the second case, you’d expect two rows, right? Something like this:

    trantest1

    However in the first case you get:

    trantest2

    The insert with “London” isn’t rolled back. This is because the table variable doesn’t participate in transactions. You can see this with this update as well.

    DECLARE @MyTable TABLE (MyIdentityColumn INT IDENTITY(1,1),
         MyCity NVARCHAR(50))
    INSERT INTO @MyTable (MyCity) VALUES (N'Boston');
    BEGIN TRANSACTION IdentityTest
    UPDATE @MyTable SET MyCity = 'Denver'
    ROLLBACK TRANSACTION IdentityTest
    INSERT INTO @MyTable (MyCity) VALUES (N'New Delhi');
    SELECT * FROM @MyTable mt

     

    trantest3

    You might think this is a bug, after all, a transaction is supposed to capture changes and enforce the ACID principles. That is true, but a table variable isn’t a permanent change on the database. It’s a temporary object that exists only in memory, and only for the duration of the batch.

    This means that if you want to persist anything from a table variable, you need to write it to a real table, which will enforce ACID principles.

    Where do you need this? It’s extremely handy for capturing information about potential issues in a transaction, like logging, and returning them outside of the transaction (where/when you can store them in a real table).

  • Roadtrip! SQL Saturday #53 – Kansas City

    kansas_city_mo After speaking this coming weekend in Denver at SQL Saturday #52, I’m heading to SQL Saturday #53 in Kansas City the following weekend, to give my Modern Resume presentation again and meet some SQL pros in another city.

     

    It’s not just me, however, that is making the trip. Chris Shaw (blog | @sqlshaw), Marc Beacom (LinkedIn | @marcbeacom), and Carlos Bossy (LinkedIn | blog | @carlosbossy), all will be speaking with me in Denver (our home) and then making the Roadie to KC.

     

    No, there’s no tour bus (hmmmm, maybe next year), but we will all be heading to KC along with some great out of towners. Arie Jones (blog | @programmersedge), Wendy Pastrick (LinkedIn | blog | @wendy_dance), and Jorge Segarra (blog | @sqlchicken) are coming from out of town. And for a long plane trip west, @TheSQLGuru, Kevin Boles (LinkedIn, @thesqlguru), is flying his plane from Alambama to come and share some great T-SQL tips with the crowd.

    I’m excited for two reasons. One is that I get to meet a whole new group of people in a city that I’ve only driven through, never spent any time in. It’s always great to meet new SQL professionals, and I’m looking forward to Kansas City, and finally shaking hands with a few people that I correspond with on a regular basis.

     

    kansas_city_royals_field-9435 The second is baseball. The speaker’s dinner on Friday night is at the Royal’s game, and I’ve never been to that stadium. My goal is to attend them all one day, and I’ve been to a lot (NYC, San Diego, Seattle, Denver, Baltimore, Arizona, Boston), and this will be one more.

     

    I’m looking forward to the trip, and looking forward to meeting a few people, enjoying some BBQ, and learning about SQL Server.