Post reply

Help with variable in a Trigger

  • February 29, 2008 at 2:52 am

    #71586

    I have created a insert trigger to send an e-mail.

    I have set a variable as the subject, but i would like to have 2 variables and a bit of text as the subject instead.

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    go

    ALTER TRIGGER [New Registrations] ON [dbo].[Registration]

    for INSERT

    AS

    declare @NewUserFN nvarchar(50)

    declare @NewUserSN nvarchar(50)

    set @NewUserFN = (select top 1 FirstName from registration order by ID desc)

    set @NewUserSN = (select top 1 Surname from registration order by ID desc)

    EXEC master..xp_sendmail

    @recipients = 'a@b.com',

    @message = 'A new Website registration has been added',

    @Subject = @NewUserSN

    I would like the @Subject to be "@NewUserFN @NewUserSN has registered on the website"

    How can i insert 2 variables and the text into the @Subject variable ?

  • February 29, 2008 at 3:14 am

    #783648

    you could do this:

    declare @subject nvarchar(510)

    set @subject = @NewUserFN + ' ' + @NewUserSN + ' has registered on the website'

    Then, when you call xp_sendmail you do this:

    EXEC master..xp_sendmail

    @recipients = 'a@b.com',

    @message = 'A new Website registration has been added',

    @Subject = @subject

    Incidentally, when you assign @NewUserFN and @NewUserSN, you could do that with one statement, like so:

    select top 1 @NewUserFN = FirstName, @NewUserSN = Surname from registration order by ID desc

    On another note, if you're creating new records in the registration table using a stored procedure then you might want to consider abandoning the use of the trigger and put the xp_sendmail in the procedure.

    Hope that helps,

    Karl
    source control for SQL Server

  • February 29, 2008 at 3:15 am

    #783649

    Whether we not concanate the two variable like

    @Var1 + @Var2 ??

    Mohammad Irfan
    http://matespoint.blogspot.com
    http://www.irfit.com

  • February 29, 2008 at 4:06 am

    #783662

    Perfect.

    Thanks guys

  • February 29, 2008 at 4:24 am

    #783667

    No... not perfect... what happens in the trigger if you insert more than 1 row into the table? Are you only sending 1 email or are you sending 1 email for each new row? I suspect that you're only sending 1 email... 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply