Re: writing a Delete trigger on a table for cascading effect

From: Eric Sabine (mopar41_at_hyotyt_mail_nounderscores.com)
Date: 07/23/04 

Date: Fri, 23 Jul 2004 10:30:55 -0400

"But our developers out here do not beleive in primary key and foreign key
constraints so putting constraints on tables is ruled out"
LOL

OK, use this

create trigger on TABLENAME for delete -- note the inclusion of delete
only, not insert or update.
...

You get a trigger that fires on a delete only.

hth
Eric

Rodger wrote:
> Thanks Eric
>
> But our developers out here do not beleive in primary key and foreign
> key constraints so putting constraints on tables is ruled out, if you
> have a small code for delete trigger where it just picks up the row
> deleted and not the row updated, because in case of update the
> deleted table also has a value which is i dont want
>
> thanks
>
>
>
> "Eric Sabine" wrote:
>
>> Yes a trigger would work. You would query on the virtual table
>> called DELETED from within the trigger. But better is to use
>> referential integrity and allow cascading deletes. For example some
>> air code
>>
>>
>> create table parent( id int identity primary key)
>> create table child (col1 int not null, col2 int not null constraint
>> FK_CHILD_PARENT_ID foreign key references parent ( id ) on update no
>> action on delete cascade)
>>
>> hth
>> Eric
>>
>>
>> Rodger wrote:
>>> Hi Guys
>>>
>>> I want to write a delete trigger on a table which will delete rows
>>> on a second table, I have written triggers for insert and update
>>> but not tried delete, would appreciate if somebody can give me a
>>> small sample code for any two tables. for delete operation
>>>
>>> Thanks

Relevant Pages

  • Re: Trigger Verzeichnis
    ... Und dann helfen nur Trigger, ... SP-Zugriffsschicht vom Client her eingebaut hat, ... > CONSTRAINTs, CHECKs und DEFAULTs arbeiten auf einer anderen, niederen ... Auch ich gebe den FOREIGN KEY CONSTRAINTs gegenüber den TRIGGERn den ...
    (microsoft.public.de.sqlserver)
  • Re: Constraints: disable / enable constraints issue
    ... You can use ALTER TABLE to disable a foreign key. ... primary key, since it uses a unique index. ... Columnist, SQL Server Professional ... constraints, in particular primary and foreign keys? ...
    (microsoft.public.sqlserver.server)
  • RE: Having a stored procedure copy tables & also preserve indexing
    ... FOREIGN KEY, CHECK, NOT NULL, DEFAULT constraints ... INTO <tablename> doesn't create any of the PRIMARY KEY, UNIQUE, ...
    (microsoft.public.sqlserver.mseq)
  • Re: Cascade Delete within Self Referencing Table
    ... > Since the constraints are not checked individually for each row remove, ... > but once after all rows are removed, the foreign key is not violated ... sqlserver but will be modified in sqlserver within the next few months, ... this trigger will be useful in the future. ...
    (microsoft.public.sqlserver.programming)
  • RE: Strange error
    ... Is the dataset a strong typed dataset? ... Are there any constraints like ... primary key or foreign key defined in the dataset? ...
    (microsoft.public.dotnet.languages.csharp)