TR_DESIGNATIONLEVELGOAL_DESIGNATIONGOAL_INSERT (Trigger)

                
                    CREATE trigger dbo.TR_DESIGNATIONLEVELGOAL_DESIGNATIONGOAL_INSERT 
                       on dbo.DESIGNATIONLEVELGOAL 
                       after insert NOT FOR REPLICATION
                    as
                    begin
                        set nocount on;

                        declare @DESIGNATIONGOALID uniqueidentifier;
                        set @DESIGNATIONGOALID = newid();

                        declare @DESIGNATIONID uniqueidentifier;
                        select @DESIGNATIONID = ID 
                        from dbo.DESIGNATION 
                        where DESIGNATIONLEVEL1ID = (select DESIGNATIONLEVELID from INSERTED) and
                            DESIGNATIONLEVEL2ID is null;
                            
                        if not @DESIGNATIONID is null 
                            insert into dbo.DESIGNATIONGOAL(
                                ID,
                                DESIGNATIONID,
                                DESIGNATIONLEVELGOALID,
                                GOAL,
                                ORGANIZATIONGOAL,
                                ORGANIZATIONEXCHANGERATEID,
                                BASECURRENCYID,
                                ADDEDBYID,
                                CHANGEDBYID, 
                                DATEADDED, 
                                DATECHANGED
                            )
                            select 
                                @designationGoalID,
                                @DESIGNATIONID,
                                ID,
                                GOAL,
                                ORGANIZATIONGOAL,
                                ORGANIZATIONEXCHANGERATEID,
                                BASECURRENCYID,
                                ADDEDBYID,
                                CHANGEDBYID,
                                getdate(),
                                getdate()

                            from inserted
                                                    
                        declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                        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
                            );
                            
                    end