Solved

SQL TRIGGER FOREACH LOOP

Posted on 2014-11-14
8
193 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 34

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:
Scott Pletcher 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
Industry Leaders: 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!

 
LVL 34

Expert Comment

by:ste5an
ID: 40443084
What SQL Server version??
0
 

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 34

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

726 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