Exception Handling in SqlServer Trigger to allow base table transaction to commit but handle the error

In Oracle World this is easy:

CREATE OR REPLACE TRIGGER myorcltrig
AFTER INSERT OR UPDATE
ON mytable
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
error_code NUMBER;
error_message VARCHAR2(2000);
BEGIN
-- make a copy of two fields into a new table on insert and updates
INSERT INTO mynewtable(myfield1, myfield2)
VALUES(:NEW.fieldA,:NEW.fieldB);
EXCEPTION WHEN OTHERS THEN -- any issue, let the insert into mytable occur and log an error to another table
error_code :=SQLCODE;
error_message :=SQLERRM;
INSERT INTO myerrortable(myerrcode, myerrmsg, myerrdt)
VALUES(error_code, error_message, SYSDATE);
END;

The trick turned out to be used a SAVE TRANSACTION with XACT_ABORT OFF

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER TRIGGER [dbo].[mysqltrig]

ON [dbo].[mytable]

AFTER INSERT,UPDATE

BEGIN

SET NOCOUNT ON;

SET XACT_ABORT OFF;

SAVE TRANSACTION appInsert;

BEGIN TRY

INSERT INTO [dbo].[mynewtable] ( [myfield1],[myfield2] )

SELECTi.fieldA, i.fieldB

FROM inserted i;

END TRY

BEGIN CATCH

DECLARE @myErrorNumber int, @myErrorSeverity int, @myErrorState int, @myErrorLine int,

@myErrorProcedure varchar(4000), @myErrorMessage varchar(4000)

SELECT

@myErrorNumber = ERROR_NUMBER(),

@myErrorSeverity = ERROR_SEVERITY(),

@myErrorState = ERROR_STATE(),

@myErrorProcedure = ERROR_PROCEDURE(),

@myErrorLine = ERROR_LINE(),

@myErrorMessage = ERROR_MESSAGE();

ROLLBACK TRANSACTION appInsert;

INSERT INTO myerrortable ( myerrcode, myerrmsg, myerrdt )

SELECT CAST(@myErrorNumber as VARCHAR),

CAST(@myErrorMessage as VARCHAR),

SYSDATETIME()

END CATCH

END

Here is the sample code to build the table objects

/****** Object:  Table [dbo].[mytable]    Script Date: 11/25/2011 10:31:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[mytable](
	[fieldA] [varchar](50) NULL,
	[fieldB] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


/****** Object:  Table [dbo].[mynewtable]    Script Date: 11/25/2011 10:35:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[mynewtable](
	[myfield1] [varchar](50) NULL,
	[myfield2] [varchar](50) NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


/****** Object:  Table [dbo].[myerrortable]    Script Date: 11/25/2011 10:33:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[myerrortable](
	[myerrcode] [varchar](max) NULL,
	[myerrmsg] [varchar](max) NULL,
	[myerrdt] [datetime] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

I got an assist at Experts Exchange to come up with this solution. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_27464087.html#a37197268