SELECT in a CHECK ? With a trigger?

Question

I have difficulties to grasp the trigger concept, or to be perfectly clear, the way to use it in my case.

Here's my CREATE so far :

CREATE TABLE Station 
( 
nomStation VARCHAR2(100), 
capacite NUMBER(5) NOT NULL, 
lieu VARCHAR2(100) NOT NULL, 
region VARCHAR2(100), 
tarif NUMBER(6,2) DEFAULT 0, 
PRIMARY KEY (nomStation), 
CONSTRAINT UC_lieu_region UNIQUE (lieu, region), 
CONSTRAINT regionUnique CHECK ( region='Ocean Indien' OR 
                                region='Antilles' OR 
                                region='Europe' OR 
                                region='Ameriques' OR 
                                region='Extreme Orient') 
); 

CREATE TABLE Activite 
( 
nomStation VARCHAR2(100), 
libelle VARCHAR2(100), 
prix NUMBER(6,2) DEFAULT 0, 
CONSTRAINT PK_nomStation_libelle PRIMARY KEY (nomStation, libelle), 
CONSTRAINT FK_nomStation FOREIGN KEY (nomStation) REFERENCES Station(nomStation) ON DELETE CASCADE 
);

What I want to achieve is a constraint on Station.tarif and Activite.prix .

Activite.prix < Station.tarif && Activite.prix > 0

So naturally I tried this :

CONSTRAINT CH_prix CHECK (prix<(SELECT tarif FROM Station)),

But I get the subquery error. Now I understand that I have the same issue as this topic : Using subquery in a Check statement in Oracle

Which is cool, but unfortunately, I'm a rookie and I don't see how a trigger can help me…

Anyone?

So, is this a school assignment or a production issue? If it's school, did you ask the teacher/instructor first? If it is production, shouldn't someone more experienced do this? — , Dec 11 '16 at 15:37
It's a school assignment. It's not going to be, mmm I dont know how to say that in English, it's not cheating if that's your question. This won't be part of my final grade or note or whatever. We have to find the solution by ourselves, including with the help of stackoverflow. I know that the solution is based on triggers, I just dont understand how… — petaire, Dec 11 '16 at 15:47
Hi - no, I didn't mean this is cheating. What I meant was, whenever students have questions, they can ask the teacher. (I was a teacher and I never understood why students would use any other means possible except for asking the teacher.) Did you study triggers in class yet? — , Dec 11 '16 at 15:49
Yes we did. But it's night classes, and I have just access to some powerpoints and work sessions, not the actual course. The powerpoints shows just an example of trigger's syntaxes, so it's not really big help for me. Actually I think it's a great way of teaching. I've learned Java and Swift by myself, and I've never had a "mentor" to solve magically my issues, I always had to look for the solutions. I'm not asking for that magic solution, just a solution that I can decrypt to see why a trigger can compare two rows in different tables, where CHECK can't. — petaire, Dec 11 '16 at 15:55

score 1 · Accepted Answer · answered Dec 11 '16 at 15:52

1

You can create a trigger like this.

Create or replace trigger tr_activite_prix
Before insert or update of prix on activite for each row
is
v_tarrif station.tarrif%type;
Begin
    Select tarrif into v_tarrif
    From station
    Where nomstation = :new.nomstation;
    If :new.prix >= v_tarrif then
        raise_application_error(-20001,'Invalid prix');
    End if;
End;

answered Dec 11 '16 at 15:52

Gurwinder Singh

38,557
6
51
76

Use underscore in your trigger name instead of '-' and use the `for each row` part. Other wise you cant use :new and :old – Gurwinder Singh Dec 11 '16 at 17:11
(I replied to that in a new answer for code lisibility) – petaire Dec 11 '16 at 17:12
Fundamental problems in your trigger - you are not using `for each row` so you cant use :new and :old. Second, why are you selecting from activite table. You already have that row in :new pseudo table. Third, rollback will never be called after `raise_application_error` – Gurwinder Singh Dec 11 '16 at 17:18

SELECT in a CHECK ? With a trigger?

1 Answers1

Linked