First of all, you (very sadly) don't get to use CREATE OR REPLACE in SQL Server. You need to drop the object you're creating, then CREATE it. You can say FOR INSERT, FOR UPDATE or FOR DELETE to denote which DML operation the trigger applies to.

The Oracle :NEW pseudo-table is the INSERTED table in SQL Server. The concept is identical, but you don't need the colon before it; it's just available by name. It contains the incoming records being changed by the INSERT or UPDATE. Similarly, the other pseudo-table is DELETED. It contains the records being deleted by the DELETE. Don't ever name one of your tables INSERTED or DELETED. Yes, you can actually do this, but don't.

A good thin is that I've never suffered from the Oracle mutating exception in SQL Server, but then again that just might be because I try to avoid triggers because of the overhead and I don't even try to use the table being impacted in the first place when I do have to write them.

Of everything that's different, the thing I miss the most is not having CREATE OR REPLACE. It's the simple things in life that make us happy. 🙂

HTH