title: SQL using a function in a trigger
body: <p>I am creating a a trigger in SQL that will insert into another table after Insert on it. However I need to fetch a Value from the table to increment to be used in the insert.</p>
  
  <p>I have a AirVisionSiteLog table. On insert on the table I would like for it to insert into another SiteLog table. However in order to do this I need to fetch the last Entry Number of the Site from the SiteLog table. Then on its insert take that result and increase by one for the new Entry Number. I am new to Triggers and Functions so I am not sure how to use them correctly. I believe I have a function to retrieve and increment the Entry Number however I am not sure how to use it in the Trigger. </p>
  
  <p>My Function - </p>
  
  <pre><code>CREATE FUNCTION AQB_RMS.F_GetLogEntryNumber
  
  (@LocationID int)
  
  RETURNS INTEGER
  
  AS 
  BEGIN
      DECLARE
          @MaxEntry Integer,
          @EntryNumber Integer
  
   Set @MaxEntry = (Select Max(SL.EntryNumber) FROM AQB_MON.AQB_RMS.SiteLog SL
                  WHERE SL.LocationID = @LocationID)
  
   SET @EntryNumber = @MaxEntry + 1
  
   RETURN @EntryNumber
  
   END 
  </code></pre>
  
  <p>My Trigger and attempt to use the Function - </p>
  
  <pre><code> CREATE TRIGGER [AQB_RMS].[SiteLogCreate] on [AQB_MON].[AQB_RMS].[AirVisionSiteLog]
   AFTER INSERT
   AS
   BEGIN
  
  declare @entrynumber int
  declare @corrected int
  
  set @corrected = 0
  
  INSERT INTO [AQB_MON].[AQB_RMS].[SiteLog]
          ([SiteLogTypeID],[LocationID],[EntryNumber],[SiteLogEntry]
         ,[EntryDate],[Corrected],[DATE_CREATED],[CREATED_BY])
  
  SELECT st.SiteLogTypeID, l.LocationID, 
   (select AQB_RMS.F_GetLogEntryNumber from [AQB_MON].[AQB_RMS].[SiteLog] sl 
   where sl.LocationID = l.LocationID)
   , i.SiteLogEntry, i.EntryDate, @corrected, i.DATE_CREATED, i.CREATED_BY
  
  from inserted i
  left join AQB_MON.[AQB_RMS].[SiteLogType] st on st.SiteLogType = i.SiteLogType
  left join AQB_MON.AQB_RMS.Location l on l.SourceSiteID = i.SourceSiteID
  
   END 
  
   GO
  </code></pre>