Re: How to create a database trigger

From: Inna <mednyk_at_my-deja.com>
Date: Tue, 09 Jan 2001 17:34:06 GMT
Message-ID: <93fi2e$vu0$1@nnrp1.deja.com>

In article <93f6st$mj5$1_at_nnrp1.deja.com>,   xbguan_at_yahoo.com wrote:
> Your question is not very clear to me. It will help if you include
 your
> table structures.
>
> I don't understand why you have a empid in dept table.
> Why you can't create a forgein key?
>
> Is you really need a trigger, try this:
>
> CREATE or REPLACE TRIGGER Del_Dept
> BEFORE DELETE ON EMP
> FOR EACH ROW
> BEGIN
> DELECT
> FROM dept
> WHERE empid = :old.id;
> END Del_Dept;
>
> Good luck!
> Peter
>
> In article <93f4tl$l40$1_at_nnrp1.deja.com>,
> Inna <mednyk_at_my-deja.com> wrote:
> >
> >
> > In article <3A59ABC7.E6D_at_yahoo.com>,
> > connor_mcdonald_at_yahoo.com wrote:
> > > Inna wrote:
> > > >
> > > > Hello everyone.
> > > > Started creating database triggers and realized that know
 nothing
 about
> > > > it. Please help me in this problem. I have two tables dept and
 emp.
> > > > Have primary key ID in emp table and empid in dept, but I can
 not
 make
> > > > it foreign. So I need a trigger <Before delete> on table emp,
 which
> > > > going to delete record in dept table. And I don't know how to do
 that,
> > > > because my books are not so good, there is no syntax there.
> > > > Thank you in advance.
> > > > --
> > > > Inna.Junior programmer.
> > > >
> > > > Sent via Deja.com
> > > > http://www.deja.com/
> > >
> > > Check out the ON DELETE CASCADE option when defining a constraint.
> > > Since you're new, jump to technet.oracle.com and register (its
 free).
> > > Then you can have a wander through the documentation.
> > >
> > > HTH
> > > Connor
> > > --
> > > ===========================================
> > > Connor McDonald
> > > http://www.oracledba.co.uk (mirrored at
> > > http://www.oradba.freeserve.co.uk)
> > >
> > >
> >
> > Thank you. But I know about delete cascade. I can not make empid
> > foreign key. That is why I need to know how to create a trigger for
> > that.
> >
> > --
> > Inna.Junior programmer.
> >
> > Sent via Deja.com
> > http://www.deja.com/
> >
>
> Sent via Deja.com
> http://www.deja.com/
>

Thank you very much for your help.
table EMP:
name
address
ID

---

table DEPT:
ID
EMPID
address

---

It is going to take long time to explain whole table structure. It was just the closest example. But you helped me a lot. I really can not have
EMPID as foreign key, Because some times I need to have in DEPT EMPID=- 1, that is not existing in EMP table. And I have to create trigger which does not allow to delete record from DEPT if there is corresponding record in EMP. And trigger that you have already sent me, but I think It must be some validation: If we have this empid in dept table.
 Thank you very much for you help. It is very useful for me.

--
Inna.Junior programmer.


Sent via Deja.com
http://www.deja.com/

Received on Tue Jan 09 2001 - 11:34:06 CST