Instead of Update Trigger on View

  • Hello,

    I am working on a project that requires that I be able to make changes to a view as though it were a table, and I have concluded that what I need is an INSTEAD OF UPDATE trigger, but my attempts so far to create one have failed. I wanted to see if anyone out there could help me put one together.

    Here is the structure of my View:

    SELECT dbo.Addresses.AddressLine1,

    dbo.Addresses.AddressLine2,

    dbo.Addresses.City,

    dbo.Addresses.STProvCode,

    dbo.Addresses.CountRegCode,

    dbo.Addresses.PostalCode,

    dbo.Customer_Addresses.CustomerID AS Customer_Address_CustomerID,

    dbo.Customer_Addresses.AddressID,

    dbo.Customer_Addresses.DateAddressFrom,

    dbo.Customer_Addresses.AddressTypeCode,

    dbo.Ref_Address_Type.AddressTypeCode AS Ref_Address_Type_AddressTypeCode,

    dbo.Ref_Address_Type.AddressTypeDescription,

    dbo.Customers.CustomerID,

    dbo.Customers.FirstName,

    dbo.Customers.MiddleName,

    dbo.Customers.LastName,

    dbo.Customers.SuffixCode,

    dbo.Customers.DateBecameCustomer,

    dbo.Ref_Suffix.SuffixCode AS Ref_Suffix_SuffixCode,

    dbo.Ref_Suffix.SuffixDescription,

    dbo.Ref_STProv.STProvCode AS Ref_STProv_STProvCode,

    dbo.Ref_STProv.STProvDescription,

    dbo.Ref_CountryRegion.CountRegCode AS Ref_CountryRegion_CountRegCode,

    dbo.Ref_CountryRegion.CountRegDescription,

    dbo.Addresses.AddressID AS Addresses_AddressID,

    dbo.Customers.ProjID,

    dbo.Ref_Projects.ProjID AS Ref_Projects_ProjID,

    dbo.Ref_Projects.ProjName,

    dbo.Ref_Projects.ProjLabel,

    dbo.Customers.StatusCode,

    dbo.Customers.AcctStatusCode AS Customers_AcctStatusCode,

    dbo.Customers.Email,

    dbo.Customers.Email2,

    dbo.Customers.Email3,

    dbo.Customers.Phone,

    dbo.Customers.Phone2,

    dbo.Customers.Phone3,

    dbo.Customers.Fax,

    dbo.Customers.EquipWarrantyBeginDate,

    dbo.Addresses.CustomerID AS Addresses_CustomerID,

    dbo.Addresses.LocID,

    dbo.Addresses.HouseType,

    dbo.Addresses.AreaSubdivision,

    dbo.Addresses.OccupancyDate,

    dbo.Addresses.ISWCompDate,

    dbo.Addresses.HomeWarrantyBeginDate,

    dbo.Addresses.NumBedrooms,

    dbo.Addresses.ForeignInsideWireInstalled,

    dbo.Addresses.ForeignSecurityInstalled,

    dbo.Addresses.ForeignCanInstalled,

    dbo.Customers.Notes,

    dbo.Ref_Contact_Status.StatusCode AS Ref_Contact_Status_StatusCode,

    dbo.Ref_Contact_Status.StatusDescription,

    dbo.Ref_Account_Status.AcctStatusCode,

    dbo.Ref_Account_Status.AcctStatusDescription

    FROM dbo.Addresses

    INNER JOIN dbo.Customer_Addresses ON dbo.Addresses.AddressID = dbo.Customer_Addresses.AddressID

    INNER JOIN dbo.Customers ON dbo.Customer_Addresses.CustomerID = dbo.Customers.CustomerID

    INNER JOIN dbo.Ref_Address_Type ON dbo.Customer_Addresses.AddressTypeCode = dbo.Ref_Address_Type.AddressTypeCode

    INNER JOIN dbo.Ref_CountryRegion ON dbo.Addresses.CountRegCode = dbo.Ref_CountryRegion.CountRegCode

    INNER JOIN dbo.Ref_STProv ON dbo.Addresses.STProvCode = dbo.Ref_STProv.STProvCode

    INNER JOIN dbo.Ref_Suffix ON dbo.Customers.SuffixCode = dbo.Ref_Suffix.SuffixCode

    INNER JOIN dbo.Ref_Projects ON dbo.Customers.ProjID = dbo.Ref_Projects.ProjID

    INNER JOIN dbo.Ref_Account_Status ON dbo.Customers.AcctStatusCode = dbo.Ref_Account_Status.AcctStatusCode

    INNER JOIN dbo.Ref_Contact_Status ON dbo.Customers.StatusCode = dbo.Ref_Contact_Status.StatusCode

    I was able to put together an INSTEAD OF INSERT trigger to enable me to insert "records" into the view, but I have not been able to create an INSTEAD OF UPDATE trigger that works.

    Here is the INSTEAD OF INSERT trigger that I used:

    USE [Contact_Management]

    GO

    /****** Object: Trigger [dbo].[tr_Customer_View] Script Date: 07/01/2009 10:00:15 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER TRIGGER [dbo].[tr_Customer_View] on [dbo].[Customer_View]

    INSTEAD OF INSERT

    AS

    BEGIN

    SET NOCOUNT ON;

    insert into Addresses

    select Addresses_CustomerID, LocID, AddressLine1, AddressLine2, City, STProvCode, CountRegCode, PostalCode, HouseType, AreaSubdivision, OccupancyDate, ISWCompDate, HomeWarrantyBeginDate, NumBedrooms, ForeignInsideWireInstalled, ForeignSecurityInstalled, ForeignCanInstalled

    from inserted

    insert into Customers

    select CustomerID, ProjID, StatusCode, Customers_AcctStatusCode, DateBecameCustomer, FirstName, MiddleName, LastName, SuffixCode, Email, Email2, Email3, Phone, Phone2, Phone3, Fax, EquipWarrantyBeginDate, Notes

    from inserted

    END

    If anyone can help me build an INSTEAD OF UPDATE trigger for use with this view, it would be greatly appreciated.

    Thanks,

    Tyler

  • I'm sure we can, but what issue are you having, you haven't provided enough information, it is almost "it doesn't work, can you help?" What about it doesn't work, what errors do you get, what happens, what doesn't happen?

    CEWII

  • Elliot,

    Thank you for the quick response. Below is the trigger and UPDATE statement that I created as an attempt:

    Use Contact_Management

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TRIGGER trUp_Customer_View on Customer_View

    INSTEAD OF UPDATE

    AS

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    -- Insert statements for trigger here

    update Addresses

    set AddressLine1 = i.AddressLine1, AddressLine2 = i.AddressLine2, City = i.City, STProvCode = i.STProvCode, CountRegCode = i.CountRegCode, PostalCode = i.PostalCode, HouseType = i.HouseType, AreaSubdivision = i.AreaSubdivision, OccupancyDate = i.OccupancyDate, ISWCompDate = i.ISWCompDate, HomeWarrantyBeginDate = i.HomeWarrantyBeginDate, NumBedrooms = i.NumBedrooms, ForeignInsideWireInstalled = i.ForeignInsideWireInstalled, ForeignSecurityInstalled = i.ForeignSecurityInstalled, ForeignCanInstalled = i.ForeignCanInstalled

    from inserted i

    inner join Addresses on i.Addresses_CustomerID = Addresses.CustomerID

    update Customers

    set FirstName = i.FirstName, MiddleName = i.MiddleName, LastName = i.LastName, SuffixCode = i.SuffixCode, DateBecameCustomer = i.DateBecameCustomer, ProjID = i.ProjID, StatusCode = i.StatusCode, AcctStatusCode = i.Customers_AcctStatusCode, Email = i.Email, Email2 = i.Email2, Email3 = i.Email3, Phone = i.Phone, Phone2 = i.Phone2, Phone3 = i.Phone3, Fax = i.Fax, EquipWarrantyBeginDate = i.EquipWarrantyBeginDate

    from inserted i

    inner join Customers on i.CustomerID = Customers.CustomerID

    END

    GO

    UPDATE Customer_View

    SET AddressLine1 = "150 Elm St.",

    AddressLine2 = "Suite 110",

    City = "Fresno",

    STProvCode = 16,

    CountRegCode = 2,

    PostalCode = 90210,

    HouseType = "Casa Chica",

    AreaSubdivision = "Founders Neighborhood",

    OccupancyDate = "5/2/2008",

    ISWCompDate = "2/16/2009",

    HomeWarrantyBeginDate = "6/2/2010",

    NumBedrooms = 6,

    ForeignInsideWireInstalled = "True",

    ForeignSecurityInstalled = "True",

    ForeignCanInstalled = "True",

    FirstName = "Steve",

    MiddleName = "Allen",

    LastName = "Jones",

    SuffixCode = 2,

    DateBecameCustomer = "2/2/2001",

    ProjID = 2,

    StatusCode = 1,

    Customers_AcctStatusCode = 2,

    Email = "steve1@yahoo.com",

    Email2 = "steve1@gmail.com",

    Email3 = "steve1@hotmail.com",

    Phone = "(303) 468-1829",

    Phone2 = "(303) 335-5112",

    Phone3 = "(303) 847-8418",

    Fax = "(720) 488-1164",

    EquipWarrantyBeginDate = "11/12/2007"

    WHERE CustomerID = "R91298"

    When I execute the trigger and corresponding UPDATE command, I get the following error:

    Msg 207, Level 16, State 1, Line 2

    Invalid column name 'R91298'.

    This could very well be a simple problem, but "R91298" is not a column in my view but rather a value in one of the records in the CustomerID column, so I don't understand why SQL Server is trying to interpret it as a column.

    Do you have any ideas as to the mistake I'm making?

    Tyler

  • SET QUOTED_IDENTIFIER ON

    This line says treat everything in double quotes (") as an identifier, not a literal.

    Change them to single quotes (')

  • You were right on the money, Ian. I replaced all of the double quotes with single quotes, and it worked. Thanks for your help.

    Tyler

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply