How do I rewrite the below Oracle trigger so it will work on SQL Server?
The bad news is that SQL Server doesn't support FOR EACH ROW triggers, so you'd have to take extra care to convert the trigger code.

The good news is that this appears to just be doing what can be achieved with an IDENTITY column in SQL Server, and you won't need the trigger at all.

In your CREATE TABLE COURSE_DELEGATES... statement, declare the COURSE_DELE_ID column something like this (use a more appropriate data type if it isn't int):

CREATE TABLE COURSE_DELEGATES (
    COURSE_DELE_ID int NOT NULL IDENTITY(1,1),
    ....
)


Then any rows inserted in COURSE_DELEGATES will get an auto-generated value for COURSE_DELE_ID, starting with 1, and incrementing by 1 for each new row.