Re: mutant tables
Hi there,
What you want to achieve is that the rows are not inserted if the first_date > last_date, right?
You can use two different approaches:
1) do it using a view WITH CHECK OPTION:
create view date_view1
as
SELECT *
FROM DATE_TABLE1 t
WHERE t.FIRST_DATE > t.LAST_DATE
WITH CHECK OPTION
;
this is by far the simplest solution. The view checks the rows that are inserted or updated and prevents them from violoating the rule.
2) Create a BEFORE INSERT (and/or UPDATE) trigger. Check your rule and force another constraint to be violated.
Suppose you have a NOT NULL column in your table (best if that is also on of FIRST_DATE or LAST_DATE):
CREATE TABLE DATE_TABLE1(
FIRST_DATE DATE NOT NULL,
LAST_DATE DATE NOT NULL
);
delimiter |
CREATE TRIGGER AGE_OK
BEFORE INSERT ON DATE_TABLE1
FOR EACH ROW
BEGIN
IF NEW.FIRST_DATE > NEW.LAST_DATE THEN
SET NEW.FIRST_DATE := NULL;
SET NEW.LAST_DATE := NULL;
END IF;
END
|
However, this will complain about a NOT NULL constaint not aobut your rule. the view solution will tell you exactly what is the matter.
Subject
Views
Written By
Posted
5701
March 25, 2006 06:59AM
Re: mutant tables
2621
March 26, 2006 03:32PM
2098
March 27, 2006 03:26AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.