SQL TRIGGER FOREACH LOOP

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.
desiredforsomeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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
desiredforsomeAuthor Commented:
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
Scott PletcherSenior DBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

ste5anSenior DeveloperCommented:
What SQL Server version??
0
desiredforsomeAuthor Commented:
SQL Server  Express 2005 Service Pack 4
0
desiredforsomeAuthor Commented:
Works perfect thank you.
0
ste5anSenior DeveloperCommented:
You know that this version is no longer supported (mainstream)?
0
desiredforsomeAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.