Results 1 to 5 of 5

Thread: [RESOLVED] Trigger Problem

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jul 2006
    Posts
    97

    Resolved [RESOLVED] Trigger Problem

    Hi everybody

    In my case I just want trigger to work after row inserted when status = 16 or row updated to status 16 but the problem is when any modification is made in any column the trigger will be running but that is wrong in my case.

    here is my trigger:
    Code:
    ALTER TRIGGER [dbo].[TRIG_CopyRegularEmp] 
       ON  [dbo].[Table1]
       AFTER INSERT,UPDATE
    AS 
    BEGIN
    Declare @ID int 
    select @ID = ID from inserted
    INSERT INTO Table2
                   (Table2.ID, Table2.Name, Table2.Status, Table2.Job)
    SELECT     Table1.ID, Table1.Name, Table1.Status,Table1.Job
    FROM         Table1
    WHERE     (Table1.Status = 16)
    AND        (Table1.ID = @ID)
    END
    can anyone correct trigger to achieve my case?

    bintaleb

  2. #2
    New Member
    Join Date
    Nov 2002
    Posts
    14

    Re: Trigger Problem

    Please try the following


    Code:
    ALTER TRIGGER [dbo].[TRIG_CopyRegularEmp] 
       ON  [dbo].[Table1]
       AFTER INSERT,UPDATE
    AS 
    BEGIN
          Declare @ID int 
          Declare @status int 
          select @ID = ID, @status = status from inserted
          If (@status =16)
          BEGIN
                   INSERT INTO Table2
                   (Table2.ID, Table2.Name, Table2.Status, Table2.Job)
                    SELECT     Table1.ID, Table1.Name, Table1.Status,Table1.Job
                    FROM         inserted
                    WHERE     (inserted.id = @ID)
          END
    END

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Jul 2006
    Posts
    97

    Re: Trigger Problem

    Hello there

    Thank you so much for help but also not OK.

    bintaleb

  4. #4
    New Member
    Join Date
    Nov 2002
    Posts
    14

    Re: Trigger Problem

    I think i missed out the comparision sorry, please try the following

    Code:
    ALTER TRIGGER [dbo].[TRIG_CopyRegularEmp] 
       ON  [dbo].[Table1]
       AFTER INSERT,UPDATE
    AS 
    BEGIN
          Declare @ID int 
          Declare @OldStatus int, @NewStatus Int 
          select @ID = ID, @NewStatus = status from inserted
          select @OldStatus = Status from deleted
          If (@NewStatus =16 AND  @OldStatus!=@NewStatus)
          BEGIN
                   INSERT INTO Table2
                   (Table2.ID, Table2.Name, Table2.Status, Table2.Job)
                    SELECT     inserted.ID, inserted.Name, inserted.Status,inserted.Job
                    FROM         inserted
                    WHERE     (inserted.id = @ID)
          END
    END
    --Nagendra
    Last edited by Nagendra Rao; Feb 25th, 2011 at 04:00 AM.

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Jul 2006
    Posts
    97

    Re: Trigger Problem

    Thank you so much that is what I exactly looking for.

    bintaleb

Posting Permissions

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



Click Here to Expand Forum to Full Width