Will an update in the trigger trigger itself? Oracle PLSQL

oracleplsqltrigger

Here's my PLSQL trigger:

CREATE OR REPLACE TRIGGER ROOT_CHANGING_TRG 
BEFORE UPDATE OF ROOT_ID,PARENT_PHYS_ID ON UNIQUE_PHYSICIAN 
FOR EACH ROW
DECLARE
var_root number := :new.ROOT_ID;
var_par number := :new.UNIQUE_ID;

BEGIN

UPDATE UNIQUE_PHYSICIAN
SET ROOT_ID = var_root
WHERE PARENT_PHYS_ID = var_par;

END;

Will this trigger propagate? Like, if it updates a ROOT_ID for another record, will that trigger its own trigger? Further, if it does trigger that, will it use the new ROOT_ID? I want the ROOT_ID to propagate down the tree I've built.

Edit:

How this works is that each record has a unique ID, a parent ID, and a root ID. I basically have a tree, each member of that tree has a root_ID pointing at the unique ID of the root and a parent ID pointing at the one above it. The root's root and parent IDs are its own unique ID.

in the case that a user manually changes a record to point at a new root and parent, I want all the children of that node to have the new root ID. Is there a better way to do this?

Best Answer

My proposal would be this one:

CREATE TABLE UNIQUE_PHYSICIAN (
    PARENT_PHYS_ID NUMBER,
    PHYS_ID        NUMBER CONSTRAINT PHYSICIAN_PK PRIMARY KEY);

ALTER TABLE DPISYS.UNIQUE_PHYSICIAN ADD CONSTRAINT UNIQUE_PHYSICIAN_PARENT_FK 
   FOREIGN KEY (PARENT_PHYS_ID) REFERENCES DPISYS.UNIQUE_PHYSICIAN (PHYS_ID);

CREATE OR REPLACE TRIGGER ROOT_CHANGING_TRG 
    BEFORE UPDATE OF PHYS_ID ON UNIQUE_PHYSICIAN 
    FOR EACH ROW

BEGIN
    :NEW.PHYS_ID := :OLD.PHYS_ID;
END;

Then the root element(s) is/are the record(s) where PARENT_PHYS_ID IS NULL.

For PHYS_ID, resp. PARENT_PHYS_ID you should use a surrogate key, preferable generated from a sequence. In this case there is no reason (and no possibility) to change them ever.

The root Id you could get with this query:

SELECT p.*, CONNECT_BY_ROOT PHYS_ID AS ROOT_ID
FROM UNIQUE_PHYSICIAN p
START WITH PARENT_PHYS_ID IS NULL 
CONNECT BY PRIOR PHYS_ID = PARENT_PHYS_ID;

Related Solutions

MySQL using trigger instead of check

You do not need a RETURN 1 because Trigger is a Stored Procedure, not a Stored Function.

If you want to break it on purpose, that's acceptable.

I wrote two posts about how to break a trigger midstream

Trigger in MySQL to prevent insertion (Apr 25, 2011)
check constraint does not work? (Dec 23, 2011)

Try this:

CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER `test`
BEFORE UPDATE ON `apply` 
FOR EACH ROW
BEGIN
    DECLARE dummy INT;

    IF NEW.cname = "hi" THEN          
        SET NEW.cname = "hello";
    ELSE
        SELECT no_such_column INTO dummy
        FROM information_schema.no_such_table;
    END IF;

END; $$

Cannot update parent rows with trigger on the same table

It looks like what you want to do is actually have a summary of the sum of used bytes in each folder, right? I believe that triggers in Oracle are not the best choice of technology to provide you this functionality. This could be better modeled using a view, or even better, a materialized view.

The problem with triggers is that they will serialize the DML to the table and will not scale too well. Imagine a situation where you are updating and inserting lots of rows... for each one of them you'll have an update to the folder table.

If you model your problem with a view instead you would not incur the overhead of the extra DML on the folder table. A simple query like this should do the trick:

SELECT fd.name AS folder_name,
       SUM(f.bytes) AS folder_size
  FROM folders fd, files f
 WHERE fd.id = f.folderId
 GROUP BY fd.name;

You may then make this query persistent by creating a view (the sum will run every time you query the view) or a materialized view with fast refresh on commit (in this case the materialized view stores the calculation as a pre-computed table, useful if the table is huge and the sum is costly).

The materialized view "fast refresh on commit" feature delegates to Oracle the task of recalculating the agregation every time the underlying table(s) changes.

The Data Warehousing Guide has an in dept explanation on this feature: https://docs.oracle.com/database/121/DWHSG/refresh.htm#DWHSG015

Best Answer

Related Solutions

MySQL using trigger instead of check

Cannot update parent rows with trigger on the same table

Related Question