SQL Trigger on view to insert to table

Question

I'm trying to setup an insert trigger on a view like the one below:

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'  

I'm using the below table as the table where the data is to be inserted:

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]      

And this is the trigger I'm attempting to use to perform an insert:

 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             

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.

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.

I'm fairly new to using triggers so any help would be greatly appreciated.

Answer 1

If I understand you correctly the problem is that your trigger doesn't work if you insert data into ServiceAppointmentViewBase table. It is the correct behaviour. Your trigger is defined on a view so it will be fired only if you insert data through this view. It is not fired if data are inserted directly into a table without using the view PYC_ServiceAppointments. Of course, to insert data through a view it must be updatable or as marc_s pointed the instead of trigger must exist on this view (as in your case).