Creating a trigger with SELECT Function

Good day everyone,

I’m having trouble making my trigger work. As far as the functionality of the body and how it behaves, it does exactly as I intended for it to behave. However, when I start to fire the trigger, it returns an error in which Triggers should not have a SELECT statement from inside the main body. I’m still fairly new to coding and how to materialize the idea in my head into code. Hopefully someone could point me in a right direction on how change the Trigger I would like to have as a final result. Please see below script.

Update: Expected result would be whenever a user UPDATE a row and INSERT a new record via the application or job being run in the background, S1_HOVER_REPORT column would be updated with the value from the SELECT script and use the data from the S1_HOVER case result.

(Edit: I have updated the details of the problem above, added the Table being used and Error return)

Table: SITE
Column Name       Type        
------------------------------
ID                VARCHAR2(14)
NAME              VARCHAR2(70)
TYPE_CODE         VARCHAR2(2)
PARENT            VARCHAR2(14)
S1_HOVER_REPORT   VARCHAR2(14) 

CREATE OR REPLACE TRIGGER MESS.S1_HOVER_REPORT
AFTER INSERT OR UPDATE ON MESS.SITE
FOR EACH ROW
BEGIN
   UPDATE (SELECT S1.ID,
                  S1.NAME,
                  S1.TYPE_CODE,
                  S1.PARENT AS PARENT1,
                  S2.PARENT AS PARENT2,
                  S1.S1_HOVER_REPORT,
                  CASE
                     WHEN (S1.TYPE_CODE = 'H2') THEN S1.PARENT
                     WHEN (S1.TYPE_CODE = 'S1') THEN S2.PARENT
                     ELSE S1.ID
                  END AS S1_HOVER
             FROM SITE S1,
                     (SELECT ID,
                             NAME,
                             PARENT,
                             TYPE_CODE
                        FROM site
                       WHERE type_code='H2') S2
            WHERE S1.PARENT=S2.ID
               OR S1.ID = S2.PARENT) S3
   SET S3.S1_HOVER_REPORT = S3.S1_HOVER;
END;

Error returned when Trigger fired:
Error report -
SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
ORA-06512: at "MES.S1_HOVER_REPORT", line 2
ORA-04088: error during execution of trigger 'MES.S1_HOVER_REPORT'
01779. 00000 -  "cannot modify a column which maps to a non key-preserved table"
*Cause:    An attempt was made to insert or update columns of a join view which
           map to a non-key-preserved table.
*Action:   Modify the underlying base tables directly.

(Update: I have included the updated trigger and it’s now compiling without any issue, but I’m having errors whenever I try updating a record)

CREATE OR REPLACE TRIGGER MESS.S1_HOVER_REPORT
BEFORE INSERT OR UPDATE ON MESS.SITE
FOR EACH ROW
DECLARE
   v_S1_HOVER_REPORT VARCHAR2(14);
BEGIN
SELECT CASE
           WHEN (S1.TYPE_CODE = 'H2') THEN S1.PARENT
           WHEN (S1.TYPE_CODE = 'S1') THEN S2.PARENT
           ELSE (S1.ID)
        END AS S1_HOVER
   INTO v_S1_HOVER_REPORT
   FROM SITE S1,
       (SELECT ID,
               NAME,
               PARENT,
               TYPE_CODE
          FROM site
         WHERE type_code='H2') S2
  WHERE S1.PARENT=S2.ID
     OR S1.ID = S2.PARENT;

:NEW.S1_HOVER_REPORT := v_S1_HOVER_REPORT;
END;

Error report -
SQL Error: ORA-04091: table MES.SITE is mutating, trigger/function may not see it
ORA-06512: at "MES.S1_HOVER_REPORT", line 4
ORA-04088: error during execution of trigger 'MES.S1_HOVER_REPORT'
04091. 00000 -  "table %s.%s is mutating, trigger/function may not see it"
*Cause:    A trigger (or a user defined plsql function that is referenced in
           this statement) attempted to look at (or modify) a table that was
           in the middle of being modified by the statement which fired it.
*Action:   Rewrite the trigger (or function) so it does not read that table.

Answer

Firstly from the error message

An attempt was made to insert or update columns of a join view which map to a non-key-preserved table.

S3 is the veiw ( you are creating the view by doing a select inside an update statment). You can try and change this to have key preservation but I really wouldn’t know how.

The error suggests updating the base tables not the view. So as mentioned in the comments :old and :new are your friend.

:OLD holds all the values of the table the trigger is created on BEFORE the update (null if insert)

:NEW holds all the values of the table the trigger is created on AFTER the update / insert…..

So if I understand what you want to do correctly you would need to…

1. declare a variable eg v_S1_hover_report

2. do your select returning whatever value you need into this variable

3. set the value in your site table by doing

   :NEW.S1_HOVER_REPORT := v_S1_hover_report

By setting this value into the :NEW object when a commit happens it will be committed to the database. This completely removes the need for an update statement in the trigger.

You can also use :NEW.id in your select statement to filter it down to the record you are updating if it is helpfull

CREATE OR REPLACE TRIGGER MESS.S1_HOVER_REPORT
AFTER INSERT OR UPDATE ON MESS.SITE
FOR EACH ROW

v_test varchar2(10);

BEGIN

select 'Your value' into v_test from dual;

:new.s1_hover_report := v_test;

END;

OR

CREATE OR REPLACE TRIGGER MESS.S1_HOVER_REPORT
AFTER INSERT OR UPDATE ON MESS.SITE
FOR EACH ROW

v_test varchar2(10);

BEGIN

select 'Your value' into :new.s1_hover_report from dual;

END;