We help IT Professionals succeed at work.
Get Started

SQL 2005 Trigger, send mail, join tables

pkromer
pkromer asked
on
383 Views
Last Modified: 2012-05-09
I have a trigger built (below) which will email somebody when a field changes in a table. Now, I need to add data from another table in the same db, as well as data from a table in another db, into the body of the email.
 
In the same db I have a table called Suppliers.

In another db I have a table called Products.

I need to join a field (SupplierID) from the table in this trigger (Inventory) with a field in table Suppliers (SupplierID) so I can get the name of the supplier, which is in table Suppliers (SupplierName).

In the other db, table products has the name of the product (ProductName).

Bottom line, In need to display, in addition to the sku and other data already in the trigger, the supplier name and the product name.

All these tables will join on a common field (SKU).

Can this be done?
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER TRIGGER [dbo].[Cost_Change]
   ON  [dbo].[Inventory] 
   AFTER UPDATE
AS 
BEGIN
	
	SET NOCOUNT ON;
	--if update(cost)
	if exists (Select deleted.cost from deleted inner join inserted on deleted.Local_sku = inserted.local_sku where deleted.cost <> inserted.cost)
    Begin
     Insert into Audit_Cost (username, "when", SKU, old, new) select SUSER_SNAME(), GetDate(), inserted.local_sku, deleted.cost, inserted.cost from inserted inner join deleted on inserted.local_sku = deleted.local_sku
	
	  DECLARE @tableHTML  NVARCHAR(MAX) ;
	  Declare @sku  nvarchar(50);
	  Declare @old  money;
	  Declare @new  money;

      Select @sku= inserted.local_sku, @old= deleted.cost, @new=inserted.cost from inserted inner join deleted on inserted.local_sku = deleted.local_sku
		
	    SET @tableHTML =
		  N'<H3>The following cost changes were made:</H3>' +
		  'SKU ' + @sku + '<br />Old cost: <b>' + cast(@old as nvarchar(20)) + '</b><br />New cost: <b>' +  cast(@new as nvarchar(20)) + '</b><br />Changed by ' + SUSER_SNAME()
	    

	EXEC msdb.dbo.sp_send_dbmail @recipients='biff@biffsworld.com',
	  @subject = 'Cost Change',
	  @body = @tableHTML,
	  @body_format = 'HTML' ;
	
	end 

END

Open in new window

Comment
Watch Question
Chief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE