"Update" in Trigger not working

Hi,

I am trying to concatenate the columns (PrevEmp01, PrevEmp02, PrevEmp03, PrevEmp04, PrevEmp05) into column (ft) using trigger:

CREATE TRIGGER [tg_prevemp_ft_update] ON [tStaffDir_PrevEmp]
FOR INSERT, UPDATE
AS
UPDATE tStaffDir_PrevEmp SET ft = PrevEmp01 + ' ' + PrevEmp02 + ' ' + PrevEmp03 + ' ' + PrevEmp04 + ' ' + PrevEmp05

I would expect the (ft) column will be populated accordingly regardless if any of the columns are (Null).
But the Trigger will only work when all the 5 columns are populated. If one of the column is (Null), the (ft) column will be (Null) too.

Please advise. Many Thanks.

Try this:

CREATE TRIGGER [tg_prevemp_ft_update]ON [tStaffDir_PrevEmp]FOR INSERT,UPDATEASUPDATE tStaffDir_PrevEmpSET ft =ISNULL(PrevEmp01,'') +' ' +ISNULL(PrevEmp02,'') +' ' +ISNULL(PrevEmp03,'') +' ' +ISNULL(PrevEmp04,'') +' ' +ISNULL(PrevEmp05,'')

Good luck.

|||

Thanks! It worked.