Re: sql plus table constraints

That's exactly what I needed - thanks a million for your help.

Lisa

In article <90dq3l$4n3$1_at_nnrp1.deja.com>,   steveee_ca_at_my-deja.com wrote:
> Hi Lisa,
>
> Okay..you want to make sure that an entry into Department corresponds
 to
> an entry in Employee where the role id is '2'?
>
> How about a trigger on the Department table like this:
>
> CREATE OR REPLACE TRIGGER is_doctor
> BEFORE INSERT OR UPDATE ON deptartment
> FOR EACH ROW
>
> DECLARE
> V_ROLE VARCHAR2(2);
>
> BEGIN
> SELECT ROLEID
> INTO V_ROLE
> FROM EMPL
> WHERE RSI = :new.mgrrsi;
>
> IF V_ROLE <> '2' THEN
> RAISE_APPLICATION_ERROR(-20505,'Manager must be a Doctor');
> END IF;
>
> EXCEPTION
> WHEN NO_DATA_FOUND THEN
> RAISE_APPLICATION_ERROR(-20506,'Not a valid RSI');
> END;
>
> Hopefully this works for you?
>
> Best luck,
> Steve
>
> In article <90dcn0$s59$1_at_nnrp1.deja.com>,
> lisamariajean_at_my-deja.com wrote:
> > I'm an SQL Plus novice and have come across the
> > following problem.
> >
> > I would appreciate it if someone could help me or
> > let me know if there is a more appropriate forum
> > for such a query.
> >
> > I have 3 tables Employee, Department and Role.
> >
> > In the Department Table I have the Department
> > Manager Social Security No in a column called
> > MgrRSI. This column is a foreign key to the RSI
> > column (Primary Key) in the Employee table.
> >
> > Also within the Employee table I have a column
> > called RoleID which is a foreign key to the
> > column entitled RoleNo of the Role table. In
> > other words, each employee has a role to play
> > which is described in the Role table.
> >
> > In short, each department has a manager who is an
> > employee. Each employee has a role number e.g. 1
> > for administrator, 2 for doctor etc.
> >
> > I want to set up a constraint that checks that
> > the MgrRSI number entered by the user relates to
> > an employee who is a doctor. Or, to avoid having
> > to involve a third table in the query, simply to
> > ensure that the MgrRSI relates to an employee who
> > does role no 2.
> >
> > I've tried doing all sorts of check constraints
> > but keep getting errors. I've seen in some
> > versions of SQL you can do a Create Assertion
> > command that looks like it would do the trick but
> > it doesn't seem to recognise it in SQL Plus.
> >
> > Please help!!
> >
> > Lisa
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Dec 03 2000 - 18:44:23 CST