Solved

SQL Insert Trigger

Posted on 2010-11-17
1
256 Views
Last Modified: 2012-05-10
I have 3 tables - TableA, TableB, and TableC.  Table A is populated with data.  When an insert is performed on TableB I want to get appropriate data from TableA.  Then modify that data and insert the modified data into TableC.  

The data in Table A is as such:  Provider {0} has been modified by {1}.
It should be inserted into TableC as:  Provider xxxx has been modified by yyyy -- per the code in the trigger.

This is not working.  Instead Provider {0} has been modified by {1}. is being inserted into TableC.  What is wrong with my Trigger.

** Code is not inserting - Here it is below **

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER TestBInsert
   ON  _TestB
   AFTER INSERT
AS
BEGIN
      declare @uName varchar(50);
      declare @Message varchar(50);
      declare @TestB varchar(50);

      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

        -- Insert statements for trigger here
      SELECT @uName=SYSTEM_USER;
      print 'UserName: ' + @uName;

      SELECT @Message = (SELECT [TestAData] FROM _TestA Where TestApkid = 1);
      print 'Message: ' + @message;

      SELECT @TestB = (SELECT [TestBData] FROM Inserted);      
      print 'TestB Data: ' + @TestB;

      SELECT REPLACE(@Message,'{0}',@TestB);
      SELECT REPLACE(@Message,'{1}',@uName);
      print 'Insert Data: ' + @Message;

      Insert Into _TestC (TestCData) Values (@Message);

END
GO


Trigger.jpg
0
Comment
Question by:CipherIS
1 Comment
 
LVL 26

Accepted Solution

by:
Shaun Kline earned 500 total points
ID: 34156822
Change these two lines

      SELECT REPLACE(@Message,'{0}',@TestB);
      SELECT REPLACE(@Message,'{1}',@uName);

To
SET @Message = REPLACE(@Message,'{0}',@TestB);
SET @Message = REPLACE(@Message,'{1}',@uName);
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now