Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002
    Posts
    22

    Question Unanswered: updating field using trigger in Ms sql2000

    hi guys,

    need help with triggers.

    i have 2 tbls. tblContact & tblMemo (1-m) resp..

    tblContact [PK contId(int 4), Name, MemoCount(int 4)]
    tblMemo[pk memoId (int 4), {FK} contId(int 4), memo (varchar 100)]

    I need to Write a trigger to automatically update the MemoCount field in the tblContact whenever a memo from tblMemo is inserted, updated or deleted. Assuming that the memoCount field might not have the correct value so have to take that into account when writting the trigger.

    any help will be really appreciated.
    am new to sql server..


    thanks
    mark00189@hotmail.com

  2. #2
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117

    Re: updating field using trigger in Ms sql2000

    CREATE TRIGGER [UpdateMemoCount] ON [dbo].[tblContact]
    FOR INSERT, UPDATE, DELETE
    AS
    DECLARE @contID INT, @memoSTR VARCHAR(100), @memoCnt INT
    BEGIN
    SELECT @contID = contID, @memoSTR = memo FROM INSERTED
    SET @memoCnt = LEN(@memoSTR)
    UPDATE tblContact SET MemoCount = @memoCnt WHERE contID = @contID
    END

    I hope that's what you wanted!

    Originally posted by mark00189
    hi guys,

    need help with triggers.

    i have 2 tbls. tblContact & tblMemo (1-m) resp..

    tblContact [PK contId(int 4), Name, MemoCount(int 4)]
    tblMemo[pk memoId (int 4), {FK} contId(int 4), memo (varchar 100)]

    I need to Write a trigger to automatically update the MemoCount field in the tblContact whenever a memo from tblMemo is inserted, updated or deleted. Assuming that the memoCount field might not have the correct value so have to take that into account when writting the trigger.

    any help will be really appreciated.
    am new to sql server..


    thanks
    mark00189@hotmail.com
    Steve

  3. #3
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117
    ..oops!
    Pls,replace the first line of the trigger with this one.

    CREATE TRIGGER [UpdateMemoCount] ON [dbo].[tblMemo]
    Steve

Posting Permissions

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