Trigger Problem (Delete Trigger) 
Author Message
 Trigger Problem (Delete Trigger)

Hi,

I created a delete trigger for a table called SubCatDta on my SqlServer box.
Unfortunately, it never fires.

The text of it is below. I have other triggers for INSERT and UPDATE, and
those seem to work fine. The only reason for the PRINT 'Hello' statement was
to see if the trigger was firing or not (it wasn't). The other PRINT was
just for debugging.

Thanks for any help.

- Steve Harclerode

-----------------------

CREATE TRIGGER SubCatDta_OnDelete ON SubCatDta
FOR DELETE
AS


PRINT 'Hello'


 FROM SubCatDta AS S, Deleted AS D
 WHERE S.SubCatId = D.SubCatId


DELETE FROM RelatedCats




Sun, 02 Nov 2003 06:06:54 GMT
 Trigger Problem (Delete Trigger)

If you have set up referential integrity (foreign keys) between the tables,
the trigger will not fire if you attempt to delete a record from the parent
that has related records in the child. You need to remove the foreign key
contraint and do it all through triggers, or upgrade to SQL 2000 and avail
yourself of cascading foreign key constraints.

--
Mary Bray
MCT, MCSE, MCDBA, MCSD


Quote:
> Hi,

> I created a delete trigger for a table called SubCatDta on my SqlServer
box.
> Unfortunately, it never fires.

> The text of it is below. I have other triggers for INSERT and UPDATE, and
> those seem to work fine. The only reason for the PRINT 'Hello' statement
was
> to see if the trigger was firing or not (it wasn't). The other PRINT was
> just for debugging.

> Thanks for any help.

> - Steve Harclerode

> -----------------------

> CREATE TRIGGER SubCatDta_OnDelete ON SubCatDta
> FOR DELETE
> AS


> PRINT 'Hello'


>  FROM SubCatDta AS S, Deleted AS D
>  WHERE S.SubCatId = D.SubCatId


> DELETE FROM RelatedCats





Sun, 02 Nov 2003 06:57:44 GMT
 Trigger Problem (Delete Trigger)
Thanks for the info. The DBA in my office didn't know that -- it's time to
send him back to school, I guess.

- Steve Harclerode


Quote:
> If you have set up referential integrity (foreign keys) between the
tables,
> the trigger will not fire if you attempt to delete a record from the
parent
> that has related records in the child. You need to remove the foreign key
> contraint and do it all through triggers, or upgrade to SQL 2000 and avail
> yourself of cascading foreign key constraints.

> --
> Mary Bray
> MCT, MCSE, MCDBA, MCSD



Sun, 02 Nov 2003 07:47:34 GMT
 Trigger Problem (Delete Trigger)
A couple of issues here.


trying to join the deleted table to the row that no longer exists
in your base table. The inner join returns nothing (at least when I try it).

2) Triggers as a rule have to be able to handle sets of changes.
If someone issues a delete statement that affects more than
one row, the trigger only fires once, so the code has to be
able to take the desired action(s) on all of the affected rows.
Using a variable this way you have to loop through the deleted table,
or else use something like this:

delete from relatedcats
where (parentlevel = 1
            and exists (select '' from deleted d where relatedcats.parentid
= d.subcatid))
  or (childlevel = 1
            and exists (select '' from deleted d where relatedcats.childid =
d.subcatid))

Tim


Quote:
> Hi,

> I created a delete trigger for a table called SubCatDta on my SqlServer
box.
> Unfortunately, it never fires.

> The text of it is below. I have other triggers for INSERT and UPDATE, and
> those seem to work fine. The only reason for the PRINT 'Hello' statement
was
> to see if the trigger was firing or not (it wasn't). The other PRINT was
> just for debugging.

> Thanks for any help.

> - Steve Harclerode

> -----------------------

> CREATE TRIGGER SubCatDta_OnDelete ON SubCatDta
> FOR DELETE
> AS


> PRINT 'Hello'


>  FROM SubCatDta AS S, Deleted AS D
>  WHERE S.SubCatId = D.SubCatId


> DELETE FROM RelatedCats





Sun, 02 Nov 2003 10:49:19 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. INSERT trigger versus UPDATE/DELETE trigger

2. instead of delete trigger delete data from table using execute and temporary table for deleted

3. Triggers, Triggers and more Triggers

4. Linked-Server (Trigger in a Trigger Problem)

5. Delete trigger not updating rows using delete from

6. delete trigger or cascade delete, which one??

7. Delete trigger shows nothing in Deleted table

8. HELP! Creating delete trigger for cascade delete

9. No INSTEAD OF DELETE triggers for tables with ON DELETE CASCADE constraints

10. Trigger: delete dependent records when deleting a row

11. ON DELETE CASCADE - does it fire delete trigger?

12. How to delete from a delete trigger


 
Powered by phpBB® Forum Software