November 25, 2011 at 11:39 am
I'm very familiar with Oracle and I'm trying to find the comparable code in SqlServer world.
Basically, I am putting a trigger on a base table 'mytable' that upon an insert or update to mytable, I copy two values to another table 'mynewtable'. I believe I understand how to handle that aspect but want to add error handling such that if the trigger logic fails to insert into 'mynewtable' for whatever reason, I still want the insert into the base table 'mytable' to commit regardless and write a message to another table 'myerrortable'
In oracle, my code would look like this:
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);
-- 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;
For testing, I set a not null constraint on myfield2 in mynewtable.
INSERT INTO [dbo].[mytable]
([fieldA]
,[fieldB])
VALUES
('TEST1'
,'DATA1')
works
INSERT INTO [dbo].[mytable]
([fieldA]
,[fieldB])
VALUES
('TEST2'
,NULL)
Gives an error, and does not write to the base table 'mytable' nor the errortable 'myerrortable'
Msg 3930, Level 16, State 1, Procedure mysqltrig, Line 17
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
The statement has been terminated.
My sample code below:
/****** 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
/****** Object: Trigger [dbo].[mysqltrig] Script Date: 11/25/2011 10:33:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[mysqltrig]
ON [dbo].[mytable]
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
INSERT INTO [dbo].[mynewtable] ( [myfield1],[myfield2] )
SELECTi.fieldA, i.fieldB
FROMinserted i
END TRY
BEGIN CATCH
INSERT INTO myerrortable ( myerrcode, myerrmsg, myerrdt )
SELECT CAST(ERROR_NUMBER()as VARCHAR),
CAST(ERROR_MESSAGE()as VARCHAR),
SYSDATETIME()
END CATCH
END
November 25, 2011 at 2:30 pm
Any constraints will be verified before your trigger ever gets control. Thus, you cannot handle those types of errors in a trigger.
In general, you use BEGIN TRY ... END TRY, BEGIN CATCH ... END CATCH to insure that an error in your INSERTs in the trigger won't cause a rollback of the main transaction:
CREATE TRIGGER trigger_name
ON table_name
AFTER INSERT
AS
BEGIN TRY
INSERT INTO other_table ( col1, col2, ...)
SELECT cola, colb, ...
FROM inserted
END TRY
BEGIN CATCH
--ignore the error, don't issue ROLLBACK, so main trans is allowed to finish
DECLARE @dummy int
SET @dummy = 1
END CATCH
GO
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 26, 2011 at 3:42 am
You need to use the SAVE TRANSACTION command. You need to use it twice if you also want to avoid an error in the insertion to mynewtable making the main transactio uncommitable, so that even when you fail to record the error the main transaction still commits. Pick sensible savepoint names, not the ones I've used below, and look at http://msdn.microsoft.com/en-us/library/ms188378%28v=SQL.100%29.aspx to understand what save and rollback do.
CREATE TRIGGER [dbo].[mysqltrig]
ON [dbo].[mytable]
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
SAVE TRANSACTION CallMeFred
BEGIN TRY
INSERT INTO [dbo].[mynewtable] ( [myfield1],[myfield2] )
SELECT i.fieldA, i.fieldB
FROM inserted i
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION CallMeFred
BEGIN TRY
SAVE TRANSACTION CallMeBill
INSERT INTO myerrortable ( myerrcode, myerrmsg, myerrdt )
SELECT CAST(ERROR_NUMBER()as VARCHAR),
CAST(ERROR_MESSAGE()as VARCHAR),
SYSDATETIME()
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION CallMeBill
END CATCH
END CATCH
END
Tom
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply