title: SQL Trigger on Insert to call a Function and Manipulate Values
body: <p>I am having a problem knowing set up a trigger on insert to call a function and passing an identifier <code>SiteID</code> to that function which then operates on variables from another table.</p>
  
  <p>I have the table, where values are inserted:</p>
  
  <pre><code>CREATE TABLE [dbo].[Tests] (
     [Id]     INT       IDENTITY (1, 1) NOT NULL,
     [SiteID] NVARCHAR (128) NOT NULL,
     [Date]   NVARCHAR (MAX) NULL,
     [Time]   NVARCHAR (MAX) NULL,
     [Tub]    NVARCHAR (MAX) NULL,
     [FCl]    FLOAT (53)     NOT NULL
     PRIMARY KEY CLUSTERED ([Id] ASC)
  );
  </code></pre>
  
  <p>Every time a value is inserted in to the <code>Tests</code> table I would like a function to be called which uses the table <code>Review</code> to perform a simple calculation (see the trigger down the post below):</p>
  
  <pre><code>CREATE TABLE [dbo].[Review] (
     [SiteID] NVARCHAR (128) NOT NULL,
     [TubNum] INT NOT NULL,
     [Supplied]  INT  NULL,
     [Performed] INT  NULL,
     [Remaining] INT  NULL 
     PRIMARY KEY CLUSTERED ([SiteID] ASC)
  );
  </code></pre>
  
  <p>This is my <a href="http://sqlfiddle.com/#!3/7141e" rel="nofollow">sqlfiddle</a> so far, however, I am unable to save it with the functions I am working on, which are here:</p>
  
  <p>The trigger - not sure how to pass the SiteID for the insert or call the function:</p>
  
  <pre><code>CREATE TRIGGER [dbo].[TRIG_MyTable]
  ON [dbo].[Tests]
  AFTER INSERT
   AS
     CALL CalcRemaining() //how to pass it SiteID?
  </code></pre>
  
  <p>and the the function itself:</p>
  
  <pre><code>CREATE FUNCTION [dbo].[CalcRemaining](@ID NVARCHAR (128))
  RETURNS NULL
  AS
  BEGIN
     SELECT (Supplied, Performed FROM Review WHERE SiteID = ID);
     Performed = Performed + 1;
     INSERT INTO Review (Performed, Remaining) VALUES (Performed, (Supplied-Performed))
  RETURN;
  END
  </code></pre>
  
  <p>The idea is that the <code>SiteID</code> from the inserted line is passed to the function when called, the function then <code>selects</code> the values <code>Supplied</code> and <code>Performed</code> for that matching <code>SiteID</code> from the <code>Review</code> table. The <code>Performed</code> value is incremented by 1 and that new value along with the retrieved <code>Supplied</code> value are subtracted and written to the <code>Remaining</code> field in the <code>Review</code> table.</p>