Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Unanswered: 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 INS

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I 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

    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    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.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Good catch, snail! How about that, Brett, huh? huh? huh?

  5. #5
    Join Date
    Feb 2004
    Posts
    2
    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •