TR_DESIGNATIONLEVELGOAL_DESIGNATIONGOAL_UPDATE (Trigger)

                
                    CREATE trigger dbo.TR_DESIGNATIONLEVELGOAL_DESIGNATIONGOAL_UPDATE 
                       on dbo.DESIGNATIONLEVELGOAL 
                       after update NOT FOR REPLICATION
                    as
                    begin
                        set nocount on;

                        declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
                        if update(GOAL)
                            update
                                dbo.DESIGNATIONLEVELGOAL
                            set
                                DESIGNATIONLEVELGOAL.ORGANIZATIONGOAL = DESIGNATIONLEVELGOAL.GOAL,
                                DESIGNATIONLEVELGOAL.BASECURRENCYID = @ORGANIZATIONCURRENCYID,
                                DESIGNATIONLEVELGOAL.CHANGEDBYID = DESIGNATIONLEVELGOAL.CHANGEDBYID,
                                DESIGNATIONLEVELGOAL.DATECHANGED = DESIGNATIONLEVELGOAL.DATECHANGED
                            from
                                inserted
                            inner join
                                dbo.DESIGNATIONLEVELGOAL on inserted.ID = DESIGNATIONLEVELGOAL.ID
                            where
                                DESIGNATIONLEVELGOAL.ORGANIZATIONEXCHANGERATEID is null
                                and (DESIGNATIONLEVELGOAL.BASECURRENCYID is null or DESIGNATIONLEVELGOAL.BASECURRENCYID = @ORGANIZATIONCURRENCYID)
                                and
                                (
                                    DESIGNATIONLEVELGOAL.ORGANIZATIONGOAL <> DESIGNATIONLEVELGOAL.GOAL
                                    or DESIGNATIONLEVELGOAL.GOAL = 0
                                );
                            
                        declare @DESIGNATIONID uniqueidentifier;
                        select
                            @DESIGNATIONID = ID 
                        from 
                            dbo.DESIGNATION 
                        where 
                            DESIGNATIONLEVEL1ID = (select top 1 DESIGNATIONLEVELID 
                                                        from DELETED)
                            and    DESIGNATIONLEVEL2ID is null;
                            
                        if not @DESIGNATIONID is null 
                        begin 
                            
                            declare @DESIGNATIONGOALTABLE table(ID uniqueidentifier);
                            
                            insert into @DESIGNATIONGOALTABLE
                            select 
                                ID 
                            from 
                                dbo.DESIGNATIONGOAL 
                            where 
                                DESIGNATIONID = @DESIGNATIONID and DESIGNATIONLEVELGOALID in (select ID from DELETED);
                            
                            if (select count(ID) from @DESIGNATIONGOALTABLE) = 0
                            begin
                                declare @CURRENTDATE datetime;
                                set @CURRENTDATE = getdate();
                                
                                insert into dbo.DESIGNATIONGOAL
                                    (DESIGNATIONID,DESIGNATIONLEVELGOALID,GOAL,ORGANIZATIONGOAL,ORGANIZATIONEXCHANGERATEID,BASECURRENCYID,ADDEDBYID,CHANGEDBYID, DATEADDED, DATECHANGED)
                                SELECT
                                    @DESIGNATIONID, ID, GOAL,ORGANIZATIONGOAL,ORGANIZATIONEXCHANGERATEID,BASECURRENCYID,ADDEDBYID,CHANGEDBYID, @CURRENTDATE, @CURRENTDATE
                                FROM INSERTED
                            end
                            else
                                update dbo.DESIGNATIONGOAL 
                                    set DESIGNATIONGOAL.GOAL = INSERTED.GOAL,
                                        DESIGNATIONGOAL.ORGANIZATIONGOAL = INSERTED.ORGANIZATIONGOAL,
                                        DESIGNATIONGOAL.ORGANIZATIONEXCHANGERATEID = INSERTED.ORGANIZATIONEXCHANGERATEID,
                                        DESIGNATIONGOAL.BASECURRENCYID = INSERTED.BASECURRENCYID,
                                        DESIGNATIONGOAL.CHANGEDBYID = INSERTED.CHANGEDBYID,
                                        DESIGNATIONGOAL.DATECHANGED = getdate()
                                    from 
                                        INSERTED
                                    join
                                        dbo.DESIGNATIONGOAL on DESIGNATIONGOAL.DESIGNATIONLEVELGOALID = INSERTED.ID
                                    where 
                                        DESIGNATIONGOAL.ID in (select ID from @DESIGNATIONGOALTABLE)
                                        
                        end
                    end