SQL Server trigger : when a new row is inserted if column A is blank or null SET value to X

I am trying to create a SQL Server trigger so that when a new row is inserted in CONTACT, if column Contact_Type is blank '' or NULL, it should be set to PCON.

The trigger I have created doesn't work. I have not created any triggers before so I am well out of my depth, any help would be greatly appreciated.

I assume I am messing something up in my where clause in regards to the inserted table?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[EJ_ConTACT_TYPE_DefaultValue_INS] 
ON [dbo].[CONTACT] 
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
        
    DECLARE @defaultvalue varchar(6) = 'PCON'

    UPDATE [dbo].[CONTACT]
    SET Contact_Type = @defaultvalue
    FROM inserted i
    WHERE [dbo].[CONTACT].[Contact_Type] = i.Contact_Type
      AND (i.[Contact_Type] = '' OR i.[Contact_Type] = NULL)
END
GO

Solution

Personally, like I mention in the comments, I would simply create a default constraint:

ALTER TABLE dbo.Contact ADD CONSTRAINT df_Contact_ContactType DEFAULT 'PCON' FOR Contact_Type;

Then you simply omit the column in your INSERT and the column will have the value. For example:

INSERT INTO dbo.Contact (Column1, Column2, Column3)
VALUES('abc',1,'20200916');

This will cause the column Contact_Type to have the value 'PCON'.

As for why your trigger isn't working, one problem is because of your WHERE. [dbo].[CONTACT].[ConTACT_TYPE] = i.ConTACT_TYPE will never be true is the column ConTACT_TYPE (does your column really have that odd casing in it's name..?) if it's value is NULL; NULL is equal to nothing including NULL itself. This also applies to your clause OR [ConTACT_TYPE] = NULL. Finally, I doubt that that value is unique, so it'll update rows it shouldn't.

If you "must" do this in a TRIGGER (I suggest you don't), use the Primary Key for the JOIN. Also, don't use 3+ part naming for columns, as I discuss in my post 3+ part naming on Columns will be Deprecated. This results in the below:

CREATE TRIGGER [dbo].[EJ_Contact_Type_DefaultValue] ON [dbo].[contact]
AFTER INSERT
AS
    BEGIN
    SET NOCOUNT ON;
        
        --DECLARE @defaultvalue varchar(6) = 'PCON'

        UPDATE C
        SET Contact_type = 'PCON'
        FROM dbo.Contact C
             JOIN inserted i ON C.ID = i.ID
        WHERE i.Contact_type = ''
           OR i.Contact_type IS NULL;
END;
GO