Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.9K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.5K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.2K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 154 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 402 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
How to create single trigger for INSERT/UPDATE operations

Hi Team,
We are using R12.2.3.
We need to create a trigger on Inventory Items. We have DFF enabled at Item header level.
Now, the trigger has to load data into custom table XX_ITEM_AUDIT whenever new item is created or when ever DFF attribute6 is updated.
I am not able to achieve this in single trigger. So I created 2 separate triggers: for INSERT and other for UPDATE. Questions and help required here
- INSERT related trigger has to fire whenever a new item is created
- UPDATE related trigger has to fire whenever attribute6 is updated on Inventory Item. For me, what ever the field is updated on item, this UPDATE trigger is being fired. But we want this to be fired only when attribute6 is updated (NULL is not allowed for this attribute6 as we made it as mandatory DFF element).
- It will be more helpful if both the triggers are merged and created as a single trigger
- We want the custom table to be created in custom schema and grant permissions(Only SELECT,INSERT) to APPS schema.
I connected to APPS schema and created this table (As we dont have permissions and password to connect to custom schema) and tried the below command directly from APPS schema to provide grants to APPS schema itself. But this command is not working
EXEC APPS.APPS_DDL.APPS_DDL('GRANT select, insert, update ON XXNPI.XX_INV_ITEM_AUDIT TO APPS WITH GRANT OPTION');
Attached the sample table and triggers I created.
Please help me on this
Thanks in Advance,
Bommi
Answers
-
Many sites block links, and many community users refuse to open links for the same reason. There is no reason you cannot past your code (formatted as such) directly into your postings here.
-
You should not be doing this at all! Do not create triggers on the supplied tables, it is not supported and the next time you apply a patch or upgrade, heaven knows what will happen. What you should be doing is connecting to the Alert Manager Responsibility, and creating appropriate Alert Actions. If you need more complex logic, then use a Workflow.
I am surprised that your implementation consultants have not already told you this :)
-
Hi John,
Even after I warned, I am also surprised, when they told that they want only triggers :)
As it is client side technical team forcing this, I am also left with no other option other than saying 'Yes' 😁
So, could you please let me know how to create single trigger .
Thanks,
Bommi
-
Hi Ed,
I just provided you a sample code for understanding of my requirement. Because, as it is simple requirement, I thought that saying in code language is simple than saying in words.
Hope it is not against to forum 🙂
Thanks,
Bommi
-
I just provided you a sample code
Where? I don't see any code.
-
Well, I can certainly let you know: use a compound trigger. However, if you are not aware that compound triggers exist, you might want to ask your manager about having a bit of training before the client finds out :)
For example,
create trigger trig1 for insert or update on emp compound trigger before each row is begin case when inserting then dbms_output.put_line('insert!'); when updating then dbms_output.put_line('update!'); end case; end before each row; end; /
-
Hi John,
I created compound trigger. But still it is not working. Instead I tried with a simple trigger, which is working.
Could you please check and let me know what is the issue with compound trigger I created(Attached)
Thanks,
Bommi
-
--Trigger that is working CREATE OR REPLACE TRIGGER XX_inv_item_audit_trg1 AFTER INSERT OR UPDATE ON mtl_system_items_b FOR EACH ROW DECLARE PRAGMA autonomous_transaction; BEGIN IF :old.inventory_item_id IS NULL THEN INSERT INTO apps.XX_inv_item_audit ( item_number, item_id, organization_id, reserve_flag, reserve_start_date, reserve_end_date, event_type_code, event_date, creation_date, created_by, last_update_date, last_updated_by ) VALUES ( :new.segment1, :new.inventory_item_id, :new.organization_id, :new.attribute6, :new.attribute7, :new.attribute8, 'INSERT', sysdate, :new.creation_date, :new.created_by, sysdate, :new.last_updated_by ); COMMIT; ELSIF :old.inventory_item_id IS NOT NULL AND :old.attribute6 <> :new.attribute6 THEN INSERT INTO apps.XX_inv_item_audit ( item_number, item_id, organization_id, reserve_flag, reserve_start_date, reserve_end_date, event_type_code, event_date, creation_date, created_by, last_update_date, last_updated_by ) VALUES ( :new.segment1, :new.inventory_item_id, :new.organization_id, :new.attribute6, :new.attribute7, :new.attribute8, 'UPDATE', sysdate, :new.creation_date, :new.created_by, sysdate, :new.last_updated_by ); COMMIT; END IF; END; --Compound Trigger that is NOT working CREATE OR REPLACE TRIGGER XX_inv_item_audit_trg FOR INSERT OR UPDATE ON mtl_system_items_b COMPOUND TRIGGER BEFORE EACH ROW IS BEGIN CASE WHEN inserting THEN INSERT INTO apps.XX_inv_item_audit ( item_number, item_id, organization_id, reserve_flag, reserve_start_date, reserve_end_date, event_type_code, event_date, creation_date, created_by, last_update_date, last_updated_by ) VALUES ( :new.segment1, :new.inventory_item_id, :new.organization_id, :new.attribute6, :new.attribute7, :new.attribute8, 'INSERT', sysdate, :new.creation_date, :new.created_by, sysdate, :new.last_updated_by ); COMMIT; WHEN updating THEN IF :old.attribute6 <> :new.attribute6 THEN INSERT INTO apps.XX_inv_item_audit ( item_number, item_id, organization_id, reserve_flag, reserve_start_date, reserve_end_date, event_type_code, event_date, creation_date, created_by, last_update_date, last_updated_by ) VALUES ( :new.segment1, :new.inventory_item_id, :new.organization_id, :new.attribute6, :new.attribute7, :new.attribute8, 'UPDATE', sysdate, :new.creation_date, :new.created_by, sysdate, :new.last_updated_by ); COMMIT; END IF; END CASE; END BEFORE EACH ROW; END XX_inv_item_audit_trg;
-
You really should consider attending a PL/SQL course. If you are trying to COMMIT inside a trigger, I dread to think what other bugs you have already built into the application. At the very least, you should ask your DBA to do a thorough code review of everything you have written so far.
It isn't fair on your client to charge him for writing code like that. Though if he happens to be reading this topic, perhaps he will not pay the invoice 😉
-
Hi John,
I like your sarcasm 😄
But, to say, I am very new to this trigger concepts (As I never used). Now only I recognized we dont need to give COMMIT.
Now, I removed the COMMIT in the compound trigger and then it is working.
But, seems any normal trigger or compound trigger can help to achieve my requirement
Thanks,
Bommi