Solved

SQL TRIGGER FOREACH LOOP

Posted on 2014-11-14
8
176 Views
Last Modified: 2014-11-14
I am looking to create a sql trigger that on insert of record creates ultiple records and inserts them into another table.

For instance

Record 1234 gets inserted into table A

Then a trigger runs for after insert

It checks table B to see if record 1234 exists. If it does not then it inserts multiple items for Record 1234 such as

INSERT INTO TABLEB VALUES(1234, 'DOCUMENT A','Y');
INSERT INTO TABLEB VALUES(1234, 'DOCUMENT B','Y');
INSERT INTO TABLEB VALUES(1234, 'DOCUMENT C','Y');

Thanks in advanced.
0
Comment
Question by:desiredforsome
  • 4
  • 3
8 Comments
 
LVL 32

Expert Comment

by:ste5an
ID: 40442858
Please think set based.. e.g.

CREATE TRIGGER tr_TableA_I ON TableA
    AFTER INSERT
AS
    SET NOCOUNT ON;
 
    INSERT  INTO TableB
            ( MyNumber ,
              DocumentName ,
              YesNoColumn
            )
            SELECT  I.MyNumber ,
                    D.DocumentName ,
                    'Y'
            FROM    INSERTED I
                    LEFT JOIN TableB ON I.MyNumber = B.MyNumber
                    CROSS APPLY ( VALUES ( 'DOCUMENT A', 'DOCUMENT V', 'DOCUMENT C') ) D ( DocumentName )
            WHERE   B.MyNumber IS NULL;

Open in new window

0
 

Author Comment

by:desiredforsome
ID: 40443017
I altered the code as follows but it tells me that there is an issue with  "Incorrect syntax near the keyword 'VALUES'

CREATE TRIGGER [requireddocs] ON xrefidtable
    AFTER INSERT
AS
    SET NOCOUNT ON;
 
    INSERT  INTO requestdoc
            ( XREFID ,
              DocumentName ,
              YesNoColumn
            )
            SELECT  I.XREFID ,
                    D.DocumentName ,
                    'Y'
            FROM    INSERTED I
                    LEFT JOIN requestdoc ON I.XREFID = B.XREFID
                    CROSS APPLY ( VALUES( 'DOCUMENT A', 'DOCUMENT V', 'DOCUMENT C') ) D ( DocumentName )
            WHERE   B.XREFID IS NULL;

Open in new window

0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 40443078
CREATE TRIGGER tableA__TRG_INS
ON tableA
AFTER INSERT
AS
SET NOCOUNT ON;
INSERT INTO dbo.tableB ( colname1, colname2, colname3 )
SELECT i.record_id, document_name, 'Y'
FROM inserted i
CROSS JOIN (
    SELECT 'DOCUMENT A' AS document_name UNION ALL
    SELECT 'DOCUMENT B' UNION ALL
    SELECT 'DOCUMENT C'    
) AS document_names
WHERE
    NOT EXISTS (
        SELECT 1
        FROM dbo.tableB b
        WHERE
            b.record_id = i.record_id
    )
0
 
LVL 32

Expert Comment

by:ste5an
ID: 40443084
What SQL Server version??
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:desiredforsome
ID: 40443089
SQL Server  Express 2005 Service Pack 4
0
 

Author Closing Comment

by:desiredforsome
ID: 40443093
Works perfect thank you.
0
 
LVL 32

Expert Comment

by:ste5an
ID: 40443101
You know that this version is no longer supported (mainstream)?
0
 

Author Comment

by:desiredforsome
ID: 40443405
I know. Its being used by a peice of software at work and I am building on top of it and the software willf ail if it gets upgraded.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

744 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

11 Experts available now in Live!

Get 1:1 Help Now