Forum Stats

  • 3,751,474 Users
  • 2,250,366 Discussions
  • 7,867,435 Comments

Discussions

How to create single trigger for INSERT/UPDATE operations

Bommi
Bommi Member Posts: 705 Bronze Badge

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


  1. INSERT related trigger has to fire whenever a new item is created
  2. 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).
  3. It will be more helpful if both the triggers are merged and created as a single trigger
  4. 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


«1

Answers

  • EdStevens
    EdStevens Member Posts: 28,394 Gold Crown

    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.

  • JohnWatson2
    JohnWatson2 Member Posts: 4,301 Silver Crown

    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 :)

  • Bommi
    Bommi Member Posts: 705 Bronze Badge

    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

  • Bommi
    Bommi Member Posts: 705 Bronze Badge

    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

  • EdStevens
    EdStevens Member Posts: 28,394 Gold Crown

    I just provided you a sample code

    Where? I don't see any code.

  • JohnWatson2
    JohnWatson2 Member Posts: 4,301 Silver Crown


    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;
    /
    


    Bommi
  • Bommi
    Bommi Member Posts: 705 Bronze Badge

    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


  • Bommi
    Bommi Member Posts: 705 Bronze Badge
    --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;
    
  • JohnWatson2
    JohnWatson2 Member Posts: 4,301 Silver Crown

    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 😉

  • Bommi
    Bommi Member Posts: 705 Bronze Badge

    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