First problem with your sample trigger is that you are trying to insert into a table that doesn't exist - you create
#cmmsGlobal
but then try to use
#tmp
Once the trigger has completed then any temporary tables created by it go out of scope and are not accessible to the session that instigated the trigger - check out the documentation for
CREATE[
^]
It doesn't really make sense to use a temporary table in this way.
Either create an audit table on your main database, or you could create an
actual table on
tempdb
e.g.
CREATE TABLE [tempdb].[dbo].tmp
...