Solved

SQL TRIGGER FOREACH LOOP

Posted on 2014-11-14
8
181 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 33

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 33

Expert Comment

by:ste5an
ID: 40443084
What SQL Server version??
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 33

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

910 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

22 Experts available now in Live!

Get 1:1 Help Now