I'm not sure what to tell you. The code for the trigger is correct and SQL Server is performing as I would expect. It may be that the SEM is not behaving has you expect.
Using the SEM, I used the Open Table | Return All Rows option to open both tables. I then enter one row into Table1 and tab through to move off the row (which causes SEM to submit the new row).
The window that contains the rows from Table2 does not automatically reflect the changes since it's a snapshot of the data. I have to right-click on the window and select Run which causes SEM to resubmit the query to return all the rows. After doing so, the new row appears. This is the behavior that I would expect.
-Mike
>When I enter data in through an Access front end, I also get an error. I do not get the error without the insert function in the trigger. The fact that I get an error in the SEM indicates that the error in Access is not an Access error, but coming from SQL. Sergey does not seem to get the error in his test, but I get it on two different servers.