Problem Creating TRIGGER on a VIEW

Hello,

I have a problem that definitely has me stumped.

I have a view that looks at data in a different database. Some of the fields in the view are updateable and some are not. I am trying to create a trigger against the view that will allow me to audit the updates into an audit table. I am having problems when trying to execute the CREATE TRIGGER statement.

I keep getting the message...

Server: Msg 208, Level 16, State 4, Procedure updDocInfo, Line 1
Invalid object name 'vwDC_DocInfo'.

Where vwDC_DocInfo is the name of the view.

Does anyone have any idea why I might be getting this error? The VIEW definitely does exist and I am executing the script in the same database as the view.

The script is included below...

CREATE TRIGGER updDocInfo
ON [vwDC_DocInfo]
FOR UPDATE AS

DECLARE @.ModifiedDate AS DATETIME
SELECT @.ModifiedDate = GETDATE()

-- Audit OLD record.
INSERT tblAudit_DC_DocInfo
SELECT
0 AS AuditType,
ItemID,
Comment,
VersionComment,
CheckedOut,
Title,
BaseParagonDocumentNumber,
Author,
ClientDocumentNumber,
ClientDocumentType,
ClientJobNumber,
[Module],
Unit,
SequenceNumber,
RevisionDate,
ApprovedBy,
CheckedDepartmentManager,
CheckedLeadEngineerDesigner,
IssueType,
RevisedByDesigner,
RevisedByEngineer,
RevisionCode,
HSECheck,
CurrentVersionNumber,
CurrentVersionDate,
USER AS ChangedByUser,
@.ModifiedDate AS DateChanged
FROM DELETED DEL

-- Audit NEW record.
INSERT tblAudit_DC_DocInfo
SELECT
0 AS AuditType,
ItemID,
Comment,
VersionComment,
CheckedOut,
Title,
BaseParagonDocumentNumber,
Author,
ClientDocumentNumber,
ClientDocumentType,
ClientJobNumber,
[Module],
Unit,
SequenceNumber,
RevisionDate,
ApprovedBy,
CheckedDepartmentManager,
CheckedLeadEngineerDesigner,
IssueType,
RevisedByDesigner,
RevisedByEngineer,
RevisionCode,
HSECheck,
CurrentVersionNumber,
CurrentVersionDate,
USER AS ChangedByUser,
@.ModifiedDate AS DateChanged
FROM INSERTED INSI didn't think you could create a trigger on a view, but BOL says otherwise...

Still doesn't keep my example here from exploding though

USE Northwind
GO

CREATE VIEW myView99 AS SELECT * FROM Orders
GO

CREATE TRIGGER myTrigger99 ON myView99 FOR INSERT AS PRINT 'HI'
GO

DROP VIEW myView99
GO|||BOL:

Designing Triggers
Microsoft SQL Server 2000 provides two options when designing triggers:

INSTEAD OF triggers are executed in place of the usual triggering action. INSTEAD OF triggers can also be defined on views with one or more base tables, where they can extend the types of updates a view can support.

AFTER triggers are executed after the action of the INSERT, UPDATE, or DELETE statement is performed. Specifying AFTER is the same as specifying FOR, which is the only option available in earlier versions of SQL Server. AFTER triggers can be specified !!! ONLY !!! on tables.|||Good catch, snail! How about that, Brett, huh? huh? huh?|||Originally posted by snail
BOL:

Designing Triggers
Microsoft SQL Server 2000 provides two options when designing triggers:

INSTEAD OF triggers are executed in place of the usual triggering action. INSTEAD OF triggers can also be defined on views with one or more base tables, where they can extend the types of updates a view can support.

AFTER triggers are executed after the action of the INSERT, UPDATE, or DELETE statement is performed. Specifying AFTER is the same as specifying FOR, which is the only option available in earlier versions of SQL Server. AFTER triggers can be specified !!! ONLY !!! on tables.

Thanks,

I did some experimenting after I created the post. I finally realized that I could only create a INSTEAD OF TRIGGER. It is not obvious in the documentation. The only problem was that I then had to go and recreate the transaction, as I really did want the transaction to go through. Very odd use of triggers...