Solved

SQL Trigger with multiple insert statements not working

Posted on 2013-01-23
12
491 Views
Last Modified: 2013-02-07
I have a trigger on one of my table that has multiple insert statements in it.  When the trigger kicks off it is only executing the first insert statement.  What do I need to do to get all of the insert statements to run.  Code attached.

ALTER TRIGGER [dbo].[UpdateForecastBreakdown]
   ON  [dbo].[Forecasts] 
   FOR Insert
AS 
Begin
INSERT INTO dbo.Forecasts_Breakdown(Forecast_KeyID, BreakdownType, lineorder, margin)
VALUES(@@IDENTITY, 'Labor', 1, 0)

INSERT INTO dbo.Forecasts_Breakdown(Forecast_KeyID, BreakdownType, lineorder, margin)
VALUES(@@IDENTITY, 'Subs/POs', 2, 0)


INSERT INTO dbo.Forecasts_Breakdown(Forecast_KeyID, BreakdownType, lineorder)
VALUES(@@IDENTITY, 'Materials', 3)

INSERT INTO dbo.Forecasts_Breakdown(Forecast_KeyID, BreakdownType, lineorder, margin)
VALUES(@@IDENTITY, 'Retainage', 4, 0)
end

Open in new window

0
Comment
Question by:imstac73
  • 5
  • 5
  • 2
12 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 38810743
You need to get a list of ALL the records identifier from INSERTED which is a internal SQL table and process them ALL one by one in a LOOP OR in a batch depending what you need to do.

So it would be something like:

DECLARE @rct int
SET @rct =      (select count(*) from INSERTED)

IF @rct = 1
BEGIN
      -- statements for single row to process here
END
ELSE IF @rct > 1
BEGIN

      DECLARE @id int
      DECLARE id_list CURSOR FOR (SELECT ID  FROM INSERTED)
      OPEN id_list
      FETCH next FROM id_list INTO @id
      WHILE @@fetch_status=0
            BEGIN

                  --statements to process one row from the batch
                  FETCH next FROM id_list INTO @id
            END

CLOSE id_list
DEALLOCATE id_list

END
0
 

Author Comment

by:imstac73
ID: 38810816
So would I put all my insert statements in both locations where you have '-statements'?  

I"m not sure how the code above helps as there will only be one record added to the triggered table at a time.  

The problem I"m having is that I need to insert 4 records in my breakdown table for every one record that is added to the triggered 'Forecasts' table. And currently the trigger runs the first insert line and then dies.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 250 total points
ID: 38810996
ALTER TRIGGER [dbo].[UpdateForecastBreakdown]
   ON  [dbo].[Forecasts]
   AFTER Insert
AS
INSERT INTO dbo.Forecasts_Breakdown (
    Forecast_KeyID, BreakdownType, lineorder, margin
)
SELECT
    $IDENTITY, BreakdownType, lineorder, 0
FROM inserted
CROSS JOIN (
    SELECT 'Labor' AS BreakdownType, 1 AS lineorder UNION ALL
    SELECT 'Subs/POs', 2 UNION ALL
    SELECT 'Materials', 3 UNION ALL
    SELECT 'Retainage', 4
) AS hardcoded_data
0
 

Author Comment

by:imstac73
ID: 38810997
I think I figured it out.  From your code I figured out that I need to assign the @@identity to a variable as the trigger maybe couldn't find the identity id after the first insert statement.  I did this and tested it and it worked with me manually inserting a record into the triggered table. I'm going to watch it and see if the next record added automatically also works.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38811000
That's not the best method.

Please refresh this q and look at my suggested code.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 38811166
lets understand this correctly...you said that "...trigger runs the first insert line and then dies. "
so my understanding was that you do a batch operation (insert or a batch delete/update where the result would be the same) in the table therefor the trigger dies because of that.
Is this what is happening? Whet is the error when the "trigger runs the first insert line and then dies. '?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:imstac73
ID: 38816683
I don't get an error..it just only adds the first insert record in my trigger code.  It is now working since I added the code to store the @@identity value in a variable and then call the variable within all the insert statements.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38816703
That is not an accurate method in any sense.  Good luck with that any time you insert more than one row at once, or a different trigger fires ahead of the current trigger, or any of a dozen other things.
0
 

Author Comment

by:imstac73
ID: 38852839
This trigger fires in response to another trigger. What is the correct method to do it then?
0
 

Author Comment

by:imstac73
ID: 38852867
Scott,
I am looking at your code posted: by: ScottPletcherPosted on 2013-01-23 at 11:27:41ID: 38810996 and I don't understand how this inserts 4 separate records.  To me it only looks like it inserts one record.

My process is as follows:
User enters record into Projects table
Trigger on Projects table inserts one record into Forecasts table
Trigger on Forecasts table  inserts four records into Forecasts_Breakdown table.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38852923
The CROSS JOIN generates the extra rows.

For example, run this SQL and you'll see that every input row gets joined to all 4 rows from the CROSS JOINed table:


SELECT
    ident, BreakdownType, lineorder
FROM (
    SELECT 1 AS ident UNION ALL
    SELECT 2
) AS inserted --simulate two "inserted" col values
CROSS JOIN (
    SELECT 'Labor' AS BreakdownType, 1 AS lineorder UNION ALL
    SELECT 'Subs/POs', 2 UNION ALL
    SELECT 'Materials', 3 UNION ALL
    SELECT 'Retainage', 4
) AS hardcoded_data
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38864759
Just to clarify, a CROSS JOIN joins every row in the first table to every row in the second table.

So, if the first table had 10 rows, and the second table had 8 rows, a CROSS JOIN would produce 80 rows.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Edureka is one of the fastest growing and most effective online learning sites.  We are here to help you succeed.

911 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

18 Experts available now in Live!

Get 1:1 Help Now