Oracle: Using subquery in a trigger

Question

How can I work around the Oracle's limitation of not allowing subqueries in triggers.

Here's an example trigger I'm trying to create, but am unable to because I can't use a subquery.

CREATE OR REPLACE TRIGGER trigger_w_subquery
AFTER UPDATE OR INSERT ON project_archiving
FOR EACH ROW WHEN (old.archiving_status <> new.archiving_status
  AND new.archiving_status = 1
  AND (SELECT offer FROM projects WHERE projnum = :new.projnum) IS NULL
)
BEGIN
  INSERT INTO offer_log (offer, status, date)
  VALUES (null, 9, sysdate);
END;

Tony Andrews · Accepted Answer · 2009-05-26 09:09:08Z

11

This trigger would do it:

CREATE OR REPLACE TRIGGER trigger_w_subquery
AFTER UPDATE OR INSERT ON project_archiving
FOR EACH ROW WHEN (old.archiving_status <> new.archiving_status
  AND new.archiving_status = 1
)
DECLARE
  l_offer projects.offer%TYPE;
BEGIN
  SELECT offer INTO l_offer 
  FROM projects 
  WHERE projnum = :new.projnum;

  IF l_offer IS NULL THEN
    INSERT INTO offer_log (offer, status, date)
    VALUES (null, 9, sysdate);
  END IF;
END;

I have assumed that the select from projects will always find a row; if not it will raise a NO_DATA_FOUND exception that you may need to handle.

answered May 26, 2009 at 9:09

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

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

3 Comments

Gary Myers Over a year ago

Just a warning that, if you use multi-table inserts (inserting into both project and project_archiving), you may end up with a mutating table error. As such it is preferable to put the logic where the original insert is, rather than rely on a trigger.

Haakon Løtveit Over a year ago

Where did the type projects.offer%TYPE come from? Does it refer to the type of the offer in the table projects? Or perhaps more tantalizingly, does anyone have any good resources to recommend for learning this?

Tony Andrews Over a year ago

@HaakonLøtveit docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/…

Justin Cave · Accepted Answer · 2009-05-26 09:08:58Z

6

I expect that you want something like

CREATE OR REPLACE TRIGGER trigger_w_subquery
AFTER UPDATE OR INSERT ON project_archiving
FOR EACH ROW 
WHEN (old.archiving_status <> new.archiving_status
  AND new.archiving_status = 1)
DECLARE
  l_offer projects.offer%TYPE;
BEGIN
  SELECT offer 
    INTO l_offer
    FROM projects 
   WHERE projnum = :new.projnum;

  IF( l_offer IS NULL )
  THEN
    INSERT INTO offer_log (offer, status, date)
      VALUES (null, 9, sysdate);
  END IF;
END;

answered May 26, 2009 at 9:08

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

1 Comment

vipirtti Over a year ago

Sorry, Justin. I can only select one answer as corrent one. This time you were beaten by a simple exception warning :-) The up vote you deserved, never the less.

Jonathan Leffler · Accepted Answer · 2009-05-26 06:25:49Z

2

Can you put the condition into the action (between BEGIN and END) instead of in the 'whether it fires'? Yes, it means that the trigger body might be fired more often - but if it gets you around the problem...

answered May 26, 2009 at 6:25

Jonathan Leffler

760k145 gold badges961 silver badges1.3k bronze badges

3 Comments

Erich Kitzmueller Over a year ago

The cost of executing the subquery is most likely much higher than the overhead for firing a trigger, so I guess it doesn't matter anyway.

vipirtti Over a year ago

Attempted BEGIN IF (SELECT offer FROM projects WHERE projnum = :new.projnum) IS NULL THEN INSERT INTO etc. However, it Oracle throws an error as it encounteres the SELECT in that statement. It just doesn't expect it.

Jonathan Leffler Over a year ago

I'm not sufficiently familiar with PL/SQL to know whether that's a plausible way of writing the code in the trigger. It looks good; other systems that I know would require some sort of assignment from the SELECT (with an INTO clause and a variable), and then check the variable, or something along those lines. There's probably a description of the limitations on triggers in the manuals. Also - can you call a procedure in the action part? If so, maybe that will work for you? (Pass the relevant values as parameters.)

Collectives™ on Stack Overflow

Oracle: Using subquery in a trigger

3 Answers 3

3 Comments

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related