Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    Dorset UK
    Posts
    147

    Unanswered: 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

    Ken

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    First 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...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Location
    Dorset UK
    Posts
    147
    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..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •