Display Results Before Delete SQL Server

Question

I'm so close to getting the answer to this, but I feel like I'm missing the final part.

I've created a DELETE trigger that should display values from the SELECT statement before they are deleted from the table. However, when the trigger is called I get a blank result, but the values (order_id=10001 AND product_id=25) are still deleted from the table.

I've verified the SELECT statement works before I've run the trigger, so I'm confident that part is correct.

I've tried using an INSTEAD OF DELETE trigger, but the values end up not being deleted. I don't believe there is a BEFORE DELETE function for SQL Server? Is there a work around?

Suggestions?

CREATE TRIGGER deleteOrderTrigger
ON order_details
FOR DELETE
AS
    SELECT order_details.product_id, products.name, 
           order_details.quantity AS 'Quantity being deleted from order', 
           SUM(products.quantity_in_stock) + order_details.quantity AS 'In Stock Quantity after Deletion'
    FROM order_details
    LEFT JOIN products ON products.product_id = order_details.product_id
    WHERE order_details.order_id = 10001 AND products.product_id = 25
    GROUP BY order_details.product_id, products.name, order_details.quantity

GO

-- Below is the code that will fire the trigger
DELETE order_details
WHERE order_id = 10001 AND product_id = 25

This does not really make sense. I suggest you do not have a select or delete in your trigger. Depending on your exact intent you can do something like this - Create a SP that runs a delete, your trigger can pop items into a temp table or staging area and then your SP interrogates that data after the delete statement. — Joe C, Nov 24 '17 at 17:30
Should I create a temporary table, or should I use the virtual INSERTED table? — Chef1075, Nov 24 '17 at 17:32

score 1 · Accepted Answer · answered Nov 24 '17 at 17:45

@JoeC - using a proc is probably the best answer.

If you must use a trigger then remmeber that it must be coded to support sets. If someone executes delete order_details where order_id = 10001 then your trigger will need to return the stock level for every product on the order.

Also, when coding a trigger, you have access to a built in table named deleted. This table contains the records deleted.

So you can do something like this:

CREATE TRIGGER deleteOrderTrigger
ON order_details
FOR DELETE
AS

INSERT INTO deleted_order_products_log

SELECT order_details.product_id
      ,products.name
      ,[Quantity being deleted from order] = order_details.quantity
      ,[In Stock Quantity after Deletion] = SUM(products.quantity_in_stock) + order_details.quantity
  FROM order_details
    INNER JOIN deleted d
        ON order_details.primaryKey = d.primaryKey
      LEFT JOIN products
          ON products.product_id = order_details.product_id
 GROUP BY order_details.product_id
         ,products.name
         ,order_details.quantity;

You can then query the log file to get the results of the calculation.

I'm going to take a look at both yours and @Joe C's answers, to see what works. Thanks! — Chef1075, Nov 24 '17 at 20:41
So I think this is the most correct answer because it uses the `deleted` table, which is what I used in my final code. Thank you to you and @Joe C for helping me :) — Chef1075, Nov 24 '17 at 22:15

benjamin moskovits · Answer 2 · 2017-11-24T17:43:17.790

0

Put the trigger results into an audit table and you will see the results of the trigger. I have never seen a trigger with a where clause similar to yours. Do a insert into table x select.... You also want to use the deleted table(there is an inserted table) that is created just for each occurrence of the trigger that will contain any/all of the rows that were just deleted. The delete occurs in the main body of the code. The trigger is then invoked and the deleted table (you can do a delete * from deleted only in the trigger) that will contain the row(s) that were deleted.

edited Nov 24 '17 at 17:43

answered Nov 24 '17 at 17:29

benjamin moskovits

5,261
1
12
22

Great, thanks. I'll look up what audit tables are and see if I can get the result. – Chef1075 Nov 24 '17 at 17:30
No i meant an audit table is a regular table that you create to store results of the trigger. There is a concept of SQL Server Audit but that is not what I am referring to. – benjamin moskovits Nov 24 '17 at 17:39

score 0 · Answer 3 · answered Nov 24 '17 at 17:38

I haven't used triggers a lot, but it seems you have an AFTER trigger, which is not able to read the deleted rows, that's why you get blank result.

The thing with triggers are the inserted and deleted tables, maybe this would help: https://learn.microsoft.com/en-us/sql/relational-databases/triggers/use-the-inserted-and-deleted-tables

You need a INSTEAD OF trigger as you mentioned, but you have to do the delete operation yourself. More on this subject here: https://learn.microsoft.com/en-us/sql/relational-databases/triggers/dml-triggers and one example in here: https://stackoverflow.com/a/3267726/5605866

Display Results Before Delete SQL Server

3 Answers3