Sql Trigger On Insert To Call A Function And Manipulate Values

I am having a problem knowing set up a trigger on insert to call a function and passing an identifier SiteID to that function which then operates on variables from another table. I

Solution 1:

If the assumptions I've asked about in the comments are valid (that Remaining and Performed can always be calculated), here's how I'd implement your database structure, with no trigger nor function:

Base tables:

CREATETABLE dbo.Tests (
   [Id]     INTIDENTITY (1, 1) NOTNULL,
   [SiteID] NVARCHAR (128) NOTNULL,
   [Date]   NVARCHAR (MAX) NULL,
   [Time]   NVARCHAR (MAX) NULL,
   [Tub]    NVARCHAR (MAX) NULL,
   [FCl]    FLOAT (53)     NOTNULLPRIMARY KEY CLUSTERED ([Id] ASC)
);
GO
CREATETABLE dbo._Review (
   [SiteID] NVARCHAR (128) NOTNULL,
   [TubNum] INTNOTNULL,
   [Supplied]  INTNULL,
   PRIMARY KEY CLUSTERED ([SiteID] ASC)
);

Then a view that calculates Performed:

CREATEVIEW dbo._Tests_Count
WITH SCHEMABINDING
ASSELECT
        SiteID,
        COUNT_BIG(*) as Performed
    FROM
        dbo.Tests t
    GROUPBY SiteID
GO
CREATEUNIQUE CLUSTERED INDEX IX_Tests_Count ON dbo._Tests_Count (SiteID)

And finally a view that re-creates the original Review table:

CREATEVIEW dbo.Review
WITH SCHEMABINDING
ASSELECT
        r.SiteID,
        r.TubNum,
        r.Supplied,
        COALESCE(tc.Performed,0) as Performed,
        r.Supplied -COALESCE(tc.Performed,0) as Remaining
    FROM
        dbo._Review r
            leftjoin
        dbo._Tests_Count tc WITH (NOEXPAND)
            on
                r.SiteID = tc.SiteID
GO

If needed, at this point a trigger could be created on this Review view to allow any INSERTs, DELETEs and UPDATEs to be performed against it rather than _Review, if you cannot change some calling code.

Solution 2:

You have to be completely sure you're manipulating ONLY one row at a time!

CREATETRIGGER [dbo].[TRIG_MyTable]
ON [dbo].[Tests]
AFTER INSERTASDECLARE@SiteID NVARCHAR (128)
SELECT@SiteID= ID FROM inserted
   CALL CalcRemaining(@SiteID)