TR_APPUSER_ARCHIVE (Trigger)

            
            create trigger dbo.TR_APPUSER_ARCHIVE
                on dbo.APPUSER
                after insert, update
                not for replication
                as begin
                    
                    declare @APPUSERID uniqueidentifier;
                    declare @DISPLAYNAME nvarchar(255);
                    declare @CHANGEAGENT uniqueidentifier;
                    declare @TIMESTAMP datetime;

                    select 
                        @APPUSERID = ID,
                        @DISPLAYNAME = DISPLAYNAME,
                        @CHANGEAGENT = CHANGEDBYID,
                        @TIMESTAMP = DATECHANGED 
                    from INSERTED
                    where ISSYSTEM = 0;

                    -- the trigger fires even when no rows are updated
                    if @APPUSERID is not null        
                        if exists(select 1 from dbo.APPUSERARCHIVE where APPUSERID = @APPUSERID)
                            update 
                                ARCHIVE 
                            set
                                ARCHIVE.DISPLAYNAME = @DISPLAYNAME,
                                ARCHIVE.CHANGEDBYID = @CHANGEAGENT,
                                ARCHIVE.DATECHANGED = @TIMESTAMP 
                            from 
                                dbo.APPUSERARCHIVE ARCHIVE
                            where 
                                @APPUSERID = ARCHIVE.APPUSERID;
                        else
                            insert into dbo.APPUSERARCHIVE(
                                APPUSERID, 
                                DISPLAYNAME,
                                ADDEDBYID, 
                                CHANGEDBYID, 
                                DATEADDED, 
                                DATECHANGED)
                            values (
                                @APPUSERID, 
                                @DISPLAYNAME, 
                                @CHANGEAGENT, 
                                @CHANGEAGENT, 
                                @TIMESTAMP,
                                @TIMESTAMP);
                            
                end