Can't create INSTEAD OF trigger

I wanted to learn how to do this, so I created the following non-updatable
view:
CREATE VIEW [dbo].[TestAggregateView]
WITH SCHEMABINDING, VIEW_METADATA
AS
SELECT C.CustId, C.CrLmt, SUM(A.OrigDocAmt) AS TotAmt
FROM dbo.Customer AS C INNER JOIN
dbo.ARDoc AS A ON A.CustId = C.CustId
GROUP BY C.CustId, C.CrLmt
I wrote this UPDATE statement, which fails:
UPDATE TestAggregateView SET
CrLmt = 20000
WHERE CrLmt = 0
AND TotAmt > 10000
The goal is to create a trigger that will run a different and valid update
statement. Here is the code I tried:
CREATE TRIGGER [dbo].[TestAggregateView_Instead_Update]
ON [dbo].[TestAggregateView]
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE Customer SET
CrLmt = 20000
WHERE Customer.CustID = inserted.CustID
END
The error I get is:
Msg 4104, Level 16, State 1, Procedure TestAggregateView_Instead_Update,
Line 14
The multi-part identifier "inserted.CustID" could not be bound.
Can someone tell me what I'm doing wrong? I could not find a sample INSTEAD
OF UPDATE trigger to compare this with, but according to the sample INSTEAD
OF INSERT, inserted is the valid name for the temporary table, and I think it
should hold all the records that meet the select requirements.Here's the problem:
UPDATE Customer SET
CrLmt = 20000
WHERE Customer.CustID = inserted.CustID
You refer to a tablme named INSERTED in the WHERE clause but you haven't listed it in the "FROM"
clause (I put FROM in quptes since UPDATE according to ANSI SQL doesn't have a FROM clause).
So the problem is with your UPDATE inside your trigger, not with the trigger per se. You have to
re-write that UPDATE so it is a valid UPDATE statement. For instance
UPDATE Customer
SET CrLmt = 20000
WHERE Customer.CustID IN (SELECT CustID FROM inserted)
Or:
UPDATE Customer
SET CrLmt = 20000
WHERE EXISTS(SELECT * FROM inserted AS i WHERE i.CustID = Customer.CustID )
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:F1BAB94D-ECFE-4BF7-B1EB-1330FFD17B0F@.microsoft.com...
>I wanted to learn how to do this, so I created the following non-updatable
> view:
> CREATE VIEW [dbo].[TestAggregateView]
> WITH SCHEMABINDING, VIEW_METADATA
> AS
> SELECT C.CustId, C.CrLmt, SUM(A.OrigDocAmt) AS TotAmt
> FROM dbo.Customer AS C INNER JOIN
> dbo.ARDoc AS A ON A.CustId = C.CustId
> GROUP BY C.CustId, C.CrLmt
> I wrote this UPDATE statement, which fails:
> UPDATE TestAggregateView SET
> CrLmt = 20000
> WHERE CrLmt = 0
> AND TotAmt > 10000
> The goal is to create a trigger that will run a different and valid update
> statement. Here is the code I tried:
> CREATE TRIGGER [dbo].[TestAggregateView_Instead_Update]
> ON [dbo].[TestAggregateView]
> INSTEAD OF UPDATE
> AS
> BEGIN
> SET NOCOUNT ON;
> UPDATE Customer SET
> CrLmt = 20000
> WHERE Customer.CustID = inserted.CustID
> END
> The error I get is:
> Msg 4104, Level 16, State 1, Procedure TestAggregateView_Instead_Update,
> Line 14
> The multi-part identifier "inserted.CustID" could not be bound.
> Can someone tell me what I'm doing wrong? I could not find a sample INSTEAD
> OF UPDATE trigger to compare this with, but according to the sample INSTEAD
> OF INSERT, inserted is the valid name for the temporary table, and I think it
> should hold all the records that meet the select requirements.
>

Can't create a BCP format file

Hi there,
does anyone know why I cannot create a bcp format file? I get
the following error. If I change the command to queryout instead of format,
it logs on OK and creates an output file. So why am I getting the error
below?
SQLState = 37000, NativeError = 4060
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
requested in login 'select convert(integer, p'. Login fails.
Waldy,
Please post your bcp command line. As it is, bcp apparently believes that
your login is named
'select convert(integer, p', which is surely not correct.
RLF
"Waldy" <someone@.microsoft.com> wrote in message
news:u1fydnTUIHA.5836@.TK2MSFTNGP04.phx.gbl...
> Hi there,
> does anyone know why I cannot create a bcp format file? I get
> the following error. If I change the command to queryout instead of
> format, it logs on OK and creates an output file. So why am I getting the
> error below?
> SQLState = 37000, NativeError = 4060
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open
> database requested in login 'select convert(integer, p'. Login fails.
>
|||Waldy (someone@.microsoft.com) writes:
> does anyone know why I cannot create a bcp format file? I get the
> following error. If I change the command to queryout instead of format,
> it logs on OK and creates an output file. So why am I getting the error
> below?
> SQLState = 37000, NativeError = 4060
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open
> database requested in login 'select convert(integer, p'. Login fails.
It seems to be a bug in BCP for SQL 2000. I got a similar error for this
command line:
bcp "SELECT CustomerID, COUNT(*) AS cnt FROM Northwind..Orders GROUP BY CustomerID" format xxx -f slask.fmt -T -n
with SQL 2000. With SQL 2005, the error message was more terse:
An error occurred while processing the command line.
The error in SQL 2000 actually makes sense. You are not saying QUERYOUT,
which means that what you have in the first argument is a table. But since
that database and table does not exist...
In any case, since the error message on SQL 2005 is complete crap, I
filed a bug report
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=321353
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx