DDL Trigger not working as expected SQL Server 2012
Your trigger has no problem at all (the only question I have is why do you open another transaction inside your trigger if all you do is 1 SELECT only?)

When you alter a table in such a way it has to be recreated (for example adding a column not at the end of the existing columns), the actions server takes are following (simplified):


create a new table called dbo.Tmp_old_tbl having the new
structure (note, it's not temporary table that lives in tempdb, it's
just a new table residing in your db)
insert all the data from your old table into the new one
EXECUTE sp_rename N'dbo.Tmp_old_tbl', N'old_tbl', 'OBJECT'


So that "temp table" that you saw created is in fact your new table, renamed from Tmp to your old_tbl