I am trying to prevent the deletion of records in the
w_schActivityPatients table if there is no PrintDate in the
coresponding parent table (w_schActivityCalendar)

The following deletes everything from the w_schActivityPatients table
regardless if there is a printdate in the w_schActivityCalendar table.

<snip>
CREATE TRIGGER trgNullPrint
ON w_schActivityPatients
For DELETE
AS
set nocount on

if exists(select * from deleted as d inner join w_schActivityCalendar
as t on d.actCalID = t.actCalID)
begin
DELETE w_schActivityPatients
where exists(SELECT w_schActivityCalendar.PrintDate,
w_schActivityPatients.ActCalID, w_schActivityPatients.Case_Number
FROM w_schActivityCalendar INNER JOIN
w_schActivityPatients ON
w_schActivityCalendar.ActCalID = w_schActivityPatients.ActCalID
WHERE (w_schActivityCalendar.PrintDate IS NULL))
<snip>

[quoted text, click to view]
Can someone point me in the right direction ?
What an I doing wrong?

[quoted text, click to view]

Hi ActiveX,

With a request like this, there are two possible solutions. You'll have
to choose which one you want. Let's suppose that a user executes a
DELETE statement that affects 5 rows, and one of them has no PrintDate
in the parent table. What do you want to do?

a) (Recommended) Raise an error condition and rollback the entire
transaction, or

b) Prevent the deletion of this single row (either silently or with a
warning message) but allow the other 4 to be deleted.

I recommend AGAINST the second version, since this would violate the
"all-or-nothing" principle of transactional databases.


For the first version, you can use an AFTER DELETE trigger:

CREATE TRIGGER trgNullPrint
ON w_schActivityPatients
AFTER DELETE
AS
SET NOCOUNT ON
IF EXISTS (SELECT *
FROM deleted AS d
LEFT OUTER JOIN w_schActivityCalendar AS t
ON t.actCalID = d.actCalID
WHERE t.PrintDate IS NULL)
BEGIN
RAISERROR ('No PrintDate in parent table -- DELETE operation
canceled', 16, 1)
ROLLBACK TRANSACTION
END
go


For the second (unrecommended) version, an INSTEAD OF DELETE trigger
would be better:

CREATE TRIGGER trgNullPrint
ON w_schActivityPatients
INSTEAD OF DELETE
AS
SET NOCOUNT ON
DELETE FROM w_schActivityPatients
WHERE EXISTS (SELECT *
FROM deleted AS d
INNER JOIN w_schActivityCalendar AS t
ON t.actCalID = d.actCalID
WHERE t.PrintDate IS NOT NULL
AND d.PKColumn = w_schActivityPatients.PKColumn)
go

(Note that you have to replace PKColumn with the name(s) of the
column(s) that form(s) the primary key of the w_schActivityPatients
table).

Note: All the above triggers are untested. See www.aspfaq.com/5006 if
you prefer a tested reply.

--