How do I create a trigger that will update a column if inserted or updated row is not null?-CodePudding

Table is EmployeeTest Column EmployeeCode has a Unique Index where it can allow NULL but cannot be duplicate. It is varchar (16)

I want to update the EffectiveDate column

I want to create a trigger for whenever I insert or update a row in the table, if EmployeeCode is NULL, then do not update EffectiveDate.

If a row is created or updated and EmployeeCode is not null, I want the update trigger to set EffectiveDate to GETDATE()

I want it to update EffectiveDate just for the row being edited or inserted.

I cannot get this to work. I keep getting "incorrect syntax near the word 'EmployeeCode'

Can anyone assist me with this?

After Update, Insert
AS BEGIN

IF @@ROWCOUNT = 0 RETURN
SET NOCOUNT ON;

IF COLUMN EmployeeCode = NULL RETURN;

UPDATE EmployeeTest
Set EffectiveDate = GETDATE()
FROM EmployeeTest D
join inserted i on D.id = i.id

END

CodePudding user response：

You can simply update only the rows that have a non-null value:

update t set EffectiveDate = Getdate()
from inserted i join EmployeeTest t on t.id = i.id
where i.EmployeeCode is not null;

And remove the If column line.