SQL Trigger cross join

I am using SQL Express 2005 in this project and I have a trigger that is working hwoever I need to add categories to specific entries.

USE [outlookreport]
GO
/****** Object:  Trigger [dbo].[insertdocuments]    Script Date: 11/14/2014 13:53:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[insertdocuments]
ON [outlookreport].[dbo].[xrefidtable]
AFTER INSERT
AS
SET NOCOUNT ON;
INSERT INTO outlookreport.dbo.requestdoc(xrefid, requested, [status],[required] )
SELECT i.xrefid, requested, 'N','N'
FROM inserted i
CROSS JOIN (
SELECT 'PAY STUBS LAST 60 DAYS (MUST SHOW YEAR TO DATE AND NAME OF EMPLOYEE' AS REQUESTED UNION ALL
    SELECT 'W2s 1099s  K-1 FOR 2013 2012' UNION ALL
     SELECT 'TAX RETURNS 2013 2012 ALL FORMS AND SCHEDULES - ALL PAGES' UNION ALL
     SELECT 'SELF EMPLOYED - BUSINESS TAX RETURNS 2013 IF FILED 2012 2011 ALL FORMS' UNION ALL
     SELECT 'BUSINESS LICENSE OR ACCOUNTANTS LETTER VERIFYING IN BUSINESS FOR 2 YEARS' UNION ALL
     SELECT 'CURRENT YTD PROFIT AND LOSS P AND L AND BALANCE SHEET IF APPLICABLE' UNION ALL
     SELECT 'PENSION STATEMENT' UNION ALL
     SELECT 'SOCIAL SECURITY STATEMENT' UNION ALL
     SELECT 'DIVORCE DECREE WITH ALIMONY AND CHILD SUPPORT ATTACHMENTS' UNION ALL
     SELECT 'EMPLOYMENT CONTRACT IF NOT SALARIED' UNION ALL
     SELECT 'OTHER INCOME DOCUMENTATION AND PROOF OF COURSE I.E. RENTAL INCOME' 

) AS requested
WHERE
    NOT EXISTS (
        SELECT 1
        FROM outlookreport.dbo.requestdoc b
        WHERE
            b.xrefid = i.xrefid
    )

Open in new window


I have another column in the table called cateogry and that needs to be populated in when this trigger runs for example

 SELECT 'TAX RETURNS 2013 2012 ALL FORMS AND SCHEDULES - ALL PAGES' UNION ALL would also put in a "INCOME" entry into the cateogry column

  SELECT 'BUSINESS LICENSE OR ACCOUNTANTS LETTER VERIFYING IN BUSINESS FOR 2 YEARS' UNION ALL would put in "BUSINESS" into the category column.
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.

Scott PletcherSenior DBACommented:
CROSS JOIN (
SELECT 'PAY STUBS LAST 60 DAYS (MUST SHOW YEAR TO DATE AND NAME OF EMPLOYEE' AS REQUESTED, 'BUSINESS' AS category UNION ALL
    SELECT 'W2s 1099s  K-1 FOR 2013 2012', 'INCOME' UNION ALL
     SELECT 'TAX RETURNS 2013 2012 ALL FORMS AND SCHEDULES - ALL PAGES', 'INCOME' UNION ALL
 ....
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
desiredforsomeAuthor Commented:
hMM I am having some issues getting all the syntax correctly.

For the AS it has requested. Dont know if I should add the cateogry there.
0
Scott PletcherSenior DBACommented:
Remember that every row in the CROSS JOIN must have the same number of values in it.  You can't add a category for some rows and not others.  You can pad rows with NULL for category if it doesn't apply for that row.
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 2005

From novice to tech pro — start learning today.