[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

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

---

Hello there

Thank you so much for help but also not OK.

bintaleb

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

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

bintaleb