Thursday, March 29, 2012

Combining Text and value in Trigger

Hi,

I have written a trigger that emails a specified person.

I am trying to include a body of the email which comprises of the stock level and a warning. Pulling out my hair...Help

Code so far in the trigger is :

CREATE TRIGGER Warnings ON [dbo].[tbl_sql_cartridges_kh]
for update
AS
declare @.SL as int
declare @.SS as int
declare @.Msg as nvarchar(100)
set @.SL= (select stock_level from inserted)
set @.SS =(select cartridge_key from inserted)
set @.Msg = 'Print Cartridges Level Warning'
if @.SL < 3
begin

exec sp_send_cdontsmail 'Print-Cartridges','XXX@.XXXX.co.uk','Print Cartridges Level Warning',@.Msg
end

I would like the @.Msg to say something like Cartridge XXX stock level is YYY, where XXX and YYY are taken from the table after update. I can get the values, but cant put them in the MSG string..

Like @.msg & @.SL (SL being Stock Level)

Many Thanks

KenFirst problem you have is that you are treating the virtual tables as if they have only 1 row...inserted may have n rows, so

set @.SL= (select stock_level from inserted)

Would only return the last results...

Second, sending emails from a trigger is very messy. Why not just do it from a stored procedure? If all the code is isolated to sproc calls then you're golden. If you allow dynamic sql from code, then it's a problem...

As for the email, we a notus lotes so we're hosed here...|||This calls a stored procedure.

The trigger will only ever have 1 row as this Sql dbase has adreamweaver front end that only lets a singke line be updated.

I can grab any items that have been updated, I just cant combine them.

I have made sure all constraints are working..

It actually tells you @.SS will be cartridge HP045a for example and @.SL could 1.

I need the @.msg to say something like Cartridge HP045a stock level is now 1.

The Cdonts procedure is effective and uses SMTP and works well..

No comments:

Post a Comment