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 connect to SQL Server 2000 with Visual Studio 2005

I've been using Delphi for years (Win 32) and connecting to my SQL Server
databases with no problem.
I am now trying to learn both VB and C#. (Visual studio 2005)
From the Data menu, I select Add New Data Source
I then choose Database
I select Microsoft SQL Server as the Data Source
The Add Connection Dialog appears
It populates the Server name dropdown with both of my SQL servers.
However, when I try to select a database name, the dropdown is never
populated.
If I manually enter a known database name and try to connect, I get a
TimeOut Expired (or server not responding) error.
This happens whether I use windows authentication or SQL server
authentication.
This drill works fine with Delphi (Win 32) and connects without a hitch.
Any help would be appreciated.
Regards,
Dave
Take a look at my connecting whitepaper on my blog. It lays down the basics.
I expect the rights are not setup correctly or you've chosen a server
instance that's not visible on the network.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Dave Davis" <online@.blindsided.org> wrote in message
news:uvZV0Qt3HHA.4400@.TK2MSFTNGP06.phx.gbl...
> I've been using Delphi for years (Win 32) and connecting to my SQL Server
> databases with no problem.
> I am now trying to learn both VB and C#. (Visual studio 2005)
> From the Data menu, I select Add New Data Source
> I then choose Database
> I select Microsoft SQL Server as the Data Source
> The Add Connection Dialog appears
> It populates the Server name dropdown with both of my SQL servers.
> However, when I try to select a database name, the dropdown is never
> populated.
> If I manually enter a known database name and try to connect, I get a
> TimeOut Expired (or server not responding) error.
> This happens whether I use windows authentication or SQL server
> authentication.
> This drill works fine with Delphi (Win 32) and connects without a hitch.
> Any help would be appreciated.
> Regards,
> Dave
>
|||Bill,
Remember, I connect without any problems using Borland's Delphi. I have for
years.
I also connect with no problems using ASP.
If the rights aren't setup properly or the instance wasn't visible on the
network it seems to me that I couldn't connect with anything.
Or, does .net require different rights or something else different in the
SQL server setup?
Thanks,
Dave
"William Vaughn" <billvaNoSPAM@.betav.com> wrote in message
news:elZYHTv3HHA.1484@.TK2MSFTNGP06.phx.gbl...
> Take a look at my connecting whitepaper on my blog. It lays down the
> basics. I expect the rights are not setup correctly or you've chosen a
> server instance that's not visible on the network.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant, Dad, Grandpa
> Microsoft MVP
> INETA Speaker
> www.betav.com
> www.betav.com/blog/billva
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ------
> "Dave Davis" <online@.blindsided.org> wrote in message
> news:uvZV0Qt3HHA.4400@.TK2MSFTNGP06.phx.gbl...
>

Can't connect to SQL Server 2000 with Visual Studio 2005

I've been using Delphi for years (Win 32) and connecting to my SQL Server
databases with no problem.
I am now trying to learn both VB and C#. (Visual studio 2005)
From the Data menu, I select Add New Data Source
I then choose Database
I select Microsoft SQL Server as the Data Source
The Add Connection Dialog appears
It populates the Server name dropdown with both of my SQL servers.
However, when I try to select a database name, the dropdown is never
populated.
If I manually enter a known database name and try to connect, I get a
TimeOut Expired (or server not responding) error.
This happens whether I use windows authentication or SQL server
authentication.
This drill works fine with Delphi (Win 32) and connects without a hitch.
Any help would be appreciated.
Regards,
DaveTake a look at my connecting whitepaper on my blog. It lays down the basics.
I expect the rights are not setup correctly or you've chosen a server
instance that's not visible on the network.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"Dave Davis" <online@.blindsided.org> wrote in message
news:uvZV0Qt3HHA.4400@.TK2MSFTNGP06.phx.gbl...
> I've been using Delphi for years (Win 32) and connecting to my SQL Server
> databases with no problem.
> I am now trying to learn both VB and C#. (Visual studio 2005)
> From the Data menu, I select Add New Data Source
> I then choose Database
> I select Microsoft SQL Server as the Data Source
> The Add Connection Dialog appears
> It populates the Server name dropdown with both of my SQL servers.
> However, when I try to select a database name, the dropdown is never
> populated.
> If I manually enter a known database name and try to connect, I get a
> TimeOut Expired (or server not responding) error.
> This happens whether I use windows authentication or SQL server
> authentication.
> This drill works fine with Delphi (Win 32) and connects without a hitch.
> Any help would be appreciated.
> Regards,
> Dave
>|||Bill,
Remember, I connect without any problems using Borland's Delphi. I have for
years.
I also connect with no problems using ASP.
If the rights aren't setup properly or the instance wasn't visible on the
network it seems to me that I couldn't connect with anything.
Or, does .net require different rights or something else different in the
SQL server setup?
Thanks,
Dave
"William Vaughn" <billvaNoSPAM@.betav.com> wrote in message
news:elZYHTv3HHA.1484@.TK2MSFTNGP06.phx.gbl...
> Take a look at my connecting whitepaper on my blog. It lays down the
> basics. I expect the rights are not setup correctly or you've chosen a
> server instance that's not visible on the network.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant, Dad, Grandpa
> Microsoft MVP
> INETA Speaker
> www.betav.com
> www.betav.com/blog/billva
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ----
---
> "Dave Davis" <online@.blindsided.org> wrote in message
> news:uvZV0Qt3HHA.4400@.TK2MSFTNGP06.phx.gbl...
>