TRIGGER is preventing updates on fields

[weezerboy]

I have a table usersdev that contains my users information. It has 73 fields.

I want to create a trigger so that when a certain field named active_indicator is updated I insert  information into  a table named  audit_users.

I have successfully created that trigger See code below. But for the trigger I am only concerned when the  active_indicator is updated.

The problem is that when any of the other fields is updated the update doesn't save.

How do I set the trigger so that all off the fields in the usersdev  table can be updated  -not just the active_indicator field?

Its almost like I need to disable the trigger if the active_indicator field wasn't being updated.

This code right below works to save the active_indicator change information to an audit_users table.

But again only the active_indicator keeps the updated information

USE [mydatabase]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER  [myTriggerUPDATE2]

ON  [usersdev]

INSTEAD OF UPDATE

AS

DECLARE @iD int, @newactive_indicator int,@oldactive_indicator int

BEGIN

--set local variables

SET @iD = (SELECT ID FROM inserted)

SET @newactive_indicator = (SELECT active_indicator FROM inserted)

SET @oldactive_indicator = (SELECT active_indicator FROM deleted)

--UPDATE TABLE------------

UPDATE  [usersdev]

SET

active_indicator =@newactive_indicator  WHERE ID =@ID

--UPDATE AUDIT TABLE------------

INSERT audit_users (change_type, ID_changed,change_date,active_indicator)

VALUES('UPDATE', @iD,CURRENT_TIMESTAMP,@newactive_indicator)

END

[Carl Von Stetten]

It's because you're using an "INSTEAD OF" trigger rather than an "AFTER" trigger.  INSTEAD OF basically cancels out the update action and replaces it with the trigger statement.  AFTER will allow the update to complete entirely, then will execute the trigger statement.

Also, you might want to make the trigger run on INSERT and UPDATE ("AFTER INSERT, UPDATE") so it also executes when you insert a new record.

-Carl V.