Solved

SQL 2005 E, INSTEAD OF UPDATE Trigger..

Posted on 2006-11-13
3
1,582 Views
Last Modified: 2008-02-01
I havent been fumbling with triggers in years, and wonder whats wrong with this trigger?

I get "invalid object "inserted" in VS2005.

Anyone?

#########################################################
CREATE TRIGGER Trigger1
ON dbo.Site_Page
INSTEAD OF UPDATE
AS
IF EXISTS (SELECT  PageID FROM INSERTED ins WHERE SubTo = 1)

      BEGIN
      SET NOCOUNT ON
      UPDATE    Site_Page
      SET              SubTo = 1, PageName = insPageName, MenuName = ins.MenuName, SortOrder = ins.SortOrder, ViewPage = ins.ViewPage
      FROM         inserted AS ins CROSS JOIN
                            Site_Page
      END

#########################################################
0
Comment
Question by:mattisflones
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 17928667
the code looks fine, how do you try to run it?
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17928694
a "." was missing in insPageName

CREATE TRIGGER Trigger1
ON dbo.Site_Page
INSTEAD OF UPDATE
AS
IF EXISTS (SELECT  PageID FROM INSERTED ins WHERE SubTo = 1)

     BEGIN
     SET NOCOUNT ON
     UPDATE    Site_Page
     SET              SubTo = 1, PageName = ins.PageName, MenuName = ins.MenuName, SortOrder = ins.SortOrder, ViewPage = ins.ViewPage
     FROM         inserted AS ins CROSS JOIN
                           Site_Page
     END
0
 
LVL 15

Author Comment

by:mattisflones
ID: 17929181
The missing "." was not the problem.. it was a copy error.

I tested in VS2005s queryboilder, and that seems to be a little sucky... but thanks for making me think!

This worked as intended!

CREATE TRIGGER TriggerUpd
ON dbo.Site_Page
INSTEAD OF UPDATE
AS
IF EXISTS (SELECT  PageID FROM INSERTED ins WHERE SubTo = 1)

     BEGIN
     SET NOCOUNT ON
     UPDATE    Site_Page
     SET              SubTo = null, PageName = ins.PageName, MenuName = ins.MenuName, SortOrder = ins.SortOrder, ViewPage = ins.ViewPage
     FROM         inserted AS ins CROSS JOIN
                           Site_Page WHERE ins.PageID = Site_Page.PageID
     END
     ELSE
     BEGIN
     SET NOCOUNT ON
     UPDATE    Site_Page
     SET              SubTo = ins.SubTo, PageName = ins.PageName, MenuName = ins.MenuName, SortOrder = ins.SortOrder, ViewPage = ins.ViewPage
     FROM         inserted AS ins CROSS JOIN
                           Site_Page WHERE ins.PageID = Site_Page.PageID
     END
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question