How to use trigger between two tables in Oracle SQL?

Question

Imagine this two tables

CREATE TABLE A
(
    idA       smallint primary key,
    idP       smallint ,
    ...
);

CREATE TABLE P
(
    idP       smallint primary key,
    Type      char , (ex:A, B, C)
    ...
);

I would like to behaviour like if in table A doesnt have the same type as in table P, is not for example a worker

CREATE OR REPLACE TRIGGER VERIFYTYPE 
BEFORE INSERT OR UPDATE ON A 
FOR EACH ROW
BEGIN
    IF((SELECT tp.Type
      FROM P tp
      WHERE tp.idP=:new.idP)!='W')
      THEN RAISE_APPLICATION_ERROR(-20001, 'That is not a worker');
    END IF;
END;

I believe somethin is wrong

Use VARCHAR2 instead of CHAR, it will make your life easier. You need to tell us what "Something is wrong" means....error messages, wrong outcome??? — kevinskio
– kevinskio, Commented Dec 22, 2015 at 17:54
What is it that you are trying to accomplish? Just from what you have here, it looks more like you want a foreign key constraint or check constraint instead of a trigger. Triggers are usually used when you need to insert, update, or delete data in 1..n other tables. — gmiley
– gmiley, Commented Dec 22, 2015 at 18:01
the problem is not the datatype, is the trigger, the outcome about my trigger is this: Error(5,7): PLS-00103: Encountered the symbol "SELECT" when expecting one of the following.. — user3157136
– user3157136, Commented Dec 22, 2015 at 18:02

kevinskio · Accepted Answer · 2015-12-22 18:01:35Z

1

IN PL/SQL you need to select into a variable. But gmiley is correct, a foreign key or virtual column with an index is a better solution.

CREATE OR REPLACE TRIGGER VERIFYTYPE 
BEFORE INSERT OR UPDATE ON A 
FOR EACH ROW
v_type CHAR(3);
BEGIN
SELECT tp.Type
INTO v_type
FROM P tp
WHERE tp.idP=:new.idP;

    IF v_type != 'W'
      THEN 
      RAISE_APPLICATION_ERROR(-20001, 'That is not a worker');
    END IF;
END;

answered Dec 22, 2015 at 18:01

kevinskio

4,5711 gold badge27 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kevinskio Over a year ago

@user3157136 if this answers your question you could accept it

Pavel Gatnar · Accepted Answer · 2015-12-22 18:01:54Z

0

You have to:

rename the column 'type' as this is a keyword and you'll get a lot of troubles when using it
add variable x to the declare section
use SELECT col INTO x construct
use x in the IF construct

answered Dec 22, 2015 at 18:01

Pavel Gatnar

4,0532 gold badges22 silver badges29 bronze badges

Collectives™ on Stack Overflow

How to use trigger between two tables in Oracle SQL?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related