Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Error handling in trigger

Posted on 2003-11-05
15
Medium Priority
?
1,087 Views
Last Modified: 2008-02-01
I have a trigger on a table that calls an external dll to pass a message (string) to MQSeries.  A question has arisen regarding transactional consistency.  My trigger code has logic to trap and handle any errors returned by the dll.  

I'm worried that records are being rolled back out of the table if any of the dll calls fail.  Does the code provided appear to handle any errors and prevent the record from being rolled back?  If I haven't provided enough information let me know.

Thanks.

Joe

0
Comment
Question by:Joeharvey
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
15 Comments
 
LVL 3

Author Comment

by:Joeharvey
ID: 9687263
CREATE TRIGGER tI_Send_Events ON dbo.Events_Send
FOR INSERT
AS

declare @msg_id int
declare @msg varchar(39)
DECLARE @object int
DECLARE @return int
DECLARE @step varchar(20)

select @msg_id = msg_id from inserted
select @msg = msg from inserted

--Create object
EXEC @return = sp_OACreate 'SQLMQS.Message_Sender', @object OUT
IF @return <> 0
BEGIN
      SELECT @step = 'Create object'
      GOTO error
      RETURN
END

EXEC @return = sp_OASetProperty @object, 'QueueName', 'TEST.QUEUE'
IF @return <> 0
BEGIN
      SELECT @step = 'Set MQ properties -- QueueName'
      GOTO error
      RETURN
END

--Send Message
EXEC @return = sp_OAMethod @object, 'Send', NULL, @msg
if @msg = '000000000000000000000000000000000000000'
BEGIN
      SELECT @return = 0
      GOTO destroy
END
IF @return <> 0
BEGIN
      SELECT @step = 'Send Message'
      GOTO error
      RETURN
END

destroy:
--Destroy object
EXEC sp_OADestroy @object

if @return = 0
   update Events_Send set msg_sent = 1 where msg_id = @msg_id


RETURN


error:
      EXEC sp_displayoaerrorinfo @object, @return
      RAISERROR 60005 @step


0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9687441
2 lines below are incorrect for batch insert (A SQL that insert more than one row at once. In that case trigger will fire only once, but insert table wil have more than one row)
select @msg_id = msg_id from inserted
select @msg = msg from inserted

You will need a cursor to do what you need to do.

I do not see any other problems.
0
 
LVL 3

Author Comment

by:Joeharvey
ID: 9687468
--In that case trigger will fire only once, but insert table wil have more than one row

??
Why wouldn't the trigger fire for each row inserted?

jm
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9687483
Here is an example,

DECLARE table_cursor CURSOR FOR
select msg_id, msg from inserted

Declare @msg_id int,
        @msg varchar(1000)


OPEN table_cursor

FETCH NEXT FROM table_cursor
INTO @msg_id, @msg

WHILE @@FETCH_STATUS = 0
BEGIN
     
   /*
   Do whatever you want to do with msg_id and msg here
   */  

   FETCH NEXT FROM table_cursor
   INTO @msg_id, @msg
END

CLOSE table_cursor
DEALLOCATE table_cursor
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9687511
JoeHarvey,

That is how MS has designed it. Trigger will fire only once.

By the way,

Error will be raised sp_OACreate fails. But that is not what you want right!!

EXEC @return = sp_OACreate 'SQLMQS.Message_Sender', @object OUT
IF @return <> 0
BEGIN
     SELECT @step = 'Create object'
     GOTO error
     RETURN
END
0
 
LVL 3

Author Comment

by:Joeharvey
ID: 9687547
---That is how MS has designed it. Trigger will fire only once.
Once per each row entered into the table...regardless of whether or not its in a batch, right?


As far as the GOTO error statement, will the RAISEERROR actually roll back the transaction, even though there is no ROLLBACK statement in the trigger?
0
 
LVL 15

Assisted Solution

by:namasi_navaretnam
namasi_navaretnam earned 1200 total points
ID: 9687668
If you enter one row at a time, everything will be fine.

But if you do something like,

Insert into MyTable  (col1,col2)
select col1, col2
from AnotherTable
where status = 1

If sql
select col1, col2
from AnotherTable
where status = 1
returns more than row then trigger will fire only once and inserted table wil have more than one row.

Lets goto the other  problem

This wil be updated only if @retun = 0, plus after error you issue a goto statement that goes to error label and this update statement will not be executed
if @return = 0
   update Events_Send set msg_sent = 1 where msg_id = @msg_id

If you want the update statement to execute all the time then you may want to place the update statement under error routine.

Example

Error:

update Events_Send set msg_sent = 1 where msg_id = @msg_id

EXEC sp_displayoaerrorinfo @object, @return
     RAISERROR 60005 @step

If that did not work try,

Error:
begin tran
update Events_Send set msg_sent = 1 where msg_id = @msg_id
commit tran

EXEC sp_displayoaerrorinfo @object, @return
     RAISERROR 60005 @step
0
 
LVL 26

Assisted Solution

by:Hilaire
Hilaire earned 400 total points
ID: 9687722
Hi JoeHarvey,

insert into mytable(col1,col2,...) values (val1, val2,...) is a single-row insert

insert into mytable select col1, col2, ... from mytable2 where ...
is a batch insert

bcp in of a multi-line flat file is a batch insert

a classical trigger after insert will fire
- after each and every single-row insert
- once at the end of a batch-mode insert

What namasi said is that triggers fail to work properly when the virtual table inserted contains several rows (which is the case in batch inserts) and when your triggers has statements like
select @myvar = mycol from inserted
(you get 1 value out of ????)

If you want to deal with this kind of situations, you have to write your triggers in order to
- execute set-based sql statements
- have a "for each row" approach using a cursor

Maybe that's not relevant in your case.

I did exactly the same thing a few years ago (connecting MQ Series with a DB) with a sybase DB (pretty close to SQL server, same T-sql, ...)

My advice would be to make a custom xp_* function
with the msg itself as an input parameter,
the target queue name if need be,
and an output parameter to test success/not

sp_OA_* procedures are quite difficult to deal with IMHO, since you have to manage 2 transactions (an SQL transaction, and the ActiveX-related transactions) instead of one.

Trapping errors becomes a nightmare when untrappable errors are raised.
My application was sending SWIFT (bank) messages, so trapping errors was critical.

That's why I would suggest enclosing the whole message logic in a single extented stored procedure
(see xp_hello and sample codes in BOL)

HTH

Hilaire







0
 
LVL 3

Author Comment

by:Joeharvey
ID: 9687726
I don't want the update to be executed all the time...only if the Return value is 0 (the message was successfully sent to the queue). My concern is that the RAISEERROR is potentially rolling back the row from the table and the data is being lost.
0
 
LVL 3

Author Comment

by:Joeharvey
ID: 9687761
Thanks for the comments folks.  What I ultimately want to accomplish is to have the records stored in the table regardless of whether or not they make it to the queue.  If they are successful, update the flag(msg_sent) to a '1'.  Otherwise, leave it at a zero and continue.
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9687782
Unless you 'ROLLBACK TRANSACTION' yourself Raiserror will not rollback the update.

Here is a simple test that I ran

CREATE  PROCEDURE namasi

AS
BEGIN
update jobs
set job_desc = 'TEST'
where job_id = 14

RAISERROR('Test', 16, 1)

END

Update was commited.

If you want take control over the update issue begin tran and then commit tran.

HTH
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9687805
Try this. Under Error label.

Error:

If @return = 0
BEGIN
   update Events_Send set msg_sent = 1 where msg_id = @msg_id
END
ELSE
BEGIN
   update Events_Send set msg_sent = 1 where msg_id = @msg_id
END

EXEC sp_displayoaerrorinfo @object, @return
     RAISERROR 60005 @step
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9688049
Joe,

This is kind of makes sense. When 2 or more rows were inserted you would have seen only one row updated in Events_Sent Table.
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 400 total points
ID: 9688435
don't use a cursor within the trigger do SOMETHING LIKE this to cater for the
potential multi insert scenarios

however you'll then need to decide how to recover from an individual msg send failure....
...

CREATE TRIGGER tI_Send_Events ON dbo.Events_Send
FOR INSERT
AS

declare @msg_id int
declare @msg varchar(39)
DECLARE @object int
DECLARE @return int
DECLARE @step varchar(20)



--Create object
EXEC @return = sp_OACreate 'SQLMQS.Message_Sender', @object OUT
IF @return <> 0
BEGIN
    SELECT @step = 'Create object'
    GOTO error
    RETURN
END

EXEC @return = sp_OASetProperty @object, 'QueueName', 'TEST.QUEUE'
IF @return <> 0
BEGIN
    SELECT @step = 'Set MQ properties -- QueueName'
    GOTO error
    RETURN
END

While Exists (select Msg_id from inserted where Msg_id > @msg_id)
Begin
select @msg_id=Msg_id,@msg=msg from inserted where Msg_id > @msg_id

--Send Message
EXEC @return = sp_OAMethod @object, 'Send', NULL, @msg
if @msg = '000000000000000000000000000000000000000'
BEGIN
    SELECT @return = 0
    GOTO EndofWhile
END
IF @return <> 0
BEGIN
    SELECT @step = 'Send Message'
    GOTO error1
    RETURN
END

error1:
    EXEC sp_displayoaerrorinfo @object, @return
    RAISERROR 60005 @step

ENDofWhile:

End


destroy:
--Destroy object
EXEC sp_OADestroy @object

if @return = 0
  update Events_Send set msg_sent = 1 where msg_id = @msg_id


RETURN


error:
    EXEC sp_displayoaerrorinfo @object, @return
    RAISERROR 60005 @step


 
0
 
LVL 3

Author Comment

by:Joeharvey
ID: 9694211
Thanks everyone for your input.  Much appreciated.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question