Re: Trigger on Self-Referencing table

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: oj (nospam_ojngo_at_home.com)
Date: 01/24/05 

Date: Mon, 24 Jan 2005 00:13:17 -0800

Instead Of trigger is fired before the insert/update/delete on the base
table, thus, it's not cascaded. This is by design. See this article for more
info.

http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_08_49kj.asp 

-- 
-oj
"tom d" <tom d@discussions.microsoft.com> wrote in message 
news:9AFF0011-6422-4FED-972E-7061740F99A9@microsoft.com...
> Let's say I have an employee table like this:
>
> empid     name     mgrid
> 1            A           2
> 2            B           1
> 3            C           2
> 4            B           2
> 5            E           3
>
>
> I have a self FK on mgrid referencing empid.
>
> Question is how do I create a trigger that when I delete empid 2, it 
> should
> automatically delete all of it subordinates. In this case, it should 
> delete
> all employees in this table.  I did try enabling Recurent trigger with
> "instead of" trigger with no luck.
>
> Please help me with the triggers.
> Thanks,
> Tony

Relevant Pages

  • Trigger on Self-Referencing table
    ... Let's say I have an employee table like this: ... I have a self FK on mgrid referencing empid. ... Question is how do I create a trigger that when I delete empid 2, ...
    (microsoft.public.sqlserver.programming)
  • Re: Trigger on Self-Referencing table
    ... >I have a self FK on mgrid referencing empid. ... >Question is how do I create a trigger that when I delete empid 2, ... >all employees in this table. ... Add to the temp table all employees that are managed by one of the ...
    (microsoft.public.sqlserver.programming)
  • Re: Question on Insert Trigger
    ... conditions for filtering are more complex. ... after INSERT ON employee ... Dbms_output before rollback is exceuted, then rollback is exceuted, ... What I am asking is there a way to do filtering in the trigger if it ...
    (comp.databases.oracle.server)
  • Re: Question on Insert Trigger
    ... I wrote a trigger like following. ... after INSERT ON employee ... I was hoping that when I do rollback that row will not be inserted. ... Puget Sound Oracle Users Groupwww.psoug.org-Hide quoted text - ...
    (comp.databases.oracle.server)
  • Re: SQL 2000 multi-row trigger question
    ... Ts and employee). ... CREATE TRIGGER trga_u_T1 ON T1 AFTER UPDATE ... I have> yet to find sample trigger code that handles a multi-row update in which a> primary key field is being updated. ... > to each so that they can be joined together during an update query. ...
    (microsoft.public.sqlserver.programming)