Catch and Ignore existing RaiseError Message

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Catch and Ignore existing RaiseError Message

Author

Topic

socnob
Starting Member

10 Posts

Posted - 2007-04-12 : 01:07:48

Question: How can I catch, ignore, and continue processing of a stored procedure when a particular RaiseError is raised via a trigger?

Situation: I have a stored procedure that is trying to update a table's column with a value that does not exist in the pseudo foreign key column's table (yeah I know "Why would I want to do this?" - some "dirty" data was let into some tables and I am allowing the user to keep/move the "dirty" data).

The following is the stored procedure where the update statement is failing, where I'm getting the error, and execution is stopping (. . . - means that other code is here, but not important to my question):
============================
CREATE PROCEDURE My_MergeData
@tablename varchar(26),
@keepLink int,
@throwLink int
AS
. . .

UPDATE client SET cliTypCd = (SELECT cliTypCd FROM client T WHERE T.link = @throwLink) WHERE link = @keepLink
. . .

============================

The following error is returned via the "T_U_Client" Trigger when the above stored proc is ran and the value to be copied over is not in the "cliTyp" table's "cd" column:
=============================
Msg 30002, Level 16, State 1, Procedure T_U_Client, Line 528
Cannot update child in "client" as its Foreign Key does not exist in "cliTyp".
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
=============================

"client" table definition:
============================
CREATE TABLE [client] (
[Link] [int] IDENTITY (1, 1) NOT NULL ,
[fname] [char] (30) NOT NULL DEFAULT (''),
[lname] [char] (30) NOT NULL DEFAULT (''),
[phone] [char] (12) NOT NULL DEFAULT (''),
[cliTypCd] [char] (2) NOT NULL DEFAULT ('')
) ON [PRIMARY]
GO
============================

"cliTyp" table definition:
=============================
CREATE TABLE [cliTyp] (
[cd] [char] (2) NOT NULL ,
[txt] [char] (35) NOT NULL DEFAULT (''),
[link] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
=============================

"T_U_Client" Trigger code:
=============================
ALTER TRIGGER DBO.T_U_Client ON DBO.client FOR UPDATE AS

BEGIN
DECLARE
@row_count INT,
@null_row_count INT,
@blank_row_count INT,
@error_number INT,
@error_message VARCHAR(255)

SELECT @row_count = @@rowcount
IF @row_count = 0
RETURN
SET NOCOUNT ON
. . .
IF UPDATE(cliTypCd)
BEGIN
SELECT @blank_row_count =
(
SELECT COUNT(*)
FROM inserted
WHERE cliTypCd= ''
)
IF @blank_row_count != @row_count
IF (
SELECT COUNT(*)
FROM cliTyp p, inserted i
WHERE p.cd = i.cliTypCd
)
!= @row_count - @blank_row_count
BEGIN
SELECT @error_number=30002,
@error_message='Cannot update child in "client" as its Foreign Key does not exist in "cliTyp".'
GOTO error
END
END
. . .

RETURN

/* Error Handling */
error:
RAISERROR @error_number @error_message
ROLLBACK TRANSACTION
END
=============================

Basically, I'd like to put some kind of check around the "Update client WHERE..." code and if the RaiseError message occurs from this trigger to ignore it and continue processing the rest of the stored procedure.
Ideally, I'd like to turn off the trigger just for this stored procedure. Though I can't, because others could be using the system and I still want this trigger/checking enabled for them. As far as I know there is no way of turning off a trigger for a particular process/stored procedure and not have it off for all other processes, correct?

Thanks for any help!

socnob
Starting Member

10 Posts

Posted - 2007-04-13 : 16:03:21

Bueller?....... Bueller?.......Bueller?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-04-17 : 16:42:35

Something like:

DECLARE @Error INT

-- DO Stuff
UPDATE client SET cliTypCd = (SELECT cliTypCd FROM client T WHERE T.link = @throwLink) WHERE link = @keepLink
SET @Error = @@ERROR

IF @Error <> 0 AND @Error <> 30002
BEGIN
	-- Handle Error
END
-- Else Ignore

Although, the correct answer is to code defensivly and check for the violation before you even call the update so the trigger does not need to fire and throw an error.

Cheers,

-Ryan

socnob
Starting Member

10 Posts

Posted - 2007-04-20 : 17:44:32

Ryan,

Thank you for the suggestion. Unfortunately, that trigger contains checks on a lot of columns in that table (I think like 20) and this is not the only table/column combination that is being updated and each of the other tables (4 total) have their own triggers that check validity of values. Which means, for every column update trigger error that I want to ignore, I would have to essentially copy a bunch of code from each trigger over to my stored proc that is performing the update.

If the client was running SQL Server 2005, I think I could just use the Try...Catch, but SQL Server 2000 does not have this.

Since there does not seem to be a way to catch and ignore a fatal raiserror (or not the way my existing triggers call raiseerror) directly with SQL stored procedures, I ended up going another route:
I created a COM class which contains a method which accepts SQL statements as a parameter and executes them within a "try" block and then allow my stored proc to determine what to do when the error occurs (this allows processing within the stored proc to continue and not just bomb out).
Below is the flow:
1.) Stored Proc: Construct the UPDATE sql statement to run.
2.) Stored Proc: Use the "sp_OACreate" extended stored procedure to create an instance of the COM class to use.
3.) Stored Proc: Use the "sp_OAMethod" extended stored procedure to call the sql update method of the newly created COM class (passing in the sql statement, the server name that this stored proc is running on, and DB name the DB this stored proc is running on).
4.) COM class: COM class object connects to the DB/Server that just created the object and performs the UPDATE statement.
5.) Stored Proc: If an error occurs during the the creation of the COM class object or during the method call to that object, get the error info. on that object via the "sp_OAGetErrorInfo" extended stored proc and:
a.) If the "description" begins with any of the prefixes of existing triggers that I want to ignore, ignore it and continue processing as if no error occurred.
ELSE...
a.) Print out: What caused the error (creation or method call).
b.) Print out: The hexidecimal value of the error value that was returned from the "sp_OACreate" or "sp_OAMethod" stored proc call (get via sp_OAGetErrorInfo)
c.) Print out: The "source" value of the error (get via sp_OAGetErrorInfo).
d.) Print out: The "description" value of the error (get via sp_OAGetErrorInfo).

This allows me to ignore those triggers that are raising fatal errors that I don't care about and this seems to be working well for the 1st bunch of calls to this stored proc. After a bunch of calls to this stored proc, I start getting the following errror:
=================
OLE Automation Error Information
HRESULT_mt: 0x0000275d
Source_mt: ODSOLE Extended Procedure
Description:
=================
(Technically, the "Description" is NULL, but I did an "isnull(@description, '')" to it, otherwise, this line doesn't print out at all).

Conclusion:
I think my orginal question of "can I catch and ignore fatal raiseerrors" directly in SQL has been answered and the answer is "No".

Also, it looks like I figued out a way to catch/ignore fatal errors by creating the COM class object to do the actual execution of the SQL statements, BUT let the SQL code determine what to do for those errors that were raised.

I have opened a seperate entry for the issue I am now getting with the creation of the COM class object called "sp_OACreate call causing 10077 Error?" (http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=82456) since I feel that is its own issue in itself.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources