• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 189
  • Last Modified:

SQL Insert Trigger - Inserts everything!

I have a basic trigger, I want it to insert from table 1 to table 2 (in another db) whenever a new record is created in table 1.  But each time a new record is added, the trigger inserts all records from table 1 to table 2, instead of just the new, single record!

What am I doing wrong?  Trigger posted below:
USE [Training]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tr_Update_TrainFast]
   ON  [dbo].[TrainingEnquires]
   FOR INSERT AS 
 
INSERT INTO 
	TRAINFAST..Jobs
	(JobTypeCode, TrainingCourses.TradeCode, DateTimeReceived, CallTypeId, 
	CustomerTitle, CustomerForename, CustomerSurname, [Property], Street, 
	Locality, Town, County, Postcode, TelephoneMobile, TelephoneLandline, 
	EmailAddress, StatusId, AccountCode, DateTimeRequired, Notes, SourceId)
 
SELECT     
	TrainingEnquires.CourseId, TrainingCourses.TradeCode, TrainingEnquires.DateTimeCreated, 
	1, TrainingEnquires.Title, TrainingEnquires.Forename, TrainingEnquires.Surname, 
	TrainingEnquires.Property, TrainingEnquires.Street, TrainingEnquires.Locality, 
	TrainingEnquires.Town, TrainingEnquires.County, TrainingEnquires.Postcode, 
	TrainingEnquires.TelephoneMobile, TrainingEnquires.TelephoneLandline, 
	TrainingEnquires.EmailAddress, 1, 1, TrainingEnquires.DateTimeCreated,
	TrainingEnquires.Questions, TrainingEnquires.SourceId
FROM         
	TrainingEnquires 
	INNER JOIN TrainingCourses ON TrainingEnquires.CourseId = TrainingCourses.CourseId

Open in new window

0
Lapchien
Asked:
Lapchien
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
replace "TrainingEnquires" by "inserted"
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
full code:
ALTER TRIGGER [dbo].[tr_Update_TrainFast]
   ON  [dbo].[TrainingEnquires]
   FOR INSERT AS 
 
INSERT INTO 
      TRAINFAST..Jobs
      (JobTypeCode, TrainingCourses.TradeCode, DateTimeReceived, CallTypeId, 
      CustomerTitle, CustomerForename, CustomerSurname, [Property], Street, 
      Locality, Town, County, Postcode, TelephoneMobile, TelephoneLandline, 
      EmailAddress, StatusId, AccountCode, DateTimeRequired, Notes, SourceId)
 
SELECT     
      i.CourseId, c.TradeCode, i.DateTimeCreated, 
      1, i.Title, i.Forename, i.Surname, 
      i.Property, i.Street, i.Locality, 
      i.Town, i.County, i.Postcode, 
      i.TelephoneMobile, i.TelephoneLandline, 
      i.EmailAddress, 1, 1, i.DateTimeCreated,
      i.Questions, i.SourceId
FROM  INSERTED i
JOIN TrainingCourses c 
  ON i.CourseId = c.CourseId

Open in new window

0
 
LapchienDirectorAuthor Commented:
Perfect thanks - can see that I need to use the systable inserted - I must have been inserting all the records each time!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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