I am trying to prevent the deletion of records in a child table if the
record in the parent table has a date in a date field.

Every thing is being deleted from the w_schActivityPatients table
regardless if there is or isn't a date in the Parent
table(w_schActivityCalendar) printdate field.

I tried the following but no joy.


CREATE TRIGGER trgNullPrint
ON w_schActivityPatients
For DELETE
AS

If exists(select * from deleted as d inner join w_schActivityCalendar
as t on d.[fk] = t.[Pk])
    begin
    DELETE w_schActivityPatients
    where exists(SELECT  * FROM w_schActivityCalendar INNER JOIN
                  w_schActivityPatients ON
w_schActivityCalendar.ActCalID=w_schActivityPatients.ActCalID
        WHERE    (w_schActivityCalendar.PrintDate IS NULL))


This table is getting updated from a disconnected recordset in a VB6
application but that shouldn't matter...should It?

Any help would be appreciated!

Triggers are fired AFTER the original deletion, so your deletion code
will fire additionally, that´s why records are getting deleted. For
your purpose you have to use a "instead of" trigger, look in the BOL
for more information.

HTH, jens Suessmeyer.

Alternatively, a trigger can rollback the original transaction that fired
the trigger.
You can check for the date, and if found, issue a rollback.

Triggers are fired AFTER the original deletion, so your deletion code
will fire additionally, that´s why records are getting deleted. For
your purpose you have to use a "instead of" trigger, look in the BOL
for more information.

HTH, jens Suessmeyer.

Outer Join and WHERE clause strange behavior
MINUS operation between table date ranges, is my algorithm sound?
generic audit trail best practice questions
append a string to all the values in a column SQL
Extracting from linked tables into pivot