Solved

Error handling in trigger

Posted on 2003-11-05
15
1,077 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
15 Comments
 
LVL 3

Author Comment

by:Joeharvey
Comment Utility
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
Comment Utility
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
Comment Utility
--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
 
LVL 15

Expert Comment

by:namasi_navaretnam
Comment Utility
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
Comment Utility
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
Comment Utility
---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 300 total points
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 26

Assisted Solution

by:Hilaire
Hilaire earned 100 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 100 total points
Comment Utility
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
Comment Utility
Thanks everyone for your input.  Much appreciated.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now