Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

SQL Insert Trigger

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
CipherIS
Asked:
CipherIS
1 Solution
 
Shaun KlineLead Software EngineerCommented:
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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now