Link to home
Start Free TrialLog in
Avatar of f_asmaa
f_asmaa

asked on

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
---------------------
ASKER CERTIFIED SOLUTION
Avatar of nloeber
nloeber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial