• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 384
  • Last Modified:

Catching exceptions in SQL Server 2000 triggers

I have a For update trigger  "CheckStock" that calls a stored procedure "SendWarningMails" . [Code is shown at the end of my message.]

I want to do something like try...catch that incloses "SendWarningsEmail". So that if it causes any type if error the trigger completes its function successfully. How can I accomplish this in SQL Server 2000?

---------
Trigger
------------------------------------
ALTER   trigger CheckStock on MachineCards
for update
as
declare @MachineId as integer,@CardId as integer
declare @QBefor as integer,@QAfter as integer,@QMin as integer
select @QBefor=CurrentQuantity from deleted
select @QAfter=CurrentQuantity from inserted
select @QMin=MinQuantity,@MachineId=MachineId,@CardId=CardId from inserted
if @QAfter < @QBefor
begin
if @QAfter <= @QMin
begin
exec SendWarningMails @MachineId,@CardId,@QAfter
end
end
-----------------------------
Stored Procedure
----------------------
ALTER             proc SendWarningMails(@MachineId as smallint,@CardId as smallint,@Quantity as integer)
as
declare @EMail as varchar(50)
declare @MachineName as varchar(50)
declare @CardName as Varchar(50)
declare @myMessage as varchar(200)
declare @mySubject as varchar(200)
declare @to as varchar(300)
select @MachineName=cast(MachineId as varchar) + '(' + MachineLocation +')' from Machines where MachineId=@MachineId
select @CardName= CardNameE from Cards where CardId=@CardId
set @myMessage='Warning : The Card ' + @CardName + ' is below the minumum Quantity in Machine ' + @MachineName  + ' ,the Current Quantity =' + cast(@Quantity as varchar(20))
set @mySubject='Stock Warning ' + @MachineName
set @to=''
DECLARE MailsCursor CURSOR FOR
SELECT EMail
FROM MailsList
OPEN MailsCursor
FETCH NEXT FROM MailsCursor
into @EMail
WHILE @@FETCH_STATUS = 0
BEGIN
set @to= @to + @Email + ';'
FETCH NEXT FROM MailsCursor
into @EMail
END
IF @TO <> ''
BEGIN
set @to=SUBSTRING(@to,0,len(@to))
select @to
exec master..xp_Sendmail @recipients =@to,@message =@mymessage , @subject=@mySubject
END
CLOSE MailsCursor
DEALLOCATE MailsCursor
---------------------
0
f_asmaa
Asked:
f_asmaa
1 Solution
 
nloeberCommented:
There is a T-SQL variable called @@ERROR which gives you access to the error code, so something like this might work:

USE pubs
GO
DECLARE @del_error int, @ins_error int
-- Start a transaction.
BEGIN TRAN

-- Execute the DELETE statement.
DELETE authors
WHERE au_id = '409-56-7088'

-- Set a variable to the error value for
-- the DELETE statement.
SELECT @del_error = @@ERROR

-- Execute the INSERT statement.
INSERT authors
   VALUES('409-56-7008', 'Bennet', 'Abraham', '415 658-9932',
   '6223 Bateman St.', 'Berkeley', 'CA', '94705', 1)
-- Set a variable to the error value for
-- the INSERT statement.
SELECT @ins_error = @@ERROR

-- Test the error values.
IF @del_error = 0 AND @ins_error = 0
BEGIN
   -- Success. Commit the transaction.
   PRINT "The author information has been replaced"    
   COMMIT TRAN
END
ELSE
BEGIN
   -- An error occurred. Indicate which operation(s) failed
   -- and roll back the transaction.
   IF @del_error <> 0
      PRINT "An error occurred during execution of the DELETE
      statement."

   IF @ins_error <> 0
      PRINT "An error occurred during execution of the INSERT
      statement."

   ROLLBACK TRAN
END
GO


This should work for a regular transaction, it is taken from BOL the entry for the @@ERROR variable. Please let me know if it also works for a trigger.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now