August 5, 2009 at 9:59 pm
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
August 5, 2009 at 10:08 pm
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
August 6, 2009 at 8:20 am
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
August 6, 2009 at 8:27 am
SET QUOTED_IDENTIFIER ON
This line says treat everything in double quotes (") as an identifier, not a literal.
Change them to single quotes (')
August 6, 2009 at 9:52 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy