You are correct; since this trigger is an
INSTEAD OF INSERT trigger that simply does an insert of inserted data into the table the trigger is defined on, the only outcome will be to insert the data as if the trigger did not exist.
As with any and all changes, before you delete this trigger in production, ensure you test the change in your development/test environment.
Interestingly, if the table includes an
IDENTITY column, you won't be able to create the trigger. Consider this repro, which runs on SQL Server 2016+:
DROP TABLE IF EXISTS dbo.insert_test;
CREATE TABLE dbo.insert_test
i int NOT NULL
PRIMARY KEY CLUSTERED
, d varchar(100) NOT NULL
CREATE TRIGGER insert_test_trigger ON dbo.insert_test
INSTEAD OF INSERT
SET NOCOUNT ON;
INSERT INTO dbo.insert_test (i, d)
SELECT i, d
INSERT INTO dbo.insert_test (d)
Msg 544, Level 16, State 1, Procedure insert_test_trigger, Line 8 [Batch Start Line 24]
Cannot insert explicit value for identity column in table 'insert_test' when IDENTITY_INSERT is set to OFF.