troubleshooting Question

SQL Trigger cross join

Avatar of desiredforsome
desiredforsome asked on
Microsoft SQL Server 2005
3 Comments1 Solution238 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros