CODEWITHSUNDEEP
sqloracle-databaseselecttriggers
UrsulRosu
UrsulRosu

Reputation: 527

Oracle trigger with select inside that returns multiple results

I have the trigger 

CREATE OR REPLACE TRIGGER my_trigger
AFTERE DELETE OR INSERT OR UPDATE ON my_table
FOR EACH ROW
DECLARE 
  V_PROJECT_ID VARCHAR2(10);
BEGIN
  SELECT PJ_ID INTO V_PROJECT_ID FROM PROJECT_ROLES_GROUPS
  WHERE GRP_ID = :OLD.GRP_ID;

  UPDATE PROJECTS SET TOCUHED = 1 WHERE ID = V_PROJECT_ID;
END;

but the select statement inside the trigger returns multiple values.

How should I handle this case?

Upvotes: 1

Views: 1402

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269683

How about using in for the query instead of a variable?

BEGIN
  UPDATE PROJECTS
      SET TOUCHED = 1
      WHERE ID IN (SELECT PJ_ID 
                   FROM PROJECT_ROLES_GROUPS
                   WHERE GRP_ID = :NEW.GRP_ID
                  );
END

Upvotes: 2

Related Questions