title: 'Converting' PLSQL Trigger to StoredProcedure SQL Server 2008
body: <p>My database has tables Item, Ingredients, ItemContains, BillItem. Item is 'made' of one or more Ingredients, and that is presented in table ItemContains (IDItem - IDIngredient - ContainingQuantity). When I insert record into BillItem, which also has Quantity (BillItemQuantity) field, I want Trigger or StoredProcedure to reduce value of Quantity field in Ingredient table</p>
  
  <p><strong>(IngredientQuantity = IngredientQuantity - BillItemQuantity*ContainingQuantity)</strong>, </p>
  
  <p>but the problem is when Item contains more than one Ingredient, don't know where to put IDIngredient, I tried with temporary table, but unsuccessfully. I wrote PL/SQL trigger for Oracle Database, and it works, so if someone can help with 'converting' that into SQL Server procedure or something like that, I would highly appreciate that. Thanks.</p>
  
  <pre><code>create or replace 
  trigger ReduceQuantity
  before insert on BillItem
  for each row
  declare
    quaN BillItem.Quantity%type;
    quaO BillItem.Quantity%type;
    idIt integer;
    idItOld integer;
    type RecCon is record (IngID ItemContains.idIngredient%type, Qua ItemContains.quantity%type);
    type TableQuantity is table of RecCon index by binary_integer;
    i binary_integer:=0;
  
    TabQuant TableQuantity;
  
  begin
    idIt:= :New.IDItem;
    idItOld:= :Old.IDItem;
    quaN := :New.Quantity;
    kolS := :Old.Quantity;
  
    for rec in (select IDIngredient, Quantity from ItemContains where IDItem = idIt) loop
      TabQuant(i).IngID := rec.IDIngredient;
      TabKol(i).Qua := rec.Quantity;
      i:=i+1;
    end loop;
  
    if inserting then 
      i:=TabQuant.first;
      while i&lt;=TabQuant.last loop
              update Ingredient set Quantity=Quantity-TabQuant(i).Qua*quaN where IDIngredient = TabQuant(i).IngID;
             i:=TabKol.Next(i);
      end loop; 
  
    end if;
  end ReduceQuantity;
  </code></pre>
  
  <p>Here is the entire script that you can run in SQL Fiddle. It's shortened, but it's fully functional for this problem. So I want trigger to reduce quantity in Ingredient table on inserting new BillItem (example: 2 Cappuccinos in BillItem will reduces 'Espresso coffee' for 2 pieces and 'Milk' for 0.10 l)</p>
  
  <pre><code>create table Item(
      IDItem integer NOT NULL,
      Name varchar(30) NOT NULL,
      ItemType varchar(9),
      Unit varchar(5) NOT NULL,
      Price decimal(5,2),
      CONSTRAINT item_PK PRIMARY KEY (IDItem),
      CONSTRAINT item_UQ UNIQUE (Name),
      CONSTRAINT item_unit_CHK CHECK (Unit in ('g', 'kg', 'ml', 'dl', 'l', 'pc')),
      CONSTRAINT item_type_CHK CHECK (ItemType in ('Food', 'Beverage'))
      );
  
  create table Ingredient(
      IDIngredient integer NOT NULL,
      Name varchar(30) NOT NULL,
      Type varchar(9),
      Unit varchar(5) NOT NULL,
      IngredientQuantity decimal(6,2),
      CONSTRAINT ingredient_PK PRIMARY KEY (IDIngredient),
      CONSTRAINT ingredient_UQ UNIQUE (Name),
      CONSTRAINT ingredient_unit_CHK CHECK (Unit in ('g', 'kg', 'ml', 'dl', 'l', 'pc')),
      CONSTRAINT ingredient_type_CHK CHECK (TipSastojka in ('Food', 'Beverage')),
      CONSTRAINT ingredient_UQ1 CHECK(IngredientQuantity&gt;=0)
  );
  
  create table ItemContains(
      IDItem integer NOT NULL,
      IDIngredient integer NOT NULL,
      ContainsQuantity decimal(4,2) NOT NULL,
      CONSTRAINT ItemContains_PK PRIMARY KEY (IDItem, IDIngredient),
      CONSTRAINT ItemContains_FK1 FOREIGN KEY (IDItem) references Item(IDItem),
      CONSTRAINT ItemContains_FK2 FOREIGN KEY (IDIngredient) references Ingredient(IDIngredient)
      );
  
  create table Bill(
      IDBill integer NOT NULL,
      BillDate varchar(12) NOT NULL,
      TotalPrice decimal(8,2),
      CONSTRAINT bill_PK PRIMARY KEY (IDBill)
      );
  
  
  create table BillItem(
      IDBillItem integer NOT NULL,
      IDBill integer NOT NULL,
      IDItem integer NOT NULL,
      BillItemQuantity decimal(3,1),
      CONSTRAINT billitem_PK PRIMARY KEY (IDBillItem),
      CONSTRAINT billitem_FK1 FOREIGN KEY (IDItem) references Item(IDItem),
      CONSTRAINT billitem_FK2 FOREIGN KEY (IDBill) references bill(IDBill)
  );
  
  
  Insert into Ingredient values(1, 'Amstel draft', 'Beverage', 'l', 100);
  Insert into Ingredient values(2, 'Espresso coffee', 'Beverage', 'pc', 275);
  Insert into Ingredient values(3, 'Milk', 'Beverage', 'l', 90);
  
  Insert into Item values(1, 'Amstel - small', 'Beverage', 'l', 5);
  Insert into Item values(2, 'Amstel - large', 'Beverage', 'l', 7);
  Insert into Item values(3, 'Espresso', 'Beverage', 'pc', 4.5);
  Insert into Item values(4, 'Cappuccino', 'Beverage', 'pc', 5.5);
  
  Insert into ItemContains(IDItem, IDIngredient,ContainsQuantity) values(1, 1, 0.3);
  Insert into ItemContains(IDItem, IDIngredient,ContainsQuantity) values(2, 1, 0.5);
  Insert into ItemContains(IDItem, IDIngredient,ContainsQuantity) values(3, 2, 1);
  Insert into ItemContains(IDItem, IDIngredient,ContainsQuantity) values(4, 2, 1);
  Insert into ItemContains(IDItem, IDIngredient,ContainsQuantity) values(4, 3, 0.05);
  
  insert into Bill values(1, '12-jun-2013', null);
  
  Insert into BillItem(IDBillItem, IDBill, IDItem, BillItemQuantity) values(1, 1, 1, 3);
  Insert into BillItem(IDBillItem, IDBill, IDItem, BillItemQuantity) values(2, 1, 2, 1);
  Insert into BillItem(IDBillItem, IDBill, IDItem, BillItemQuantity) values(3, 1, 3, 1);
  Insert into BillItem(IDBillItem, IDBill, IDItem, BillItemQuantity) values(4, 1, 4, 2);
  
  
  CREATE VIEW ItemsIngredientsView  AS 
    select sa.IDItem as "ID Item",  art.Name as "Item name", sa.ContainsQuantity as "Quantity", s.Unit as "Unit", s.IDIngredient as "ID Ingredient", s.Name as "Ingredient name" from ItemContains sa left join Ingredient s on sa.IDIngredient=s.IDIngredient left join Item art on art.IDItem = sa.IDItem;
  
  CREATE VIEW BillView AS 
    select r.IDBill as "Bill", sr.IDItem as "ID Item", art.Name as "Item Name", sr.BillItemQuantity as "Quantity", art.Price as "Price" from Bill r right join BillItem sr on r.IDBill=sr.IDBill left join Item art on sr.IDItem = art.IDItem;
  
  CREATE VIEW BillTotalPriceView AS 
    select sum(sr.BillItemQuantity*art.Price) as "TotalPrice" from Bill r right join BillItem sr on r.IDBill=sr.IDBill left join Item art on sr.IDItem = art.IDItem;
  </code></pre>