CODEWITHSUNDEEP
oracle-database
user4422741
user4422741

Reputation: 1

Instead of update trigger on view is not fired automatically unless manually updated on View

I have a view to fetch list of my project specific packages from all_objects in Oracle DB, which will fetch the name, status, created and last DDL for all objects with wildcard operator specific to my project requirement.

CREATE OR REPLACE FORCE VIEW ALL_XYZ_PACKAGES_VW
AS
   SELECT object_name,status, object_type,created,last_ddl_time
     FROM all_objects
    WHERE object_name LIKE 'XYZ_%'
     AND object_type IN ('PACKAGE', 'PACKAGE BODY');

And my requirement is to fire a trigger which sends an email notification when any of my package gets Invalid due to some DB Link missing or dependent object is missing or any wrong code deployed with logical errors. So following is the trigger i have created:

CREATE OR REPLACE TRIGGER notify_invalidobjects_trigger 
   INSTEAD OF UPDATE ON ALL_XYZ_PACKAGES_VW
FOR EACH ROW
BEGIN
  IF (:NEW.STATUS = 'INVALID') THEN
    DBMS_OUTPUT.PUT_LINE ('Following mentioned Package is in INVALID state: '|| :OLD.OBJECT_NAME); -- Email notification part will e written here
  ELSE
      DBMS_OUTPUT.PUT_LINE ('In Else Block');
  END IF;
END;
/

Issue:
1) When i am manually updating the package status as INVALID in my view with an update statement, trigger is getting fired.

BUT when the package is automatically getting Invalid, when i am making some dependent object as Invalid or by wrong code deployment, 

TRIGGER IS NOT GETTING FIRED EVEN THE STATUS IS 
GETTING INAVLID AUTOMATICALLY IN MY VIEW AND ALL_OBJECTS.

Could any one suggest me what i need to check or update in code.

Note: I thought of using After Update trigger, but its not allowing me to use the same on views.

Upvotes: 0

Views: 517

Answers (1)

evilive
evilive

Reputation: 996

The trigger on the view will not fire because the view is not updated. Your view is no more and no less then a stored select statement. If you don't need "live response" I'd suggest to just change to a procedure for query all_objects and send the mail and run it via job.

Upvotes: 1

Related Questions