Problematic Stored Procedures using Views with Triggers

Hi,

I would like to use the view of the company data in the following stored procedures

without needing to pass a value into the CompanyID identity column. Is there a way to do this? The following code contains the view, the insert trigger on the view, and the desired stored procedures.

Also, this code is given me problem as well the error message says it cannot convert varchar to int. This code is located in the insert trigger.

INSERT INTO State(StateName)
VALUES(@.StateName)

Here is the code of the company view, notice the C.CompanyID it is used in the GetCompany stored procedure.

SELECT C.CompanyID, C.CompanyName, A.Street, A.City, S.StateName, A.ZipCode, A.Country, P.PhoneNumber

FROM Company AS C INNER JOIN
Address AS A ON C.AddressID = A.AddressID INNER JOIN
State AS S ON A.StateID = S.StateID INNER JOIN
Phone AS P ON C.PhoneID = P.PhoneID

Here is code for the insert trigger on the view

CREATE TRIGGER InsertTriggerOnCustomerView
ON ViewOfCompany
INSTEAD OF INSERT
AS
BEGIN

DECLARE @.CompanyName VARCHAR(128)
DECLARE @.AddressID INT
DECLARE @.Street VARCHAR(256)
DECLARE @.City VARCHAR(128)
DECLARE @.StateID INT
DECLARE @.StateName VARCHAR(128)
DECLARE @.ZipCode INT
DECLARE @.Country VARCHAR(128)
DECLARE @.PhoneID INT
DECLARE @.PhoneNumber VARCHAR(32)
--DECLARE @.CompanyID INT

SELECT
--@.CompanyID = CompanyID,
@.CompanyName = CompanyName,
@.Street = Street,
@.City = City,
@.StateName = StateName,
@.ZipCode = ZipCode,
@.Country = Country,
@.PhoneNumber = PhoneNumber
FROM INSERTED

INSERT INTO State(StateName)
VALUES(@.StateName)

SET @.StateID = @.@.IDENTITY

INSERT INTO Address(Street, City, StateID, Country, ZipCode)
VALUES(@.Street, @.City, @.StateID, @.ZipCode, @.Country)

SET @.AddressID = SCOPE_IDENTITY()

INSERT INTO Phone(PhoneNumber)
VALUES(@.PhoneNumber)

SET @.PhoneID = SCOPE_IDENTITY()

INSERT INTO Company(CompanyName, AddressID, PhoneID)
VALUES(@.CompanyName, @.AddressID, @.PhoneID)

END

The stored procedures are here.

CREATE PROCEDURE GetCompany
(
@.CompanyID INT
)
AS
BEGIN
SELECT CompanyName,Street, City, StateName,
ZipCode, Country, PhoneNumber
FROM
ViewOfCompany
WHERE
CompanyID = @.CompanyID
END
GO

CREATE PROCEDURE AddCompany
(
@.CompanyName VARCHAR(128),
@.Street VARCHAR(256),
@.City VARCHAR(128),
@.StateName VARCHAR(128),
@.ZipCode VARCHAR(128),
@.Country VARCHAR(128),
@.PhoneNumber VARCHAR(32)
)
AS
BEGIN
INSERT INTO ViewOfCompany
VALUES(@.CompanyName, @.Street, @.City, @.StateName,
@.ZipCode, @.Country, @.PhoneNumber)
END
GO

Hi,

first of all you have a misdesign in your trigger. Triggers occur per statement not per row, so inserting 50 rows in a table will fire the trigger only once not 50 times. In your case the trigger is only executed once which means that it would ingnore the 49 other inserted rows. I would first fix that and perhaps come back with the error line where the error you are describing occurs, marking it with something like -- <<-- Error occurs here.

HTH; Jens Suessmeyer.

http:/www.sqlserver2005.de

|||

I am only needing to add one company at a time. In testing the AddCompany stored procedure with Query Analyzer, it appears to work with the correct parameters. For example,

-- Insert First Company
EXEC AddCompany '', '', '', ...

-- Insert Second Company
EXEC AddCompany '', '', '', ...

With actual values, the records are inserted in the tables.

But, my delimma is in the AddCompany stored procedure I don't want to have to pass in a value for the identity column and I don't want to remove it from the view because I need it for the GetCompany stored procedure. And, I don't want to have to create a seperate view for each scenerio; so, my question is there anyway to achieve the desired goal.

I have been looking at the "check" and "no check" options, but I am not sure how they function.