Solved

SQL Trigger cross join

Posted on 2014-11-14
3
159 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Trigger or Function that updates table with old values 5 57
SQL Restore Script - Syntax Error 8 117
Caste datetime 2 73
Replication failure 1 32
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…

710 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