Workshop

The Workshop provides quiz questions to help you solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you’ve learned. Try to understand the quiz and exercise answers before continuing on to the next day’s lesson. Answers are provided in the appendix.

Quiz

1.  If inserted rows go in the inserted table and deleted rows go in the deleted table, where do updated rows go?

2.  What’s wrong with this trigger error handler?

if @@error <> 0
begin
      rollback  tran
    return
end

3.  What’s wrong with this one?

if @@error  <>  0
raiserror  (“trigger  error:  rolling  back”,  16,  1)
rollback   tran
return

Exercise

Run the following script, creating two tables: items and orders.

create    table    items
(item_id     int     identity,
    description   varchar(25)   not   null,
   price   money   not   null,
    qty_in_stock  int  default  0  not  null,
    reorder_level  int  default  0  not  null
)
go
create  table  orders
(ord_id   int   identity,
  item_id  int not   null,
  qty  int not null
)
go
insert   items   (description,   price,   qty_in_stock)
values   (“boots”,   $45,   10)
insert   items   (description,   price,   qty_in_stock)
values   (“hats”,    $12,   75)
insert   items   (description,   price,   qty_in_stock)
values   (“scarves”, $15,   23)
go

Write a trigger so that, every time an order is entered, modified, or deleted, the quantity in stock is properly updated. If an order drops the stock below zero, raise an error and roll back.