How to debug an insert/update trigger to see values inserted/updated

Question

I have this trigger that I'm trying to debug. I need to know the values that it's trying to insert. The below Print @Item is not working.

DECLARE @Item VARCHAR(MAX);



  INSERT INTO [BPRIL DATA ENTRY]
              ([COMPANY],
               [CUSTOMER],
               [ITEM #],
               [VENDOR],
               [SEASON],
               [BPRILP0CaseCost],
               [CustomerItem#])
  SELECT DISTINCT oh.company,
         oh.customer,
         @Item = ins.itemnumber,
         ins.vendor,
         oh.season,
         ins.VendorCost,
         oid.[CustomerSKU]
  FROM   [ORDER HEADER] AS oh
         LEFT JOIN [Order Item Detail] AS OID
           ON oh.[ORDER #] = OID.[ORDER #]
         LEFT JOIN [INSERTED] AS ins
           ON ins.[ITEM #] = OID.[ITEM #]
         LEFT JOIN [BPRIL DATA ENTRY] bp
           ON bp.[COMPANY] = oh.company
              AND bp.[CUSTOMER] = oh.customer
              AND bp.[ITEM #] = ins.itemnumber
              AND bp.[VENDOR] = ins.vendor
              AND bp.[SEASON] = oh.season
  WHERE  bp.[COMPANY] IS NULL
         AND bp.[CUSTOMER] IS NULL
         AND bp.[ITEM #] IS NULL
         AND bp.[VENDOR] IS NULL
         AND bp.[SEASON] IS NULL
         AND (OID.[STATUS] = 'C'
         OR OID.[STATUS] = 'I')
         AND ins.[VENDOR] IS NOT NULL
         AND ins.[QUOTE #] IS NOT NULL
         AND ins.[VENDORCOST] IS NOT NULL 

Print @Item;

Solution 1

I created a table:

CREATE TABLE DebugTrace
(
   [COMPANY] NVARCHAR(2),
   [CUSTOMER] NVARCHAR(255),
   [ITEM #] NVARCHAR(35),
   [VENDOR] NVARCHAR(50),
   [SEASON] INT,
   [BPRILP0CaseCost] MONEY,
   [CustomerItem#] NVARCHAR(50)
)

Then I ran the Trigger to Insert into DebugTrace instead of [BPRIL DATA ENTRY]

Solution 2

@cadrell0 is correct, you should not set something to a variable in an insert statement. Nor should you ever be setting anything to a scalar variable in a trigger. Inserted and deleted tables may have more than one row and thus more than one value. You need to think in terms of sets for triggers.

However, here is my process for developing a trigger and seeing what is going on.

First, I figure out the code outside of the trigger (On dev this may mean dropping the existing trigger while you work things out for a major change).

So I set up and populate the inserted and or deleted tables as temp tables in a script (Iscript out the actual table and change the name to make this easier). I make sure to populate them with multiple records (very important!) and with data that will satisfy my test cases.

Then I write the code (minus the create trigger part) and use the #inserted and/or #deleted temp tables anywhere that I would use inserted or deleted. Now I can run one part at a time and see my results along the way if need be. I can also put everything in a transaction and roll it back at the end so that I can continue to use the same test data repeatedly until I get it right.

Once I know my code does what I want it to do, I remove the transactions, the temp table code and any test code and globally replace the # sign with nothing (so the trigger-only tables are now called inserted and/or deleted). Then I add in the create trigger code and create the trigger.

Solution 3

You can't set a variable and insert into a table at the same time. You will need to do this in two separate queries.