How to create single trigger for INSERT/UPDATE operations

Bommi

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

For Community.txt

EdStevens

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

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

https://community.oracle.com/tech/developers/discussion/comment/16790835#Comment_16790835

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

https://community.oracle.com/tech/developers/discussion/comment/16790823#Comment_16790823

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

I just provided you a sample code

Where? I don't see any code.

JohnWatson2

https://community.oracle.com/tech/developers/discussion/comment/16790990#Comment_16790990

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

https://community.oracle.com/tech/developers/discussion/comment/16791029#Comment_16791029

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

Sample Triggers.txt

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;

JohnWatson2

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

https://community.oracle.com/tech/developers/discussion/comment/16791143#Comment_16791143

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