Solved

SQL Trigger cross join

Posted on 2014-11-14
3
162 Views
Last Modified: 2014-12-03
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.
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
  • 2
3 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40443634
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
 

Author Comment

by:desiredforsome
ID: 40443682
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40453015
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

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

624 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