Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2007
    Posts
    28

    Unanswered: trigger for updating a value on one table when that value is updated on the base tabl

    HI all couldn't get this to work

    if the id1 will change in table1 it should also change the corresponding id1 field in table2 it does not do anything please help!!

    CREATE TRIGGER [dbo].[IDCHANGE]
    ON [dbo].[table1]
    AFTER UPDATE
    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.


    SET NOCOUNT ON;

    IF UPDATE (ID1)
    BEGIN
    UPDATE tb2
    SET tb2.ID1 = 'I.id1'
    FROM table2 tb2
    JOIN deleted d ON tb2.id1=d.ID1 JOIN INSERTED i ON i.id1 = d.id1
    WHERE tb2.ID1 = d.id1
    END
    END

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is a "poster child" example for why schemas ought to be normalized.

    Assuming that the id1 column is the PK (Primary Key) for both table1 and table2, I don't know of any way to do this because you can see all of the "new" and "old" values for the PK but can't determine which new and old values match each other. If you enforce single row updates (a nearly fatal mistake for performance), you can work around this problem.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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