The trigger fails on its normal instructions, null values, etc
::Use an Instead of insert trigger start with
select into #inserted from Inserted
declare @query nvarchar(max),@iretval int
::You can bypass the fail by building each instruction into a query
set @query=N'update.... etc '
::it is also a good idea to check query before the sp_executesql, because
::if the query is null then the iretval will be 0 anyway i.e.
if @Query is null
exec @Iretval=sp_executesql @query [[parmameters ] outvalue]
if @iretval>0 goto Last
:: your insert should carry a flag or something to show if the trigger worked allowing subsequent processing that the trigger did not do assuming this is required
if @iretval=0 update #inserted set flag=ok
insert into table select * from #inserted
::note. you must check each statement, and if you have to retrieve values from other tables check the values are valid and not isnull.
if anywants an example. email me email@example.com