Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    18

    Lightbulb Unanswered: Triggers affecting multiple tables

    I have a table "PageInfo" wich has columns "PageID, Title(has "AboutUs" as one of the values), DateModified, Active". My other table is "AboutUs" and has columns "ID, WhatsNew, Welcome, Active".

    My goal here is that if table "AboutUs" is affected as an "INSERT" or an "UPDATE" on any row, I want "PageInfo.DateModified" WHERE "PageInfo.Title = AboutUs" to be updated with "getDate()". Don't know if I'm clear enough but thanks in advance.

    Gazzou

  2. #2
    Join Date
    May 2003
    Location
    Parsippany NJ
    Posts
    36
    create trigger tr_AboutUs
    on AboutUs
    for insert,Update
    as
    update PageInfo set DataModified = getdate() where Title = 'AboutUs'

  3. #3
    Join Date
    Jan 2004
    Posts
    18

    Question Why can't I update?

    It all makes a lot of sense, but What I don't understand is, if that trigger is activated, I can no longer update my table "AboutUs"

  4. #4
    Join Date
    Jan 2004
    Posts
    18

    Question

    OK, I noticed I had another trigger on table "PageInfo", I removed it, now I can update table "AboutUs" but it doesn't update "PageInfo.DateModified"

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Whoa!

    OK, I noticed I had another trigger on table "PageInfo",

    I'd immediatley take a step back, get an ER Diagram from the DBA, and a spec of the object (yeah right) or at a minimum script the objects you're working with, include every detail, and review...

    THEN make a plan..and post the DDL here (or attach it)
    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.

  6. #6
    Join Date
    Jan 2004
    Posts
    18
    What do you mean? This is my plan:

    "I have a table "PageInfo" wich has columns "PageID, Title(has "AboutUs" as one of the values), DateModified, Active". My other table is "AboutUs" and has columns "ID, WhatsNew, Welcome, Active".

    My goal here is that if table "AboutUs" is affected as an "INSERT" or an "UPDATE" on any row, I want "PageInfo.DateModified" WHERE "PageInfo.Title = AboutUs" to be updated with "getDate()""

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Gazzou
    What do you mean?
    I mean, you don't know the structure of your database.

    You didn't know the existance of the other trigger.

    Cut and Paste this

    Code:
    USE Northwind
    GO
    
    -- I have a table "PageInfo" wich has columns 
    -- "PageID, Title, DateModified, Active". 
    
    CREATE TABLE myPageInfo(
    	  PageId 	int IDENTITY(1,1) PRIMARY KEY
    	, Title 	varchar(50)
    	, DateModified 	datetime 	  DEFAULT GetDate()
    	, Active 	char(1) 	  DEFAULT 'Y')
    GO
    
    -- (has "AboutUs" as one of the values)
    -- OK 
    
    INSERT INTO myPageInfo(Title) SELECT 'AboutUs'
    GO
    
    SELECT * FROM myPageInfo
    
    --My other table is "AboutUs" and has 
    --columns "ID, WhatsNew, Welcome, Active".
     
    -- Question?  What's their relationship?
    
    CREATE TABLE myAboutUs(
    	  [Id] 		int IDENTITY(1,1) PRIMARY KEY
    	, WhatsNew 	varchar(50)
    	, Welcome 	varchar(50)
    	, Active 	char(1) 	  DEFAULT 'Y')
    GO
    
    
    --My goal here is that if table "AboutUs" 
    --is affected as an "INSERT" or an "UPDATE" on any row
    --I want "PageInfo.DateModified" WHERE "PageInfo.Title = AboutUs" 
    --to be updated with "getDate()". 
    --OK
    
    
    CREATE TRIGGER myTrigger99 ON myAboutUs
    FOR INSERT, UPDATE
    AS
      BEGIN
    	UPDATE myPageInfo
    	   SET DateModified = GetDate()
    	 WHERE Title = 'AboutUs'
      END
    GO
    
    
    --OK, assume we need a row here
    
    INSERT INTO myAboutUs(WhatsNew,Welcome) SELECT 'AboutUs','New Baby'
    GO
    
    SELECT * FROM myAboutUs
    SELECT * FROM myPageInfo
    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.

  8. #8
    Join Date
    Jan 2004
    Posts
    18
    Thanks for your help Brett,

    It all works fine now.

Posting Permissions

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