SQL Trigger Syntax

I have this trigger: -

CREATE TRIGGER trNAME 
      ON ApSupplier
      AFTER UPDATE 
AS 
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for trigger here
      IF UPDATE (BankAccount)
      BEGIN
            EXEC msdb.dbo.sp_send_dbmail  
			@profile_name = 'TEST',
			@recipients = "me@work.com", 
			@body = "TEST",
			@subject = "SupplierName +BankAccount"

      END
END

Open in new window

I would like the @Subject to be the SupplierName and BankAccount that has been amended.

Does anyone know how to do this?
HKFueyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>@subject = "SupplierName +BankAccount"
>I would like the @Subject to be the SupplierName and BankAccount that has been amended.
Update triggers utilize two virtual tables, 'deleted' which is the old row values, and 'inserted' which is the new row values.  So in your case, it would go something like this..

@subject = inserted.SupplierNameColumnName + ' ' + inserted.BankAccountColumnName

Open in new window


*  You may have to CAST(inserted.BankAccountColumnName) to a char if it's a numeric value.
*  The above format works even when these columns where not UPDATEd.
0
HKFueyAuthor Commented:
OK, thanks but I get this: -
Incorrect syntax near '.'.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<correction>

Change the AFTER UPDATE to FOR UPDATE, then edit based on my suggestion, and execute.

AFTER UPDATE which executes AFTER the row is UPDATEd, and I'm not abundantly sure if the virtual tables inserted and deleted are available, so I'll step back to encourage other experts to respond.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Scott PletcherSenior DBACommented:
If you're sure you'll only be updating 1 and only 1 bankaccount per UPDATE statement, you can do this:


CREATE TRIGGER trNAME
      ON ApSupplier
      AFTER UPDATE
AS
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  IF UPDATE (BankAccount)
  BEGIN
        DECLARE @BankAccount varchar(40)
        DECLARE @SupplierName varchar(100)
        DECLARE @subject nvarchar(255)
        SELECT TOP (1)
        @BankAccount = BankAccount, @SupplierName = @SupplierName
        FROM inserted
        SET @subject = @SupplierName + ' ' + @BankAccount
        EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'TEST',
        @recipients = "me@work.com",
        @body = "TEST",
        @subject = @subject
  END
0
HKFueyAuthor Commented:
Hi Scott,

This works but fires for any change in the Supplier table, is there a way to limit the trigger to fire only if 'BankAccount' field is changed changed?
0
HKFueyAuthor Commented:
My previous comment is incorrect, if I manually edit the table the solution works. When saved from our System front end any change fires the trigger so the 'Save' button must affect all fields. : (
0
Scott PletcherSenior DBACommented:
Actually we can check to see if the value actually changed from what it was before the UPDATE:



  IF UPDATE (BankAccount)
  BEGIN
        DECLARE @BankAccount varchar(40)
        DECLARE @SupplierName varchar(100)
        DECLARE @subject nvarchar(255)
        DECLARE @BankAccountOld varchar(40)
        SELECT TOP (1)
        @BankAccount = BankAccount, @SupplierName = @SupplierName
        FROM inserted
        SELECT TOP (1)
        @BankAccountOld = BankAccount
        FROM deleted
        -- verify that the BankAccount value *actually changed* before sending email.
        IF ISNULL(@BankAccount, 'zzz') <> ISNULL(@BankAccountOld, 'zzz')

        BEGIN
            SET @subject = @SupplierName + ' ' + @BankAccount
            EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'TEST',
            @recipients = "me@work.com",
            @body = "TEST",
            @subject = @subject
        END --IF
  END
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HKFueyAuthor Commented:
Hi Scott,
This nearly works, if I change the Bank Account then I get an email with the details. If I change another field I get a blank email.
0
HKFueyAuthor Commented:
I added this and it now works! Thanks very much!!

        SET @Text = 'Details: ' + @Supplier + ' ' + @SupplierName + ' ' + @BankAccount
        if len(@Text)>0
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.