Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Location
    Romania
    Posts
    70

    Unanswered: Trigger - Easy Question

    I am a beginner, and an example will be usefully for me.

    Two tables

    Table_A (ID_A , Name_A)
    Table_B (ID_B , Name_B)

    I want to use triggers:
    If user update the field “Name_B” in Table_B then field Name_A to be update in Table_A where ID_B = ID_A
    Is an easy question, but for me is usefully to understand triggers.

    Sorry for my poor English
    Thanks

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Trigger - Easy Question

    Go to BOL and see the Create Trigger topic. There is a great example for what you wanted to do.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    BOL = Books Online

    It come with the Client side tools...

    Or

    http://msdn.microsoft.com/library/de...start_4fht.asp

    CREATE TRIGGER myTrigger ON Table_A
    FOR UPDATE
    AS
    UPDATE Table_B
    SET Name_B = inserted.Name_A
    FROM inserted
    WHERE ID_A = ID_B
    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.

  4. #4
    Join Date
    Nov 2003
    Location
    Romania
    Posts
    70
    Thanks
    Brett Kaiser

  5. #5
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    In your case will be very useful: UPDATE(column) or COLUMNS_UPDATED().

    CREATE TRIGGER trigger_name
    ON { table | view }
    [ WITH ENCRYPTION ]
    {
    { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
    [ WITH APPEND ]
    [ NOT FOR REPLICATION ]
    AS
    [ { IF UPDATE ( column )
    [ { AND | OR } UPDATE ( column ) ]
    [ ...n ]
    | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
    { comparison_operator } column_bitmask [ ...n ]
    } ]
    sql_statement [ ...n ]
    }
    }

  6. #6
    Join Date
    Nov 2003
    Location
    Romania
    Posts
    70
    Brett Kaiser, I try your example, but I get an error

    "Key column information is insufficient or incorrect. Too many rows were affected by update"

    I try to change (update) field [Denumire grupa] in table Grupe and I want to update automatically field [Denumire grupa] in table Grupe_SubGrupe

    This is my tables:

    First TABLE

    CREATE TABLE [dbo].[Grupe]
    [Cod grupa] VARCHAR(20) NOT NULL,
    [Denumire grupa] VARCHAR(100) NOT NULL,
    CONSTRAINT [PK_Grupe] PRIMARY KEY NONCLUSTERED ( [Cod grupa] )

    Second Table

    CREATE TABLE [dbo].[Grupe_SubGrupe]
    [Cod grupa] VARCHAR(20) NOT NULL,
    [Cod subgrupa] VARCHAR(20) NOT NULL,
    [Denumire grupa] VARCHAR(100) NOT NULL,
    [Denumire SubGrupa] VARCHAR(100) NOT NULL,
    CONSTRAINT [Key_Unica] PRIMARY KEY NONCLUSTERED ( [Cod grupa],[Cod subgrupa] )

    /*-------- Foreign Keys-----------------*/
    ALTER TABLE [dbo].[Grupe_SubGrupe]
    ADD CONSTRAINT [FK_Grupe_SubGrupe_Grupe] FOREIGN KEY([Cod grupa])
    REFERENCES [dbo].[Grupe] ( [Cod grupa] )
    ON UPDATE CASCADE
    ON DELETE NO ACTION;


    This is my Trigger

    CREATE TRIGGER ActualizareDenumire ON Grupe
    FOR UPDATE
    AS
    IF UPDATE ([denumire grupa])
    UPDATE DeActualizat
    SET DeActualizat.[denumire grupa] = Modificat.[denumire grupa]
    FROM Grupe Modificat, Grupe_SubGrupe DeActualizat
    WHERE Modificat.[cod grupa] = DeActualizat.[cod grupa]

Posting Permissions

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