The main issue is that you are using

INSERT INTO TABLE(Fields)

VALUES(Values)

when you should be using

INSERT INTO TABLE(Fields)

SELECT expressions

FROM INSERTED

I would also recommend using TRY...CATCH blocks.

There are also undefined variables in your trigger. You CANNOT directly pass variables from your main insert to the trigger. There are ways to get around that, but the best approach is to only use values from the INSERTED records.

You may want to have separate INSERT and UPDATE triggers so that you don't need to test whether there are any records in the DELETED table.

I also hate the RETURN in your code. It should be

IF @@ROWCOUNT > 0

BEGIN

<rest of code...>

END

But that's not even necessary, because there won't be any records in the INSERTED table if there were no records inserted/updated.

Drew