DELETE FROM COLS 
WHERE EXISTS (SELECT * FROM deleted WHERE Job_Number = COLS.JOBS_Job_Number)
DELETE FROM ROWS 
WHERE EXISTS (SELECT * FROM deleted WHERE Job_Number = ROWS.JOBS_Job_Number)
DELETE FROM JOBS 
WHERE EXISTS (SELECT * FROM deleted WHERE Job_Number = JOBS.Job_Number)

DELETE gr
FROM dbo.Grid_Data gr
	LEFT OUTER JOIN dbo.Columns co 
		ON gr.Column_Number = co.Column_Number 
		AND gr.JOBS_Job_Number = co.JOBS_Job_Number
	LEFT OUTER JOIN dbo.ROWS ro 
		ON gr.Row_Number = ro.Row_Number 
		AND gr.JOBS_Job_Number = ro.JOBS_Job_Number
WHERE 	JOBS_Job_Number = @JobToDel
AND 	(	co.JOBS_Job_Number IS NOT NULL
	 OR	ro.JOBS_Job_Number IS NOT NULL
	)

DELETE FROM dbo.ROWS
WHERE JOBS_Job_Number = @JobToDel

DELETE FROM dbo.COLUMNS
WHERE JOBS_Job_Number = @JobToDel

DELETE FROM dbo.JOBS
WHERE Job_Number = @JobToDel

DELETE 
FROM dbo.Grid_Data gr
WHERE 	gr.Column_Number = @ColToDel
AND 	gr.JOBS_Job_Number = @JobNo

DELETE
FROM dbo.Columns co
WHERE 	co.Column_Number = @ColToDel
AND 	co.JOBS_Job_Number = @JobNo

DELETE 
FROM dbo.Grid_Data gr
WHERE 	gr.Row_Number = @RowToDel
AND 	gr.JOBS_Job_Number = @JobNo

DELETE
FROM dbo.Rows ro
WHERE 	ro.Column_Number = @RowToDel
AND 	ro.JOBS_Job_Number = @JobNo

use pubs

if object_id ('dbo.grid_data') > 0 drop table grid_data
if object_id ('dbo.[rows]') > 0 drop table rows
if object_id ('dbo.[columns]') > 0 drop table columns
if object_id ('dbo.jobs') > 0 drop table jobs
go

create table dbo.JOBS (Job_Number int primary key clustered)
go
create table dbo.[COLUMNS]
	(JOBS_Job_Number int references jobs (job_number) on delete cascade
	,Column_Number int 
	,primary key clustered(jobs_job_number, column_number))
go
create table dbo.[ROWS]
	(JOBS_Job_Number int references jobs (job_number) on delete cascade
	,Row_Number int 
	,primary key clustered(jobs_job_number, row_number))
go
create table dbo.GRID_DATA
	(JOBS_Job_Number int references jobs (job_number) on delete cascade
	,Column_Number int 
	,Row_Number int
	,Data int
	,primary key clustered (jobs_job_number, column_number, row_number)
	,constraint fk_1 foreign key (jobs_job_number, row_number) references [rows] (jobs_job_number, row_number)
	,constraint fk_2 foreign key (jobs_job_number, column_number) references [columns] (jobs_job_number, column_number)
	)
go

quote:

---

Originally posted by TG

Would you get what you need by having cascade deletes between Jobs and all other tables, then have non cascading FK constraints on grid_data to maintain RI? That way your cascade deletes will flow from jobs to all the other tables and RI is still maintained between grid_data and cols and rows. So if a job was deleted the cascade would flow through the model but if someone tried to delete just a job's row or column (that existed in the grid_data) they would recieve a FK constraint violation.

---

quote:

---

Originally posted by samuelclay

Just remember the more control you give to the system, the less control you retain yourself.

---