A temp table column not recognized in a SQL Server trigger

I want to insert a bulk of data into a temp table and process it then insert it into another table then drop the temp table.

I'm using this code

USE [fmctest]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[HOSPITAL_LOCATION_INSTEAD_OF_INSERT]
ON [dbo].[HOSPITAL_LOCATION] 
INSTEAD OF INSERT
AS 
BEGIN
SET NOCOUNT ON;
BEGIN
CREATE TABLE [dbo].[HOSPITAL_LOCATION_TEMP]
(
HOSPITAL_LOCATION_ID [varchar](max) NULL,
NAME [varchar](max) NULL,
DIVISION [varchar](max) NULL,
STOP_CODE_NUMBER [varchar](max) NULL,
INACTIVATE_DATE_YEAR [varchar](max) NULL,
INACTIVATE_DATE_MONTH [varchar](max) NULL,
INACTIVATE_DATE_DAY [varchar](max) NULL,
TYPE_OF_LOCATION [varchar](max) NULL,
) ON [PRIMARY]
END;
-- Insert statements for trigger here
INSERT INTO [dbo].[HOSPITAL_LOCATION_TEMP]
SELECT 
REPLACE(HOSPITAL_LOCATION_ID, '"', ''),
REPLACE(NAME, '"', ''),
REPLACE(DIVISION, '"', ''),
REPLACE(STOP_CODE_NUMBER, '"', ''),
REPLACE(INACTIVATE_DATE_YEAR, '"', ''),
REPLACE(INACTIVATE_DATE_MONTH, '"', ''),
REPLACE(INACTIVATE_DATE_DAY, '"', ''), 
REPLACE(ADMINISTER_INPATIENT_MEDS, '"', ''),
REPLACE(TYPE_OF_LOCATION, '"', '')
FROM 
INSERTED;
UPDATE [HOSPITAL_LOCATION_TEMP]
SET INACTIVATE_DATE_YEAR = '1900',
INACTIVATE_DATE_MONTH = '1',
INACTIVATE_DATE_DAY = '1'
WHERE INACTIVATE_DATE_YEAR = '';
INSERT INTO dbo.HOSPITAL_LOCATION
SELECT 
HOSPITAL_LOCATION_ID, NAME,
DIVISION, STOP_CODE_NUMBER,
CONCAT(INACTIVATE_DATE_YEAR, '-', INACTIVATE_DATE_MONTH, '-', INACTIVATE_DATE_DAY),
ADMINISTER_INPATIENT_MEDS,
TYPE_OF_LOCATION,
CONVERT(varchar,getdate(), 20)
FROM 
dbo.[HOSPITAL_LOCATION_TEMP];
DROP TABLE IF EXISTS dbo.[HOSPITAL_LOCATION_TEMP];
END

These are the errors I get:

> Msg 207, Level 16, State 1, Procedure HOSPITAL_LOCATION_INSTEAD_OF_INSERT, Line 38 [Batch Start Line 7]
> Invalid column name 'INACTIVATE_DATE_YEAR'.
>
> Msg 207, Level 16, State 1, Procedure HOSPITAL_LOCATION_INSTEAD_OF_INSERT, Line 39 [Batch Start Line 7]
> Invalid column name 'INACTIVATE_DATE_MONTH'.
>
> Msg 207, Level 16, State 1, Procedure HOSPITAL_LOCATION_INSTEAD_OF_INSERT, Line 40 [Batch Start Line 7]
> Invalid column name 'INACTIVATE_DATE_DAY'.

What did I miss ?

I tried to separate this trigger with 2 triggers instead of insert and insert trigger, but I want to make the schema simpler.

I modified your code based on the information of your post I had. The information was incomplete (because I did not have the structure of the HOSPITAL_LOCATION table and information filed insert).
But try not to use a trigger for anything

alter  TRIGGER [dbo].[HOSPITAL_LOCATION_INSTEAD_OF_INSERT]
ON [dbo].[HOSPITAL_LOCATION] 
INSTEAD OF INSERT
AS 
BEGIN
-- Insert statements for trigger here
with _HOSPITAL_LOCATION_TEMP as (
SELECT 
REPLACE(HOSPITAL_LOCATION_ID, '"', '') as HOSPITAL_LOCATION_ID,
REPLACE(NAME, '"', '') as NAME,
REPLACE(DIVISION, '"', '') as DIVISION,
REPLACE(STOP_CODE_NUMBER, '"', '') as STOP_CODE_NUMBER,
Case when REPLACE(INACTIVATE_DATE_YEAR, '"', '') = '' then '1900'
else   REPLACE(INACTIVATE_DATE_YEAR, '"', '') end as INACTIVATE_DATE_YEAR,
Case when  REPLACE(INACTIVATE_DATE_YEAR, '"', '') = '' then '1'
else REPLACE(INACTIVATE_DATE_MONTH, '"', '') end as INACTIVATE_DATE_MONTH,
Case when  REPLACE(INACTIVATE_DATE_YEAR, '"', '') = '' then '1' else  
REPLACE(INACTIVATE_DATE_DAY, '"', '') end as INACTIVATE_DATE_DAY, 
REPLACE(ADMINISTER_INPATIENT_MEDS, '"', '') as ADMINISTER_INPATIENT_MEDS,
REPLACE(TYPE_OF_LOCATION, '"', '') as TYPE_OF_LOCATION
FROM 
INSERTED
)
INSERT INTO dbo.HOSPITAL_LOCATION
(HOSPITAL_LOCATION_ID,NAME,DIVISION,
STOP_CODE_NUMBER,INACTIVATE_DATE_YEAR_INACTIVATE_DATE_MONTH_INACTIVATE_DATE_DAY
,ADMINISTER_INPATIENT_MEDS,TYPE_OF_LOCATION,createdate
)
SELECT 
HOSPITAL_LOCATION_ID
,NAME,
DIVISION,
STOP_CODE_NUMBER,
CONCAT(INACTIVATE_DATE_YEAR, '-', INACTIVATE_DATE_MONTH, '-', INACTIVATE_DATE_DAY),
ADMINISTER_INPATIENT_MEDS,
TYPE_OF_LOCATION,
CONVERT(varchar,getdate(), 20)
FROM 
_HOSPITAL_LOCATION_TEMP;
END

Disadvantages of the trigger

1.When you use BULK INSERT to insert data into a table, triggers are not fired unless you include the FIRE_TRIGGERS option in your bulk insert statement.

2.Triggers are difficult to locate unless you have proper documentation because they are invisible to the client.

3.Triggers add overhead to DML statements.

4.The problem of using triggers for audit purposes is that when triggers are enabled, they execute always regardless of the circumstances that caused the trigger to fire.

limitations on triggers:

1.A table can have a maximum of three triggers: one update trigger, one insert trigger, and one delete trigger.

2.Each trigger can apply to only one table. However, a single trigger can apply to all three user actions: update, insert, and delete.

3.You cannot create a trigger on a view or on a temporary table, though triggers can reference views or temporary tables.

4.The writetext statement will not activate insert or update triggers.

5.Although a truncate table statement is, in effect, like a delete without a where clause, because it removes all rows, it cannot fire a trigger, because individual row deletions are not logged.

6.You cannot create a trigger or build an index or a view on a temporary object (@object)

7.You cannot create triggers on system tables. If you try to create a trigger on a system table, Adaptive Server returns an error message and cancels the trigger.

8.You cannot use triggers that select from a text column or an image column of the inserted or deleted table.

9.If Component Integration Services is enabled, triggers have limited usefulness on proxy tables because you cannot examine the rows being inserted, updated, or deleted (via the inserted and deleted tables). You can create a trigger on a proxy table, and it can be invoked. However, deleted or inserted data is not written to the transaction log for proxy tables because the insert is passed to the remote server. Hence, the inserted and deleted tables, which are actually views to the transaction log, contain no data for proxy tables.