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