Day 18

Quiz

1:What is wrong with the following triggers'error handler? 
If @@Error <> 0
  Raiserror ('Trigger error: Rolling back', 16, 1)
  Rollback Transaction
  Return

A1: The IF statement is missing a BEGIN...END block. This causes only the RAISERROR statement to be executed if the @@Error value is not equal to zero. Unfortunately, the ROLLBACK TRANSACTION statement will always be executed. This means that the trigger would perform the rollback no matter what the condition of the data is.
2:If inserted rows go into the INSERTED table and deleted rows go into the DELETED table, where do updated rows go?
A2: This is a trick question. An updated row is recorded in both places. The original row is placed in the DELETED table and the new version of the row is placed in the INSERTED table.

Exercise

1:Create a trigger on the Order Detail table in the Northwind database so that whenever an order is entered, modified, or deleted, UnitsInStock is properly updated. However, if an order causes UnitsInStock to drop below zero, raise an error and roll back the transaction.
A1: The following is an example of one way to perform this task:
Create Trigger tr_OrderDetails_StockIn
On [Order Details]
For Insert, Update, Delete As
If @@Rowcount = 0 Return
Select ProductID,
       sum(TotQty)
Into #temp
From (Select ProductID,
       isnull(sum(quantity), 0 ) as TotQty
      From inserted
      Group By productID
      Union
      Select ProductID,
       -isnull(sum(quantity), 0 ) as TotQty
      From deleted
      Group By productID) as prdtable
If @@Error <> 0
Begin
   Raiserror ('Error in Table creation', 16, 1)
   Rollback Transaction
   Return
End
If exists
   (Select *    
   From products as prd Inner Join
        #temp as tp on
        prd.productid = tp.productid and
        prd.UnitsInStock < tp.TotQty)
Begin
   Raiserror ('Not enough units in stock', 16, 1)
   Rollback Transaction
   Return
End
Update Products
Set UnitsInStock = UnitsInStock - TotQty
From #temp as tp
Where products.productId = tp.productid
If @@Error <> 0
Begin
   Raiserror ('Error in Update', 16, 1)
   Rollback Transaction
   Return
End

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset