Oracle Trigger Syntax Using Subquery

Question

I'm trying to write a Trigger in Oracle Syntax which, upon entering a line into a particular table, checks that both values entered belong to some classification that is held in another table. My initial thought was to have a constraint on the table that included a subquery but Oracle doesn't seem to like that.

The select query I have written in the below works - but I'm not sure how to put it into a trigger - but essentially I need the trigger to ensure that EW1.OrgId and EW2.OrgId are the same. Any help is appreciated!

CREATE TABLE Organisation (
OrgId INTEGER PRIMARY KEY,
Name VARCHAR(40) NOT NULL
);

CREATE TABLE Person  (
PersonId INTEGER PRIMARY KEY,
FirstName VARCHAR(45) NOT NULL,
LastName VARCHAR(45) NOT NULL
);

CREATE TABLE Employee (
PersonId INTEGER PRIMARY KEY REFERENCES PERSON (PersonId) ON DELETE CASCADE
);

CREATE TABLE Manager (
PersonId INTEGER PRIMARY KEY REFERENCES PERSON (PersonId) ON DELETE CASCADE
);

CREATE TABLE EnlistedWith (
OrgId INTEGER REFERENCES ORGANISATION (OrgId) ON DELETE CASCADE,
PersonId INTEGER REFERENCES PERSON (PersonId) ON DELETE CASCADE,
PRIMARY KEY(OrgId,PersonId)
);

CREATE TABLE SupervisedBy (
EmployeeId INTEGER REFERENCES Employee (PersonId) ON DELETE CASCADE,
ManagerId INTEGER REFERENCES Manager (PersonId) ON DELETE CASCADE,
CONSTRAINT PK_SupervisedBy PRIMARY KEY (EmployeeId, ManagerId)
);

CREATE TRIGGER SupervisorCompany 
AFTER INSERT ON SupervisedBy
   FOR EACH ROW
     BEGIN
       declare qty INTEGER := 0;
            BEGIN
            SELECT COUNT (*) into qty
        FROM SupervisedBy SB
        INNER JOIN EnlistedWith EW1 ON SB.ManagerId = EW1.PersonId
        INNER JOIN EnlistedWith EW2 ON SB.EmployeeId = EW2.PersonId
        and EW1.OrgId <> EW2.OrgId
        IF qty <> 0
        then Raise_Error (1234567, 'Manager and Employee are not Enlisted with same Organisation');
            END IF;
        END;
END;

Kaushik Nayak · Accepted Answer · 2018-05-10 06:55:48Z

1

You can refer to the columns of the owner of a Trigger using :NEW / :OLD. So, your Trigger could be re-written as

CREATE OR REPLACE TRIGGER supervisorcompany AFTER 
INSERT 
ON supervisedby FOR EACH ROW 
 DECLARE qty INTEGER := 0; 
BEGIN 
  SELECT count (*) 
  INTO   qty 
  FROM   enlistedwith ew1 
  WHERE  ew1.personid = :NEW.managerid 
  AND    EXISTS 
         ( 
                SELECT 1 
                FROM   enlistedwith ew2 
                WHERE  ew2.personid = :NEW.employeeid 
                AND    ew1.orgid <> ew2.orgid ) ;
IF qty <> 0 THEN 
   raise_application_error (1234567, 'Manager and Employee are not Enlisted with same Organisation');
END IF; 
END;
/

edited May 10, 2018 at 6:55

answered May 10, 2018 at 4:11

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

4 Comments

Nic Over a year ago

Thanks for responding! I've given this a shot, and unfortunately I'm still getting an error. I'm currently only able to run this in Oracle Live - the error that I'm getting is the same as that for the solution above Error at Line: 15, which just seems to be the last line...

Kaushik Nayak Over a year ago

@Nic : Please edit your question and add sample data or DDL/ DMLs which you used to create the tables in Live sql.

Kaushik Nayak Over a year ago

@Nic : Updated my answer, check now. Remember in Live sql, to see the actual error, run this query : select * FROM user_errors; You were using Raise_Error - it should be raise_application_error

Nic Over a year ago

Thankyou so much!

sticky bit · Accepted Answer · 2018-05-10 03:53:11Z

0

There's some guessing involved... Maybe something like this

CREATE TRIGGER SupervisorCompany 
               AFTER INSERT
               ON SupervisedBy
                  FOR EACH ROW
BEGIN
  IF (SELECT OrgId
             FROM EnlistedWith
             WHERE PersonId = New.ManagerId)
     <>
     (SELECT OrgId
             FROM EnlistedWith
             WHERE PersonId = New.EmployeeId) THEN
    RAISE_ERROR(1234567, 'Manager and Employee are not Enlisted with same Organisation');
  END IF;
END;

is what you want? It checks if the OrgId of the row where the PersonId equals the newly entered ManagerId is the same as the one for the newly entered EmployeeId. If not, your error is raised.

(Untested, as no DDL for the tables were provided.)

answered May 10, 2018 at 3:53

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

1 Comment

Nic Over a year ago

Thankyou for responding! - I've tried this and I'm still getting an error. I'm currently only able to run this in Oracle Live so the error is Error at Line: 15, which just seems to be the END;

Collectives™ on Stack Overflow

Oracle Trigger Syntax Using Subquery

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related