Constraint Implementation

hi, i have STAFF, BED and WARD tables in a database and am not sure what the best way to implement the following constraints would be... (like DDL, forms or triggers)

*to ensure that a STAFF member has a name
*a STAFF member must belong to either the surgical or clinical department
*a BED must belong to an exisiting ward
*only one member of STAFF should have a Staff_responsibility of 'Clinic Director'

and what would be the reason for the choice as opposed to the alternatives.

any help much appreciated.

you could implement the following constraint in your table, check it out step-by-step:

*to ensure that STAFF memeber has a name
*a STAFF memeber must belong to either surgical or clinical department

CREATE TABLE STAFF ( name VARCHAR2(30) NOT NULL,
department VARCHAR2(8) CONSTRAINT ck_dept CHECK(department IN ('Surgical', 'Clinical'),
....
....
....
)
/

*a BED must belong to an exisiting ward
CREATE TABLE ward (ward_code VARCHAR2(5) CONSTRAINT pk_ward PRIMARY KEY)
/

CREATE TABLE bed (bed_no number(3) NOT NULL,
ward_code VARCHAR2(5) CONSTRAINT fk_ward REFERENCES ward(ward_code))
/

*only one member of STAFF should have a Staff_responsibility of 'Clinic Director'

To solve this problem you need to create a trigger on this table to check the validity of data.

CREATE OR REPLACE TRIGGER check_staff_resposibility
BEFORE INSERT OR UPDATE ON staff
FRO EACH ROW
WHEN (new.staff_responsibility = 'Clinic Director')
DECLARE
count_down number;

SELECT count(*) INTO count_down
FROM staff
WHERE staff_responsibility = 'Clinical Director';

IF (count_down>=1) THEN
raise_application_error(-20701, 'Clinical Director already exist');
END IF;
END;
/

Please make the following changes in the trigger:

CREATE OR REPLACE TRIGGER check_staff_resposibility
BEFORE INSERT OR UPDATE ON staff
FRO EACH ROW
DECLARE
count_down number;
BEGIN

SELECT count(*) INTO count_down
FROM staff
WHERE staff_responsibility = 'Clinical Director';

IF (count_down>=1 AND :new.staff_responsibility='Clinical Director') THEN
raise_application_error(-20701, 'Clinical Director already exist');
END IF;
END;
/

thanks, but the main thing i need is an explanation of the best type of implementation to use for each of the constraints (DDL, forms or triggers) and the reasons why, as opposed to the actual triggers themselves.

The best way to impelemnt it in the DDL.

ahh i see, thanks. what would the triggers be for these two constraints then..?

* changes to STAFF records should not be allowed on weekends
* a STAFF member's Date_left should not be before the Date_started

considering the STAFF table is..

Staff_id varchar2(10) NOT NULL,
Name varchar2(35) NOT NULL,
address VARCHAR2(50) NOT NULL,
teleno varchar2(10) NOT NULL,
NIno varchar2(10) NOT NULL,
Notes VARCHAR2(500) NOT NULL,
Department Varchar2(20)
Staff_responsibility Varchar2(20)
Date_started Date,
Date_left Date,
CONSTRAINT STAFF_PK
PRIMARY KEY (Staff_id)

thanks again.

For STAFF member's Date_left should not be before the Date_started, make the following changes in your table:

CREATE TABLE staff (
Staff_id varchar2(10) NOT NULL,
Name varchar2(35) NOT NULL,
address VARCHAR2(50) NOT NULL,
teleno varchar2(10) NOT NULL,
NIno varchar2(10) NOT NULL,
Notes VARCHAR2(500) NOT NULL,
Department Varchar2(20) ,
Staff_responsibility Varchar2(20) ,
Date_started Date,
Date_left Date,
CONSTRAINT STAFF_PK
PRIMARY KEY (Staff_id),
CONSTRAINT ck_date CHECK (date_left>=date_started))
/

* changes to STAFF records should not be allowed on weekends

You need again to create a trigger or modified the previous trigger and add the following check condition in it:


IF (LTRIM(RTRIM(TO_CHAR(TO_DATE(sysdate,'DD-MON-YY'),'Day'))) IN ('Monday','Tuesday','Wednesday','Thursday','Friday')) THEN
raise_application_error(-20707,'Modification on this table cannot be performed today...');
END IF;

thanks again.

i'm having trouble with another constraint regarding the STAFF table. the constraint is..

*if a STAFF record is deleted, an audit record should be written to an audit table consisting of relevant information, eg. date of deletion.