Date parsed: 22/10/2007 20:14:41
Date: Mon, 22 Oct 2007 18:14:41 +0200

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.
>
>