22342

Oracle AFTER INSERT Trigger

Question:

The following trigger will not fire. The trigger worked before adding the 'SELECT c.deposit_id … piece of code. Any help will be greatly appreciated. The trigger is meant to fire after an insert is made on CASH_OR_CREDIT table if the foreign key in this table is found to be linked to another table (TRANSACTION_TABLE).

` CREATE OR REPLACE TRIGGER SEND_MONEY AFTER INSERT ON cash_or_credit REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE system_header_info NUMBER := 0; l_dep_key NUMBER := 0; CURSOR cur (cover_id NUMBER) IS SELECT header_id FROM headers WHERE party_site_id = cover_id; system_header_info VARCHAR2 (10) := schema.necessay_functions.get_system_id ('DEPOSITS'); BEGIN fnd_profile.put ('company_debugger', 'Y'); schema.necessay_functions.debugger ('old.deposit_id =' || :OLD.deposit_id); schema.necessay_functions.debugger ('new.deposit_id =' || :NEW.deposit_id); OPEN cur (system_header_info); system_header_info := 0; FETCH cur1 INTO system_header_info; CLOSE cur1; schema.necessay_functions.debugger ( 'super_user.user_id =' || super_user.user_id); schema.necessay_functions.debugger ( schema.necessay_functions.obtain_user_id ( schema.necessay_functions.get_system_id ('DEPOSITS'))); SELECT c.deposit_id INTO l_dep_key FROM schema.transaction_table o, schema.linker_table r, schema.cash_or_credit c WHERE o.primary_key = r.primary_key AND o.table_name = 'INDIVIDUAL_REC' AND o.system_id = '265226' AND o.status = 'A' AND r.status = 'A' AND c.foreign_key = r.primary_key AND c.deposit_id = :NEW.deposit_id AND r.relationship_code IN ('EMPLOYER_OF'); IF super_user.user_id = schema.necessay_functions.obtain_user_id ( schema.necessay_functions.get_system_id ('DEPOSITS')) AND l_dep_key = :NEW.deposit_id THEN schema.necessay_functions.debugger ('Inside If Condition'); FOR sys_comp IN (SELECT * FROM schema.transaction_table WHERE status = 'A' AND table_name = 'DEPOSITS' AND primary_key = :NEW.deposit_id AND system_id = schema.necessay_functions.get_system_id ( 'DEPOSITS')) LOOP schema.necessay_functions.debugger ('Inside Loop'); schema.necessay_functions.send_xml_message ('SEND_SYSTEM_MSG', 'SEND.UPDATE', system_header_info, sys_comp.system_id, sys_comp.system_key); END LOOP; ELSE schema.necessay_functions.send_xml_message ('SEND_SYSTEM_MSG', 'SEND.CREATE', system_header_info, system_header_id, :NEW.deposit_id); END IF; EXCEPTION WHEN OTHERS THEN schema.necessay_functions.debugger ('Sqlerrm:' || SQLERRM); END SEND_MONEY; /`

Answer1:

If it works without the SELECT c.deposit_id … piece then, presumably, that is what is causing an exception which is then being swallowed by the WHEN OTHERS exception handler being used and causing the trigger to look like it is not firing. You should be able to confirm that by checking whatever table/log schema.necessay_functions.debugger( is logging to.

What are the business rules around the l_dep_key value? Specifically, is it expected that the SELECT statement used to populate l_dep_key will always return a result (and only 1 result at that)? If so, at the very least wrap that statement with an anonymous block and explicitly handle any exceptions that conflict with those business rules.

BEGIN SELECT c.deposit_id INTO l_dep_key FROM schema.transaction_table o, schema.linker_table r, schema.cash_or_credit c WHERE o.primary_key = r.primary_key AND o.table_name = 'INDIVIDUAL_REC' AND o.system_id = '265226' AND o.status = 'A' AND r.status = 'A' AND c.foreign_key = r.primary_key AND c.deposit_id = :NEW.deposit_id AND r.relationship_code IN ('EMPLOYER_OF'); EXCEPTION WHEN NO_DATA_FOUND THEN ...TAKE APPROPRIATE ACTION HERE... ...POSSIBLY LOG AND RAISE... WHEN TOO_MANY_ROWS THEN ...TAKE APPROPRIATE ACTION HERE... ...POSSIBLY LOG AND RAISE... END;

As OldProgrammer stated in a comment, the exception handling in your provided code has much room for improvement. Should you really be swallowing any and all exceptions that may be thrown by the code in this trigger?

Also, as a general tip, when logging exceptions instead of just logging SQLERRM use DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() instead, as it gives you more context around the exception. Future you and/or future debuggers of this will thank you for it.

Answer2:

Thank you for all of your advice and input. I solved the problem. The exception text revealed that the table mutates when you attempt to query it leading to the trigger failure. The trick to checking the validity of the child table to the parent table after an INSERT and allowing the trigger to fire is to remove the reference to the child (trigger) table and to perform the join using :NEW.foreign_key to join to the parent table. I learned a lot while trying to debug this :)

BEGIN SELECT COUNT(1) INTO l_dep_key FROM schema.transaction_table o, schema.linker_table r WHERE o.primary_key = r.primary_key AND o.table_name = 'INDIVIDUAL_REC' AND o.system_id = '265226' AND o.status = 'A' AND r.status = 'A' AND o.foreign_key = r.primary_key AND r.primary_key = :NEW.foreign_key AND r.relationship_code IN ('EMPLOYER_OF');

Recommend

  • Angular UI Bootstrap Typeahead does not working for filter and orderBy
  • How to query a MySql table to display the root and its subchild.
  • can we insert into two tables with single sql statement?
  • If it is not allowed to Rollback TRUNCATE statement than how It is possible in Transaction [duplicat
  • Syntax error while creating function in postgresql
  • Rollback of nested transaction throwing error in TSQL
  • How to pass procedure output to multi-row insert statement
  • Display first element of a one-to-many relationship in Flask-Admin
  • Import CSV to database using sqlalchemy
  • Django DRF permissions on create related objects
  • Django and eBay style multi listing
  • How to run multiple SQL scripts, in SSMS, against SQL Azure?
  • DDL commands are AutoCommit in SQL server, what does it mean?
  • run a bulk update query in cassandra on 1 column
  • undefined method 'info' for nil:NilClass when running active record migration
  • SQLAlchemy using distinct
  • sfValidatorDoctrineUnique fails on capital letters
  • Python/Django: How to show both main model and 'foreign-key model' together in HTML
  • Model association with custom table and key names
  • Django admin - How to calculate a field value depending on other model field values
  • How do I update related object on update in SQLAlchemy?
  • ASIHTTPRequest memory leaks
  • SQLAlchemy: Any constraint to check one of the two columns is not null?
  • SQL database problems with addressbook table design
  • mysql not unique auto increment, primary key two fields
  • NoReverseMatch at / Reverse error with urlresolvers, get_absolute_url()
  • Can I update/select from a table in one query?
  • How to Add Polymorphic Comments to Feed?
  • Julia: How to give multiple workers access to functions that are 'include(…)' into a modul
  • User messaging system
  • JPA flush vs commit
  • Laravel: Getting Session ID oddly truncates when using foreach
  • C# - Is there a limit to the size of an httpWebRequest stream?
  • Optimizing database types to compact database (SQLite)
  • How to make a tree having multiple type of nodes and each node can have multiple child nodes in java
  • TFS: Get latest causes slow project reloading
  • Running a C# exe file
  • htaccess rewriting URLs with multiple forward slashes
  • Warning: Can't call setState (or forceUpdate) on an unmounted component
  • Reading document lines to the user (python)