Results 1 to 9 of 9
  1. #1
    Join Date
    May 2002
    Location
    Brisbane, Australia
    Posts
    13

    Question Unanswered: How To: Triggers

    hi,

    This is my first time with triggers and I am having trouble getting started.

    Want I want to do is insert something in another table when some thing is added to the current table. But it has to add it where the foriegn key = the record that is being updated.

    So this is what I'm after,

    Execute the following Update:

    UPDATE tbl_Pages
    SET PageDisabled = 1 'it is a bit data type
    WHERE ID = 2

    So after this is happed I want the following to happen

    UPDATE tbl_Menus
    SET MenuDisabled = 1 'it is a bit data type
    WHERE relPageID = tblPages.ID

    How do I do this?

  2. #2
    Join Date
    Jul 2002
    Location
    The Hague, NL
    Posts
    9
    Use this:

    UPDATE tbl_Menus
    SET MenuDisabled = 1 'it is a bit data type
    WHERE relPageID = inserted.ID



    inserted is a logical table which contain(s) the record(s) that was/were inserted or updated.

    deleted is the logical table which contains the records that were deleted, or the old values before the UPDATE

  3. #3
    Join Date
    May 2002
    Location
    Brisbane, Australia
    Posts
    13
    Thanks for that,

    But it didn't seem to work the following:

    CREATE TRIGGER Update_HME_Menus_MenuDisbaled ON [dbo].[tbl_HME_Pages]
    FOR UPDATE
    AS

    UPDATE [dbo].[tbl_HME_Menus]
    SET MenuDisabled = 1
    WHERE relPageID = inserted.ID

    Gives me this error:

    Error 107: The column prefix 'inserted' does not match with a table name
    or alias name used in the query.

    Btw, I'm using SQL 2000

  4. #4
    Join Date
    Jul 2002
    Location
    The Hague, NL
    Posts
    9

    Cool

    Sorry for that, I think it should be:

    Code:
    CREATE TRIGGER Update_HME_Menus_MenuDisbaled ON [dbo].[tbl_HME_Pages] 
    FOR UPDATE 
    AS 
    
    UPDATE [dbo].[tbl_HME_Menus] 
    SET MenuDisabled = 1 
    WHERE relPageID IN (SELECT ID FROM inserted)
    HTH

  5. #5
    Join Date
    May 2002
    Location
    Brisbane, Australia
    Posts
    13
    That still doesn't work.

    Error 207: Invalid column name 'ID'.

    You must be getting close, also where do you find out about this stuff??

  6. #6
    Join Date
    Jul 2002
    Location
    The Hague, NL
    Posts
    9

    Cool

    The ID in (SELECT ID FROM Inserted) should match the primary key of the table on which the trigger is attached.

    How I find out? Trial and error

  7. #7
    Join Date
    May 2002
    Location
    Brisbane, Australia
    Posts
    13
    Well, I reckon it is because the table Inserted doesn't exist.

    I am probably wrong but why doesn't it work??

  8. #8
    Join Date
    Jul 2002
    Location
    The Hague, NL
    Posts
    9

    Cool

    As I said, the table Inserted is a logical table which exists during the execution of a trigger. It contains all the inserted/updated records in the currect transactions (a trigger executes within a implicit transaction).

  9. #9
    Join Date
    May 2002
    Location
    Brisbane, Australia
    Posts
    13
    well how come I can't create this trigger? Do I have to do it via querry analyzer??

Posting Permissions

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