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,@Machine Id=Machine Id,@CardId =CardId from inserted
if @QAfter < @QBefor
begin
if @QAfter <= @QMin
begin
exec SendWarningMails @MachineId,@CardId,@QAfter
end
end
-------------------------- ---
Stored Procedure
----------------------
ALTER proc SendWarningMails(@MachineI d 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(MachineI d 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(@t o))
select @to
exec master..xp_Sendmail @recipients =@to,@message =@mymessage , @subject=@mySubject
END
CLOSE MailsCursor
DEALLOCATE MailsCursor
---------------------
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,@Machine
if @QAfter < @QBefor
begin
if @QAfter <= @QMin
begin
exec SendWarningMails @MachineId,@CardId,@QAfter
end
end
--------------------------
Stored Procedure
----------------------
ALTER proc SendWarningMails(@MachineI
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(MachineI
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(@t
select @to
exec master..xp_Sendmail @recipients =@to,@message =@mymessage , @subject=@mySubject
END
CLOSE MailsCursor
DEALLOCATE MailsCursor
---------------------
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.