Solved

SQL TRIGGER FOREACH LOOP

Posted on 2014-11-14
8
189 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 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:
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 33

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 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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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 shrink a transaction log file down to a reasonable size.

730 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