title: SQL Trigger on view to insert to table
body: <p>I'm trying to setup an insert trigger on a view like the one below:</p>
  
  <pre><code>CREATE view [dbo].[PYC_ServiceAppointments] AS   
     SELECT 
        e.epUR as 'ClientUR',  
        codeDescription as 'Category',  
        s.serName as 'Service',  
        a.OccurrenceDate as 'StartDate',   
        a.EndDate as 'EndDate',   
        a.StartTime as 'StartTime',   
        a.EndTime as 'EndTime',   
        a.Confirmed as 'ServiceStatus', 
        a.Note as 'Note'   
     FROM  
        [ServiceAppointmentViewBase] a  
     INNER JOIN 
        Episode e ON e.cid = a.ClientID AND e.epRecent = '1' 
     INNER JOIN 
        [Service] s ON s.serID = a.ServiceID 
     LEFT JOIN 
        Codes c ON s.sCatCode = c.codecode AND c.codetype = 'SVC'  
  </code></pre>
  
  <p>I'm using the below table as the table where the data is to be inserted:</p>
  
  <pre><code>CREATE TABLE [dbo].[ServiceAppointments]
  (     
      [SID] [int] IDENTITY(1,1) NOT NULL,    
      [ClientUR] [varchar](50) NULL,      
      [Category] [varchar](150) NULL,         
      [Service] [varchar](60) NULL,     
      [StartDate] [datetime] NULL,      
      [EndDate] [datetime] NULL,       
      [StartTime] [datetime] NULL,     
      [EndTime] [datetime] NULL,  
      [ServiceStatus] [bit] NULL,     
      [Note] [text] NULL, 
  
      PRIMARY KEY CLUSTERED ([SID] ASC)    
        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,     
              ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 
  ) ON [PRIMARY] 
  TEXTIMAGE_ON [PRIMARY]      
  </code></pre>
  
  <p>And this is the trigger I'm attempting to use to perform an insert:</p>
  
  <pre><code> CREATE TRIGGER [dbo].[Trigger_Service]
   ON [PYC].[dbo].[PYC_ServiceAppointments]
   INSTEAD OF INSERT
   AS 
   BEGIN
      SET NOCOUNT ON;
      SET ROWCOUNT 0;
  
      INSERT INTO PYC.dbo.ServiceAppointments 
                 ( 
                  ClientUR,
                  Category,
                  [Service],
                  StartDate,
                  EndDate,-- = ISNULL(Getdate(), CurrentValues.EndDate)),
                  StartTime,
                  EndTime,
                  ServiceStatus,
                  Note )
          SELECT  
             b.ClientUR, b.Category, b.[service], b.StartDate, b.EndDate, 
             b.StartTime, b.EndTime, b.ServiceStatus, b.Note
          FROM    
             Inserted b   
  END             
  </code></pre>
  
  <p>The database that stores the data doesn't have a unique ID against the table, so what I'm trying to accomplish is when an entry is added into the database and the requirements are met in the view so it is populated with this entry that it will fire the trigger and add this row to the table with the SID (PK) assigning an id for that row.</p>
  
  <p>I've tried the same trigger on a test table that contains the same columns as the designated table and when entry is added to the table the trigger fires and adds the row to the other table.</p>
  
  <p>I'm fairly new to using triggers so any help would be greatly appreciated.</p>