title: Issues with SQL Server functions and triggers
body: <p>I have been working on this project by using "advanced" features of SQL Server and creating functions, triggers, but two of these are giving me serious issues.</p>
  
  <p>I am supposed to create a function that includes one input parameter and returns a table. The function itself will return a summary of the individual costs of each insurance expense (base, spouse, etc.), a total of those expenses and the employees name given the number of dependents a given employee has- the user enters the number of dependents as an input parameter.</p>
  
  <pre><code>CREATE FUNCTION fnInsCosts
      (@NoDependants int)
  --returns table datatype
  RETURNS table
  AS 
      --Set the Return to select the columns and aggregated columns from vwPayRoll as listed in exercise 3 of Module 12 --assignment sheet where Dependants is = to the input variable.
      RETURN (SELECT 
                  EmpName, 
                  SUM(BaseCost) AS TotBaseCost, SUM(SpouseIns) AS TotSpouseCost, 
                  SUM(DepIns) AS TotDepCost, SUM(DentalCost) AS TotDentalCost, 
                  SUM(BaseCost * SpouseIns * DepIns * DentalCost) AS TotInsCost
              FROM  
                  vwPayroll
              WHERE 
                  Dependants = @NoDependants
              GROUP BY 
                  Dependants);
  
  SELECT * FROM dbo.fnInsCosts(2);
  SELECT * FROM dbo.fnInsCosts(0);-- Unfinished/error with select and EmpName?
  </code></pre>
  
  <p>Here is the error I get when I try to run the whole thing:</p>
  
  <blockquote>
    <p>Msg 156, Level 15, State 1, Procedure fnInsCosts, Line 15<br>
    Incorrect syntax near the keyword 'SELECT'.</p>
  </blockquote>
  
  <p>And it says this when I run everything except for the part where I invoke it:</p>
  
  <blockquote>
    <p>Msg 8120, Level 16, State 1, Procedure fnInsCosts, Line 10<br>
    Column 'vwPayroll.EmpName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.</p>
  </blockquote>
  
  <p>And here is the last one; I am creating a trigger and I need to create two table copies:</p>
  
  <pre><code>--Test for the existence of a fake table named TempEmpData. If it exists, drop it.
  IF OBJECT ID('TempEmpData') IS NOT NULL
      DROP TABLE TempEmpData;
  
  --Test for the existence of a fake table named TempWork. If it exists, drop it.
  IF OBJECT ID('TempWork') IS NOT NULL
      DROP TABLE TempWork;
  
  --Select everything from EmpData into the appropriate fake table
  SELECT * INTO TempEmpData FROM EmpData
  
  --Select everything from Work into the appropriate fake table
  SELECT * INTO TempWork FROM Work
  GO
  
  CREATE TRIGGER TempEmpDate_INSERT_UPDATE_DELETE
  ON TempEmpData
  AFTER INSERT, UPDATE, DELETE
  AS
      --(USE THIS CONDITIONAL STRUCTURE- substitute variable names where needed and remove the "--")
      IF EXISTS (SELECT * FROM Deleted JOIN TempEmpData ON Deleted.EmpID = TempEmpData.EmpID)
               --the correct primary key)
      BEGIN;
          --Custom error message
          THROW 11, 'EmpID is in use; transaction cancelled!', 1;
          --rollback the transaction
          ROLLBACK TRAN;
      END;
      ELSE
      BEGIN
          --Update the appropriate fake table
          CREATE TRIGGER TempEmpData_INSERT_UPDATE
          ON TempEmpData
          AS 
              --Set the appropriate column to the correct value
              --Where the correct primary key is in a subquery selecting that same key from the
              --system table that handles inserts
              UPDATE TempEmpData 
              SET BenPlanID = 0;
  
              DELETE TempEmpData
              WHERE EmpID = 41;
  
              INSERT TempEmpData
              VALUES ('Bill', 'Smith', '11/14/2014', 0, 0, 1, NULL, 2);
  
              SELECT * 
              FROM TempEmpData
              ORDER BY EmpID DESC
          END;
  </code></pre>
  
  <p>And here are the errors:</p>
  
  <blockquote>
    <p>Msg 4145, Level 15, State 1, Line 4<br>
    An expression of non-boolean type specified in a context where a condition is expected, near 'ID'.</p>
    
    <p>Msg 4145, Level 15, State 1, Line 8<br>
    An expression of non-boolean type specified in a context where a condition is expected, near 'ID'. </p>
    
    <p>Msg 156, Level 15, State 1, Procedure TempEmpDate_INSERT_UPDATE_DELETE, Line 17<br>
    Incorrect syntax near the keyword 'TRIGGER'.</p>
  </blockquote>
  
  <p>I would be extremely grateful for any insight.</p>