title: Force deleting a record using stored procedure
body: <p>I've been designing a database for an online store where I have some tables related to products, retailers and orders. I've defined a trigger to prevent deleting an actual product, but instead, marking it as deleted since the product might be already sold and the customer might have made the payment. So if I actually delete the product, since the product id is referenced as foreign key in all other tables, serious problems in respect of losing orders, payment and financial data may happen in the system. Therefore I've done this:</p>
  
  <pre><code>INSTEAD OF DELETE
  AS
      DECLARE @id INT
      SELECT @id = ProductId FROM deleted d
      UPDATE Products SET Deleted = 1 WHERE ProductId = @id
  BEGIN
      SET NoCount ON
  END
  </code></pre>
  
  <p>But, this may increase the size of the table dramatically, and I want to provide the option for the database admin to actually get rid of the data when necessary. Since I've created this trigger, the record can't be physically removed normally. The reason why I did not define a Stored Procedure instead of this is that, this task is quite dangerous in this case  but however, I'm looking for a way to define a specific stored procedure to allow for getting rid of the whole data when necessary. I want to know if there is any way to force the record to be deleted despite having defined such trigger, using a stored procedure. I mean <strong>by Overriding The Trigger</strong> .  </p>