Re: sql plus table constraints

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. Received on Sun Dec 03 2000 - 09:50:14 CST