Solved

How to handle multi-row insertion in insert trigger

Posted on 2004-09-02
27
409 Views
Last Modified: 2008-02-01
I have the following insert trigger which seems to work okay when only one row is inserted. When multiple rows are inserted then it fails. Would you please point out where I am going wrong here?

Thanks in advance for any help.


CREATE TRIGGER [dt_JobInsert] ON [dbo].[JobTable]
FOR INSERT
AS

DECLARE @TaskMasterID as int
DECLARE @JobID as varchar(50)

SELECT @JobID = (SELECT JOB_ID FROM Inserted)

INSERT TaskMaster(JobNumber,TaskDescription,Status,DistrictID)
SELECT JOB_ID, GETDATE(), 'New',DISTRICT_CODE
FROM Inserted

SELECT @TaskMasterID =
(SELECT TaskMasterID FROM TaskMaster WHERE TaskMaster.JobNumber = @JobID)

UPDATE TaskMaster SET TaskMaster.OrigTaskMasterID = @TaskMasterID WHERE TaskMaster.TaskMasterID = @TaskMasterID
0
Comment
Question by:samble
  • 13
  • 12
  • 2
27 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11969348
CREATE TRIGGER [dt_JobInsert] ON [dbo].[JobTable]
FOR INSERT
AS

INSERT TaskMaster(JobNumber,TaskDescription,Status,DistrictID, OrigTaskMaster)
SELECT i.JOB_ID, GETDATE(), 'New', i.DISTRICT_CODE, t.TaskMasterID
FROM    Inserted i
             Inner Join TaskMaster t On i.JOB_ID = t.JOBNumber

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11969354
Also, please maintain your abandoned questions:
1 06/24/2004 200 How to prevent the installer from overwr...  Open Visual Basic
2 07/08/2004 125 Is there a way to suppress everything bu...  Open Crystal Reports
0
 

Author Comment

by:samble
ID: 11969433
Thanks for the response. I had left another key piece in my original code. The complete code is given below.

CREATE TRIGGER [dt_JobInsert] ON [dbo].[JobTable]
FOR INSERT
AS

DECLARE @TaskMasterID as int
DECLARE @JobID as varchar(50)

SELECT @JobID = (SELECT JOB_ID FROM Inserted)

INSERT TaskMaster(JobNumber,TaskDescription,Status,DistrictID)
SELECT JOB_ID, GETDATE(), 'New',DISTRICT_CODE
FROM Inserted

SELECT @TaskMasterID =
(SELECT TaskMasterID FROM TaskMaster WHERE TaskMaster.JobNumber = @JobID)

UPDATE TaskMaster SET TaskMaster.OrigTaskMasterID = @TaskMasterID WHERE TaskMaster.TaskMasterID = @TaskMasterID

INSERT TaskMasterAddendum (TaskMasterID,TechWriter)
SELECT @TaskMasterID,TECHWRITER_NAME
FROM Inserted

I undertsand combining the first four SQL into one will help me with multi-row insertion. But how do I handle the last SQL in the updated code? Your help is very much appreciated.

Kind regards


0
 
LVL 15

Accepted Solution

by:
jdlambert1 earned 20 total points
ID: 11969564
Just add this after AC's solution above:

INSERT TaskMasterAddendum (TaskMasterID,TechWriter)
SELECT TaskMasterID,TECHWRITER_NAME
FROM    Inserted i
             Inner Join TaskMaster t On i.JOB_ID = t.JOBNumber
0
 

Author Comment

by:samble
ID: 11969713
Thanks for the response I have so far. The trigger syntax is okay. But when I insert a record to the table then I get the error "Error converting data type varchar to bigint". The column "JOB_ID" is of "bigint" type and the column "JobNumber" is of "varchar" type. How do I handle this? Thanks for any help.

Kind regards
0
 

Author Comment

by:samble
ID: 11969867
Here is my latest version:

CREATE TRIGGER [dt_JobInsert] ON [dbo].[JobTable]
FOR INSERT
AS

INSERT TaskMaster(JobNumber,TaskDescription,Status,DistrictID, OrigTaskMaster)
SELECT CONVERT(varchar(50),i.JOB_ID), GETDATE(), 'New', i.DISTRICT_CODE, t.TaskMasterID
FROM    Inserted i
             Inner Join TaskMaster t On CONVERT(varchar(50),i.JOB_ID) = t.JOBNumber

INSERT TaskMasterAddendum (TaskMasterID,TechWriter)
SELECT TaskMasterID,TECHWRITER_NAME
FROM    Inserted i
             Inner Join TaskMaster t On CONVERT(varchar(50),i.JOB_ID) = t.JOBNumber

Still when I insert a record to the JobTable I do not see the trigger causing inserts to TaskMaster and TaskMasterAddendum tables.

Any help is appreciated. Thanks in advance
0
 

Author Comment

by:samble
ID: 11969931
Here is my latest version:

CREATE TRIGGER [dt_JobInsert] ON [dbo].[JobTable]
FOR INSERT
AS

INSERT TaskMaster(JobNumber,TaskDescription,Status,DistrictID, OrigTaskMaster)
SELECT CONVERT(varchar(50),i.JOB_ID), GETDATE(), 'New', i.DISTRICT_CODE, t.TaskMasterID
FROM    Inserted i
             Inner Join TaskMaster t On CONVERT(varchar(50),i.JOB_ID) = t.JOBNumber

INSERT TaskMasterAddendum (TaskMasterID,TechWriter)
SELECT TaskMasterID,TECHWRITER_NAME
FROM    Inserted i
             Inner Join TaskMaster t On CONVERT(varchar(50),i.JOB_ID) = t.JOBNumber

If I take out the "OrigTaskMasterID" and the associated "t.TaskMasterID" then teh tables are getting inserted as expected. The culprit seems to be "t.TaskMasterID". Any thoughts on this?

Kind regards
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11969996
Change this:
             Inner Join TaskMaster t On CONVERT(varchar(50),i.JOB_ID) = t.JOBNumber

To:
             Inner Join TaskMaster t On i.JOB_ID = CAST(t.JOBNumber As bigint)
0
 

Author Comment

by:samble
ID: 11971106
Thanks for your response AC. Here is the code after your suggested changes.

CREATE TRIGGER [dt_JobInsert] ON [dbo].[JobTable]
FOR INSERT
AS

INSERT TaskMaster(JobNumber,TaskDescription,Status,DistrictID, OrigTaskMaster)
SELECT CONVERT(varchar(50),i.JOB_ID), GETDATE(), 'New', i.DISTRICT_CODE, t.TaskMasterID
FROM    Inserted i
             Inner Join TaskMaster t On i.JOB_ID = CAST(t.JOBNumber AS bigint)

INSERT TaskMasterAddendum (TaskMasterID,TechWriter)
SELECT TaskMasterID,TECHWRITER_NAME
FROM    Inserted i
             Inner Join TaskMaster t On i.JOB_ID = CAST(t.JOBNumber AS bigint)

Now I get the error "Error converting data type varchar to bigint". Do you have any thoughts on this? Thanks for any help.

Kind regards
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11971257
You must have another data type mismatch between OrigTaskMaster and TaskMasterID. If OrigTaskMaster is varchar, try this:

CREATE TRIGGER [dt_JobInsert] ON [dbo].[JobTable]
FOR INSERT
AS

INSERT TaskMaster(JobNumber,TaskDescription,Status,DistrictID, OrigTaskMaster)
SELECT CONVERT(varchar(50),i.JOB_ID), GETDATE(), 'New', i.DISTRICT_CODE, CONVERT(varchar(50),t.TaskMasterID)
FROM    Inserted i
             Inner Join TaskMaster t On CONVERT(varchar(50),i.JOB_ID) = t.JOBNumber

INSERT TaskMasterAddendum (TaskMasterID,TechWriter)
SELECT TaskMasterID,TECHWRITER_NAME
FROM    Inserted i
             Inner Join TaskMaster t On CONVERT(varchar(50),i.JOB_ID) = t.JOBNumber

You can always convert or cast from a number data types to text data types, but you can't go the other way unless the text values are compatible. If, for example, you want to cast to a bigint, you can double-check the values with this:
SELECT JobNumber FROM TaskMaster WHERE IsNumeric(JobNumber) = 0
This will give you list of values that can't be implicitly converted, like if it has a value such as "n/a".
0
 

Author Comment

by:samble
ID: 11971693
Thanks for your response. The "OrigTaskMasterID" and "TaskMasterID" are of the same type and they both are "int" type. Do you have any other suggestion?

Thanks for all the help
0
 

Author Comment

by:samble
ID: 11971919
Something is not right with the INNER JOIN statement.

Inner Join TaskMaster t On CONVERT(varchar(50),i.JOB_ID) = t.JOBNumber

This is causing the insert to not happen on the TaskMaster table in the first place. Since this is not inserting the following insert also is not successful. Do you see anything wrong with the comparison statement? If I don't have teh Inner Join statement then multi-row insert will fail.

Thanks for all the help

Kind regards
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11973746
jdlambert1 has told you what is the problem.  One of the JOBNumber values is not a number.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:samble
ID: 11974094
Thanks for your response AC. I have cleared all the tables. I am starting afresh. Still I do not see the insert on the TaskMaster table succeeding. Would you please tell me how to proceed to resolve this?

Thanks for all your help
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11974164
>> Still I do not see the insert on the TaskMaster table succeeding. <<
What does this mean?  Is there an error message, if so what is it?
0
 

Author Comment

by:samble
ID: 11974210
There is no error message popping up when I do the insert on the "JobTable" which will cause the trigger to fire. The insert on "JobTable" succeeds. After having done the insert when I inspect the "TaskMaster" and "TaskMasterAddendum" tables I do not see any new rows in the table. The tables remain cleared.

Thanks in advance
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11974255
Than we get down to the basics:
1. Post the structure for JobTable and TaskMaster (we can worry about TaskMasterAddendum later)
2. Post the data you are adding, and confirm that there is a JOBNumber that matches the JOB_ID just added to JobTable.
0
 

Author Comment

by:samble
ID: 11974470
Thanks for the followup.

The structure of the tables:

CREATE TABLE [dbo].[JobData] (
      [JOB_ID] [bigint] NOT NULL ,
      [DISTRICT_CODE] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [TECHWRITER_NAME] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TaskMaster] (
      [TaskMasterID] [bigint] IDENTITY (1, 1) NOT NULL ,
      [JobNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [TaskDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [OrigTaskMasterID] [bigint] NULL ,
      [DistrictID] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

Here is my SQL for inserting record into JobTable:

INSERT INTO JobData
                      (JOB_ID,DISTRICT_CODE,TECH_WRITER)
VALUES     ('100110002', '5', ' Harris ')

When I insert the above data then I do see the JOB_ID column to be set to '100110002'. There are no rows in "TaskMaster" table.

Thanks as always.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11976125
There are a number of problems here:
1. Previously we had been talking about JobTable now I will assume it is JobData.
2. I had mistakenly named your column "OrigTaskMasterID" as "OrigTaskMaster"
3."TECH_WRITER" should be "TECHWRITER_NAME"
4. JOB_ID is numeric value.  No need for CONVERT(varchar(50) ... or enclose the value in apostrophes
5. OrigTaskMasterID is also numeric so no need for CONVERT(varchar(50) ...

So, after making those changes this works for me:

CREATE TABLE [dbo].[JobData] (
     [JOB_ID] [bigint] NOT NULL ,
     [DISTRICT_CODE] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
     [TECHWRITER_NAME] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TaskMaster] (
     [TaskMasterID] [bigint] IDENTITY (1, 1) NOT NULL ,
     [JobNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
     [TaskDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
     [Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
     [OrigTaskMasterID] [bigint] NULL ,
     [DistrictID] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

CREATE TRIGGER [dt_JobInsert] ON [dbo].[JobData]
FOR INSERT
AS

INSERT TaskMaster(JobNumber,TaskDescription,Status,DistrictID, OrigTaskMasterID)
SELECT CONVERT(varchar(50),i.JOB_ID), GETDATE(), 'New', i.DISTRICT_CODE, t.TaskMasterID)
FROM    Inserted i
             Inner Join TaskMaster t On i.JOB_ID = t.JOBNumber

-- Uncomment the following to add a row to the TaskMaster table
-- SET IDENTITY_INSERT TaskMaster ON
-- Insert TaskMaster (TaskMasterID, JobNumber) VALUES (12345678, 100110002)
-- SET IDENTITY_INSERT TaskMaster OFF

INSERT JobData (JOB_ID,
            DISTRICT_CODE,
            TECHWRITER_NAME)
VALUES (100110002,
             '5',
             ' Harris ')
0
 

Author Comment

by:samble
ID: 11976435
Thanks for taking the trouble AC. That is good news to hear it works for you. I have copied the the code you have proposed for the trigger part (I have dropped the trailing ')' for the SELECT because there is no matching '('.). I did an insert on the "JobData" table using the SQL identical to yours. But my results are different. There is no insert in the TaskMaster table. There is an insert as expected in teh JobData table. I don't know how this can be explained.

Do you have any more wisdom to share with me? Thanks and kind regards.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11976492
>>(I have dropped the trailing ')' for the SELECT because there is no matching '('.)<<
That should have been:
SELECT i.JOB_ID, GETDATE(), 'New', i.DISTRICT_CODE, t.TaskMasterID

>>There is no insert in the TaskMaster table. <<
Did you have a row in TaskMaster that has a value of 100110002 in JobNumber?  Otherwise you are right it will fail to add to TaskMaster.  In order to see it work then you would have to run the following code (commented above)
SET IDENTITY_INSERT TaskMaster ON
Insert TaskMaster (TaskMasterID, JobNumber) VALUES (12345678, 100110002)
SET IDENTITY_INSERT TaskMaster OFF
0
 

Author Comment

by:samble
ID: 11976612
I am at a total loss here. The purpose of the trigger is to isert a record into TaskMaster table whenever there is an insert on the JobData table. I hope my intent for the trigger is clear.

Thanks in advance
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11976831
>>I am at a total loss here. <<
Me to.  From your original question:

SELECT @JobID = (SELECT JOB_ID FROM Inserted)

INSERT TaskMaster(JobNumber,TaskDescription,Status,DistrictID)
SELECT JOB_ID, GETDATE(), 'New',DISTRICT_CODE
FROM Inserted

SELECT @TaskMasterID =
(SELECT TaskMasterID FROM TaskMaster WHERE TaskMaster.JobNumber = @JobID)

UPDATE TaskMaster SET TaskMaster.OrigTaskMasterID = @TaskMasterID WHERE TaskMaster.TaskMasterID = @TaskMasterID

While it is true that the Insert will always add a row.  The Update will never update unless the JOB_ID in JobData matches the Job_Number in TaskMaster.

So I guess what I am saying is that would help if you would explain in English what you are trying to accomplish, rather than me try to guess from your code.
0
 

Author Comment

by:samble
ID: 11976930
AC, I hand it to you. You have lot of patience. Here we go.

I am trying to write an insert trigger on a table "JobData". When a record is inserted into "JobData" table then I want to insert a new record into "TaskMaster" and "TaskMasterAddendum" tables extracting data out of "JobData" table.

The UPDATE was being used to copy the TaskMaterID to OrigTaskMasterID column.

I should be in a position to handle multi-row insert into "JobData" table.

Please let me know if what I have stated is clear enough.

Kind regards
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11977250
>>You have lot of patience. << I was told it was called stubborness :)

>>I am trying to write an insert trigger on a table "JobData". When a record is inserted into "JobData" table then I want to insert a new record into "TaskMaster" and "TaskMasterAddendum" tables extracting data out of "JobData" table. <<

Than all you need is this (we can handle "TaskMasterAddendum" after):
CREATE TRIGGER [dt_JobInsert] ON [dbo].[JobData]
FOR INSERT

AS

INSERT TaskMaster(JobNumber,TaskDescription,Status,DistrictID, OrigTaskMasterID)
SELECT i.JOB_ID, GETDATE(), 'New', i.DISTRICT_CODE, t.TaskMasterID
FROM    Inserted i

>>The UPDATE was being used to copy the TaskMaterID to OrigTaskMasterID column. <<
This it the part that confuses me.  TaskMasterID is an IDENTITY column and therefore is generated with the insert.  You want to copy that to OrigTaskMasterID, so that they are both the same?  If this is really all you want to do (and I am not sure why you would want to do that) than all you have to do is write your Trigger as follows:

CREATE TRIGGER [dt_JobInsert] ON [dbo].[JobData]
FOR INSERT

AS

INSERT TaskMaster(JobNumber, TaskDescription, Status, DistrictID)
SELECT i.JOB_ID, GETDATE(), 'New', i.DISTRICT_CODE
FROM    Inserted i

UPDATE TaskMaster
SET OrigTaskMasterID = t.TaskMasterID
FROM TaskMaster t
          INNER JOIN Inserted i On t.JOB_ID = t.JobNumber

The problem with that is that we are making the assumption that JOB_ID/JobNumber are unique.  If that is not the case we may have to resort to plan B.  Let me know.

By the way this last solution, looks remarkably like the solution in your original question ...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11977278
Typo the triiger should have been:
CREATE TRIGGER [dt_JobInsert] ON [dbo].[JobData]
FOR INSERT

AS

INSERT TaskMaster(JobNumber, TaskDescription, Status, DistrictID)
SELECT i.JOB_ID, GETDATE(), 'New', i.DISTRICT_CODE
FROM    Inserted i

UPDATE TaskMaster
SET OrigTaskMasterID = t.TaskMasterID
FROM TaskMaster t
          INNER JOIN Inserted i On t.JobNumber = i.JOB_ID       -- This line needs to change
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 230 total points
ID: 11977322
Here is the whole script that I just ran:

CREATE TABLE [dbo].[JobData] (
     [JOB_ID] [bigint] NOT NULL ,
     [DISTRICT_CODE] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
     [TECHWRITER_NAME] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TaskMaster] (
     [TaskMasterID] [bigint] IDENTITY (1, 1) NOT NULL ,
     [JobNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
     [TaskDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
     [Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
     [OrigTaskMasterID] [bigint] NULL ,
     [DistrictID] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TRIGGER [dt_JobInsert] ON [dbo].[JobData]
FOR INSERT

AS

INSERT TaskMaster(JobNumber, TaskDescription, Status, DistrictID)
SELECT i.JOB_ID, GETDATE(), 'New', i.DISTRICT_CODE
FROM    Inserted i

UPDATE TaskMaster
SET OrigTaskMasterID = t.TaskMasterID
FROM TaskMaster t
          INNER JOIN Inserted i On i.JOB_ID = t.JobNumber
GO

INSERT JobData (JOB_ID, DISTRICT_CODE, TECHWRITER_NAME)
VALUES (100110002, '5', ' Harris ')
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SLQ View not updating 10 47
Help with Search Query in SQL Server 2008R2 Version 26 58
Updating variable table 9 17
Report Builder 9 30
In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

705 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

17 Experts available now in Live!

Get 1:1 Help Now