desiredforsome
asked on
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.
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.
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
)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
For the AS it has requested. Dont know if I should add the cateogry there.