| constraint [message #10233] |
Fri, 09 January 2004 04:39  |
gr
Messages: 5
Registered: January 2002
|
Junior Member |
|
|
hi all.I have created the following tables:
CREATE TABLE CAR
(BRANDNAME CHAR(10) NOT NULL,
NAME CHAR(10) NOT NULL,
CUBICS INT,
MADEYEAR INT,
PRIMARY KEY(BRANDNAME,NAME));
CREATE TABLE AUTOMOBILE
(LICENCE CHAR(7) NOT NULL,
BRANDNAME CHAR(10) NOT NULL,
NAME CHAR(10) NOT NULL,
COLOUR VARCHAR(15),
YEAR INT,
PRIMARY KEY(LICENCE),
FOREIGN KEY(BRANDNAME,NAME) REFERENCES CAR(BRANDNAME,NAME)
ON DELETE SET NULL);
MADEYEAR is the date when this car was made and YEAR is the date when the LICENCE was issued.I wanna make sure that when a new LICENCE is inserted in the AUTOMOBILE table the date when it was issued is not
before the date the car was made(MADEYEAR).
I tried the following using a trigger but it gave me a "not enough privileges error":
CREATE TRIGGER LICENCECHECK
BEFORE INSERT OR UPDATE ON AUTOMOBILE
FOR EACH ROW
BEGIN
IF(new.YEAR > (SELECT MADEYEAR FROM CAR WHERE (AUTOMOBILE.BRANDNAME=CAR.BRANDNAME) AND (AUTOMOBILE.NAME=CAR.NAME)) THEN
RAISE_APPLICATION_ERROR(-20000,"NO LOWER ALLOWED");
END IF;
END;
.
run; I tried this all in sqlplus.I am a complete newby in sql so this might be completely wrong.If you have any suggestion on how i can do this i would be very glad to hear them.
Thanx.
|
|
|
|
| Re: constraint [message #10234 is a reply to message #10233] |
Fri, 09 January 2004 08:02  |
schill
Messages: 1
Registered: January 2004
|
Junior Member |
|
|
CREATE or REPLACE TRIGGER LICENCECHECK
BEFORE INSERT OR UPDATE ON AUTOMOBILE
FOR EACH ROW
declare
n_madeyear car.madeyear%type;
BEGIN
SELECT MADEYEAR
INTO n_madeyear
FROM CAR, automobile
WHERE AUTOMOBILE.BRANDNAME=CAR.BRANDNAME
AND automobile.NAME=CAR.NAME;
IF (:new.YEAR > n_madeyear) THEN
RAISE_APPLICATION_ERROR(-20000,'NO LOWER ALLOWED');
END IF;
exception
when others then dbms_output.put_line(sqlerrm);
END;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
