Adding a moderator/interaction term to the model changes the interpretation of the main effects and potentially their statistical significance. This is normal/expected and not a problem per se. Check the literature on moderated regression, for example, Aiken and West (1991) or the relevant chapter in Cohen, Cohen, West and Aiken's (2003) general regression book. For a proper interpretation of the main effects, centering the predictors is often key as described in that same literature.

A plot of the predicted values (with confidence intervals) will often be insightful and helpful. I suggest for the predictions you keep any non-involved variables at their (unchanging) mean value and then use characteristic values (e.g. deciles or quartiles ) for the two variables involved in the interaction. You can the plot the predicted values against one of the variables grouping on the other variable. We have built a software tool to do this in MLwiN which works by simulation. If you have categorical variables, involved in the interaction, you can use all the categories separately to make the prediction.

see Chapter 2 of Article Manual supplement for MLwiN Version 2.14

(the simulations are for more complex models like logits and probits and population average versus subject specific models.).

and also see Margins plot in Stata

https://www.stata.com/stata12/margins-plots/

https://stats.idre.ucla.edu/stata/faq/how-can-i-use-the-margins-command-to-understand-multiple-interactions-in-regression-and-anova-stata-11/

https://www.stata.com/stata-news/news32-1/spotlight/

@Jiang I agree with @Christian that there's no assumption of having significant relationship between IV and DV for checking the moderation as it's meant to check the interaction effect of the moderator on the relationship between IV and DV and the same might be significant at high/low level of the moderator and vice versa.

Kelvyn Jones Really thanks!! And I still wonder that if I have a significant interaction effect, but the simple slope effect is not significant (both high SD and low SD), how should I deal with that?

The usual rule is that you need both the simple slopes and the interaction to understand what is going on; that is you should not drop the simple slopes. that is main effects. Have a look at https://www.theanalysisfactor.com/interactions-main-effects-not-significant/

and see case 3 in this and 'concluding remarks' in the following:

https://stats.idre.ucla.edu/stata/faq/what-happens-if-you-omit-the-main-effect-in-a-regression-model-with-an-interaction/

The reality is you need to appreciate all 4 parameters simultaneously - the estimated intercept, both main effects and slope of the interaction . Personally I find this very hard to do from a table of results whereas the graph does this elegantly and clearly. Look at the plot first without CIs and then with CIs. You will see in the websites that I point to, they make their points about the coefficients through graphs.