PL/SQL: Issue implementing trigger

Question

I'm new at PL/SQL. I'm trying to program a trigger that insert in a table A just if there is an insert in table B (to have an insert in table B, the following select must returns 1).

    SELECT 1
    INTO v_exists 
    FROM TFRG V
    WHERE 
        SUBSTR(:NEW.COD_OBJT, 1, 2) = V.CDP
        AND SUBSTR(:NEW.COD_OBJT, 12, 2) = V.CDS
        AND V.CDSIT = 'V'
        AND V.CDG IN (‘E’,’G’);

The problem is that I don't know how to include this select in my trigger:

CREATE OR REPLACE TRIGGER Trigger_name
-- PL/SQL Block
 AFTER INSERT
 ON B FOR EACH ROW
declare

v_exists NUMBER;

begin

begin
    SELECT 1
    INTO v_exists 
    FROM TFRG V
    WHERE 
        SUBSTR(:NEW.COD_OBJT, 1, 2) = V.CDP
        AND SUBSTR(:NEW.COD_OBJT, 12, 2) = V.CDS
        AND V.CDSIT = 'V'
        AND V.CDG IN (‘E’,’G’);


    INSERT INTO A
    (NR);
    VALUES
    (:new.objt);



exception
 when NO_DATA_FOUND then
  null;
end;

exception
  when OTHERS then
  null;
END

So, what's wrong with it (apart from `when others` - remove it! Unless it is followed by `raise`, it represents a bug in your code as it successfully hides any errors that might have happened). What happens when you run that code? — Littlefoot, Sep 02 '19 at 20:42
@Littlefoot, I don't know how to write the part of insert in table B. The trigger will do something just if there is an insert in B. To insert in B that select must returns 1. But how do I write it? — porthfind, Sep 02 '19 at 20:45
"Insert into B" is done outside the trigger (for example, on SQL*Plus prompt, in SQL Developer, your Apex application, ... doesn't matter, really. Trigger will detect that action and *fire*, running code you've written within. — Littlefoot, Sep 02 '19 at 20:52
@porthfind: I'm confused. You know how to write an `INSERT INTO A`, but you don't know how to write `INSERT INTO B`? We don't know what table B looks like, so I don't see how you'd expect someone here to advise you. ??? — Bob Jarvis - Слава Україні, Sep 03 '19 at 00:33

score 0 · Answer 1 · answered Sep 02 '19 at 20:55

0

According to this question you can not do that with trigger. Better approach would be to have additional WHERE clausule or some other logic before actual insert on table B. And to not write that trigger at all.

answered Sep 02 '19 at 20:55

batsz

55
10

Thanks for the hat tip (I wrote that accepted answer) but I feel this should be a comment not an answer. – APC Sep 03 '19 at 06:52
Of course I agree with you. My answer would be better as a comment, but I have to low reputation to post comment directly under OP question (50 reputation points needed :) ) – batsz Sep 04 '19 at 19:42

APC · Answer 2 · 2019-09-05T05:58:22.997

It seems you only want to insert into table B if the criteria for TFRG are met. The solution is to throw an exception in the trigger when they are not met. This will rollback the insert in B and prevent the insert in A.

CREATE OR REPLACE TRIGGER Trigger_name
 AFTER INSERT 
 ON B FOR EACH ROW
declare

v_exists NUMBER;

begin

begin
    SELECT 1
    INTO v_exists 
    FROM TFRG V
    WHERE 
        SUBSTR(:NEW.COD_OBJT, 1, 2) = V.CDP
        AND SUBSTR(:NEW.COD_OBJT, 12, 2) = V.CDS
        AND V.CDSIT = 'V'
        AND V.CDG IN (‘E’,’G’);


    INSERT INTO A
    (NR);
    VALUES
    (:new.objt);

exception
 when NO_DATA_FOUND then
  raise_application_exception(-20000, 'No valid record in TFRG');
END;

Incidentally, this question points to a poor data model. The rule that we can only insert into B if certain criteria in TFRG are met is only enforced at the time the trigger fires. A user in another session could be updating or deleting the record in TFRG literally right now and our insert will succeed because they haven't committed their transaction. Similarly, any DML against TFRG in the future may lead to a retroactive violation of the rule, except that nothing will happen because there is no mechanism to trigger a re-validation. Foreign key constraints are the proper approach.

Also SUBSTR(:NEW.COD_OBJT, 1, 2) is a red flag. Table B should have separate columns for each component of the key, perhaps with a compound foreign key on TFRG. If you need to display COD_OBJIT you can do that with a virtual column. Find out more.

First off this should not be in a trigger at all. But if it must I would disagree with @APC on one item. This should be an AFTER trigger. Other than "retrieving" a value for 1 column it needs no access to any of the columns in B as it concerns only data and rules for table A. However, a before trigger exposes B's data to change where the after trigger prevents that. Triggers are notorious for hiding errors (bugs) and make debugging a nightmare so protect the data wherever you can. — Belayer, Sep 03 '19 at 17:34

PL/SQL: Issue implementing trigger

2 Answers2