• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 179
  • Last Modified:

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.
0
desiredforsome
Asked:
desiredforsome
  • 2
1 Solution
 
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
 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now