TR_APPUSER_AUDIT_UPDATE

            create trigger [dbo].[TR_APPUSER_AUDIT_UPDATE] on [dbo].[APPUSER] after update not for replication
            AS 

            declare @ChangeAgentID uniqueidentifier
            declare @AuditKey uniqueidentifier
            declare @AuditDate datetime
            declare @DateChanged datetime

            set nocount on
            --make sure LASTLOGINDATE is not updated
            if not UPDATE(LASTLOGINDATE)
            begin
                -- make sure the datestamp and changeagent fields are updated
                set @DateChanged = null;
                set @ChangeAgentID = null;

                if not update(CHANGEDBYID) 
                begin

                    --Get a default changeagent id.  Applications should always explicitly included CHANGEDBYID in any updates to avoid a default change agent id.
                    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENTFROMCONTEXT @ChangeAgentID output;

                    if not update(DATECHANGED) 
                    begin
                        -- neither datechanged nor changedbyid were updated, so update both
                        set @DateChanged = GetDate();
                        update dbo.APPUSER set CHANGEDBYID = @ChangeAgentID, DATECHANGED = @DateChanged from dbo.APPUSER inner join INSERTED on APPUSER.ID = INSERTED.ID;
                    end
                    else
                        -- date changed was updated, but changedbyid wasn't so just update changedbyid
                        update dbo.APPUSER set CHANGEDBYID = @ChangeAgentID from dbo.APPUSER inner join INSERTED on APPUSER.ID = INSERTED.ID;
                    end

                else if not update(DATECHANGED) 
                begin
                    set @DateChanged = GetDate();
                    update dbo.APPUSER set DATECHANGED = @DateChanged from dbo.APPUSER inner join INSERTED on APPUSER.ID = INSERTED.ID;
                end

                --peform the audit
                if dbo.UFN_AUDITENABLED('APPUSER') = 1
                begin

                    -- the audit key is used to associate the rows in the audit table with a single atomic deletion/modification
                    -- we pre-fetch the audit date to make sure both rows contain exactly the same date value.

                    set @AuditKey = NewID();
                    set @AuditDate = GetDate();

                    INSERT INTO dbo.APPUSERAUDIT(
                    AUDITRECORDID, 
                    AUDITKEY,
                    AUDITCHANGEAGENTID,
                    AUDITDATE, 
                    AUDITTYPECODE,
                    [USERSID],
                    [DISPLAYNAME],
                    [ISSYSADMIN],
                    [ADDEDBYID],
                    [CHANGEDBYID],
                    [DATEADDED],
                    [DATECHANGED],
                    [CONSTITUENTID],
                    [CUSTOM_AUTHENTICATION_USERID],
                    [SITEID],
                    [WINDOWSUSERNAME],
                    [ISSYSTEM],
                    [EMAILADDRESS],
                    [ISACTIVE],
                    [LASTLOGINDATE],
                    [ISPROXYUSER],
                    [INVALIDLOGINATTEMPTS],
                    [PROXYOWNERID],
                    [HASRUNASRIGHTS]
                    ) 
                    SELECT 
                    ID,
                    @AuditKey,
                    COALESCE(@ChangeAgentID,(SELECT CHANGEDBYID FROM INSERTED WHERE INSERTED.ID=DELETED.ID)), --If explicitly updating CHANGEDBYID, use that, else use the the one default one fetched above
                    @AuditDate,
                    0, --Before Update
                    [USERSID],
                    [DISPLAYNAME],
                    [ISSYSADMIN],
                    [ADDEDBYID],
                    [CHANGEDBYID],
                    [DATEADDED],
                    [DATECHANGED],
                    [CONSTITUENTID],
                    [CUSTOM_AUTHENTICATION_USERID],
                    [SITEID],
                    [WINDOWSUSERNAME],
                    [ISSYSTEM],
                    [EMAILADDRESS],
                    [ISACTIVE],
                    [LASTLOGINDATE],
                    [ISPROXYUSER],
                    [INVALIDLOGINATTEMPTS],
                    [PROXYOWNERID],
                    [HASRUNASRIGHTS]
                    FROM DELETED;

                    INSERT INTO dbo.APPUSERAUDIT(
                    AUDITRECORDID, 
                    AUDITKEY,
                    AUDITCHANGEAGENTID,
                    AUDITDATE, 
                    AUDITTYPECODE,
                    [USERSID],
                    [DISPLAYNAME],
                    [ISSYSADMIN],
                    [ADDEDBYID],
                    [CHANGEDBYID],
                    [DATEADDED],
                    [DATECHANGED],
                    [CONSTITUENTID],
                    [CUSTOM_AUTHENTICATION_USERID],
                    [SITEID],
                    [WINDOWSUSERNAME],
                    [ISSYSTEM],
                    [EMAILADDRESS],
                    [ISACTIVE],
                    [LASTLOGINDATE],
                    [ISPROXYUSER],
                    [INVALIDLOGINATTEMPTS],
                    [PROXYOWNERID],
                    [HASRUNASRIGHTS]
                    ) 
                    SELECT 
                    ID,
                    @AuditKey,
                    COALESCE(@ChangeAgentID,CHANGEDBYID), --If explicitly updating CHANGEDBYID, use that, else use the the one default one fetched above
                    @AuditDate,
                    1, --After Update
                    [USERSID],
                    [DISPLAYNAME],
                    [ISSYSADMIN],
                    [ADDEDBYID],
                    coalesce(@ChangeAgentID, [CHANGEDBYID]),
                    [DATEADDED],
                    coalesce(@DateChanged, [DATECHANGED]),
                    [CONSTITUENTID],
                    [CUSTOM_AUTHENTICATION_USERID],
                    [SITEID],
                    [WINDOWSUSERNAME],
                    [ISSYSTEM],
                    [EMAILADDRESS],
                    [ISACTIVE],
                    [LASTLOGINDATE],
                    [ISPROXYUSER],
                    [INVALIDLOGINATTEMPTS],
                    [PROXYOWNERID],
                    [HASRUNASRIGHTS]
                    FROM INSERTED;

                end
            end