ORACLE APPLICATIONS ARCHIVES

Topicwise collection of
Postings to Mailing Lists
ON
TRIGGERS

Database Triggers

Date: Mon, 03 May 1999 16:40:18 -0400
From: Kathryn VanScoy kvanscoy@prg.com
Subject: Database Triggers

Hello,
How can I find out all the database triggers that we have in our system? Is this possible? We are currently using 10.7 SC but are looking to upgrade to Rel 11 and want to have all our documentation in order. Any help would greatly be appreciated.

Thanks in advance, Kathy

Date: Mon, 3 May 1999 15:43:32 -0500
From: "Landa, Mark (c)" U25MIBG@Carlson.com
Subject: RE: Database Triggers

SQL descr dba_triggers
Name Null? Type
------------------------------- -------- ----
OWNER NOT NULL VARCHAR2(30)
TRIGGER_NAME NOT NULL VARCHAR2(30)
TRIGGER_TYPE VARCHAR2(16)
TRIGGERING_EVENT VARCHAR2(26)
TABLE_OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
REFERENCING_NAMES VARCHAR2(87)
WHEN_CLAUSE VARCHAR2(2000)
STATUS VARCHAR2(8)
DESCRIPTION VARCHAR2(2000)
TRIGGER_BODY LONG

Date: Mon, 3 May 1999 16:45:37 -0400
From: "Brown, Sharon" SBrown@VisualNetworks.com
Subject: RE: Database Triggers

DESC ALL_TRIGGERS

then select what you want from the table or even better yet write a report documenting them.

Thanks, Sharon Sharon C. Brown Application Administrator Visual Networks, Inc. (301) 296-2676 SBrown@VisualNetworks.com

Db trigger to show message on Form

Date: Fri, 9 Jul 1999 11:16:03 -0400
From: PA DAVID SELVARAJ DSELVARAJ@FAMILYDOLLAR.com
Subject: DB Trigger for APPS Tables

Hi there,
We want to write a Database trigger on APPS table and raise the error message so that it displays the description of the error message on the form. We don't want to touch the form.
I created a record in the Message table.
v I wrote a Before Insert DB Trigger on one of the apps table

begin
hr_utility.set_message(800,'HR_9555_PER_PEOPLE');
hr_utility.raise_error;
end;

But Whenever i try to insert a record thro the FORM, it just displays the Message Name in the Alert "HR_9555_PER_PEOPLE" not the description.

Thanks in Advance. -- David Selvaraj

Date: Fri, 9 Jul 1999 16:40:56 +0100
From: rchalton@dunnes-stores.ie
Subject: Re: DB Trigger for APPS Tables

Do you need to generate an MSB file? No knowledge of the procedures you're using, but if you get the correct message when triggering from SQL*Plus this might be a possibility.

Date: Fri, 9 Jul 1999 13:22:46 -0400
From: PA DAVID SELVARAJ DSELVARAJ@FAMILYDOLLAR.com
Subject: RE: DB Trigger for APPS Tables

It's triggering from SQL*Plus fine. But not from the forms. How do i create MSB file?

--David Selvaraj

Date: Mon, 12 Jul 1999 10:07:09 +0100
From: rchalton@dunnes-stores.ie
Subject: RE: DB Trigger for APPS Tables

On 11.0.2 for NT I would sign-on with Application Developer responsibility, and run the AOL concurrent program Generate Messages for the required application. This would produce an MSB file in the application's (e.g. GL) MESG directory on the database server. At the operating system level I would then copy this file to the corresponding location on the application server, e.g from APPSTOP\GL\11.0.28\MESG\US.MSB on DB Server to APPSTOP\GL\11.0.28\MESG\US.MSB on the Apps server.

Rob C.

Date: Mon, 12 Jul 1999 15:57:14 -0400
From: PA DAVID SELVARAJ DSELVARAJ@FAMILYDOLLAR.com
To: oraapps-l@cpa.qc.ca
Subject: RE: DB Trigger for APPS Tables

Thanks.It worked
I did the following steps.

1. Created the Message
2. Run the Process "Generate Message"
3. It created the file called US.msb in $PER_TOP/mesg Directory.
4.Copy this file to Application Server Message Directory
5. Wrote the DB Trigger with the following statements

fnd_message.set_name('PER','MESSAGE_NAME')
fnd_message.raise_error;

-- David Selvaraj

Database trigger - Circular hit problem

Date: Thu, 29 Jul 1999 12:02:27 +0530
From: Susan susan_jackson@mail.usa.com
Subject: DATABASE TRIGGERS:Circular hit Problem *

Hi All

I have problem, of TRIGGERS:

I inserting a record through a trigger and I also want that at the same time two more record to get inserted.

A not so clear case would be that in an invoice I have items and for each item I insert two or more lines depending on the setup.

The problem one encounters is an insert invokes an insert trigger another insert accompanied by it again invokes another insert trigger, and it gets into a circular stuff.

Any pointers, script, hints will be a great help
Susan

Date: Fri, 30 Jul 1999 02:06:44 +0800
From: "Lee Chee Meng" leecmg@cyberway.com.sg
Subject: Fw: DATABASE TRIGGERS:Circular hit Problem *

I assume your problem is to have a trigger such that when you insert row1 into a table, it automatically inserts two or more rows, say row2 and row3 into the same table. But row2 and row3 should not fire off the trigger again to insert more rows and get into a recurring loop.

A suggestion (only works if Insert trigger can be defined with a When clause ... as with Update trigger):

Try differentiating row2 and row3 eg. by setting an attribute field to a certain value eg. TRIGGERRED and coding your insert trigger with a When clause to fire off only when attribute-field TRIGGERRED.

Hope this works.
Regards,
Chee Meng

Database trigger to insert another row into the same table

From: Indrasen Kasireddi [SMTP:indrasen98@hotmail.com]
Sent: Tuesday, May 04, 1999 2:21 PM
Hi Techgurus,
Can we write a database trigger to insert another row into the same table without getting mutating problems ?

Is there any simple workaround for this requirement??

Please Advice!!!! - Sen

Date: Tue, 4 May 1999 15:44:45 +0300
From: Ilya.Gandzeychuk@Kvazar-Micro.com
You must create two triggers: first trigger( for each row) for accumulation changes than create second trigger( statement trigger) for insert or update rows. If need use information of first trigger. Mutating problem doesn't appear in statement trigger.

Ilya Gandzeychuk Kvazar-Micro Corp. Ukraine

Date: Wed, 5 May 1999 10:23:56 +0300
From: Khalid Hussein Al-Sharif Ksharif@savola.com
Subject: RE: (Table Mutating)

I solved same problem by using an event alert. I wish Ilya Gandzeychuk to explain me more about his/her approach.

Thanx KSH

Date: Wed, 5 May 1999 13:01:19 +0300
From: Ilya.Gandzeychuk@Kvazar-Micro.com
Subject: RE: (Table Mutating)

For example, you must insert rows into table TABLE1 after this table updated.

At first time you must create a PL/SQL table in some package with type of TABLE1. Next step is creating trigger FOR EACH ROW on UPDATE on TABLE1 and inserting change rows into PL/SQL table. Finally you must create a second statement trigger (without FOR EACH ROW) on UPDATE on TABLE1 and inserting new rows into TABLE1 using information from PL/SQL table.

Oracle fires a statement trigger only once when the triggering statement is issued and mutation problem appears only in trigger with FOR EACH ROW.

If you UPDATE statement update a large set of rows in TABLE1 you must use a some temporary table, not a PL/SQL table then this solution work with more performance.

All of this can be use with INSERT,UPDATE or DELETE statements.

Ilya Gandzeychuk Kvazar-Micro Corp. Ukraine P.S. Khalid, I am a man.

Mutation Problem

Date: Tue, 23 Mar 1999 19:50:09 -0800
From: Satish Reddy
Subject: Mutation Problem

Hi all I am facing a peculiar problem.I wrote a after update trigger on SO_LINE_DETAILS.When I am updating any records through the form it is creating a MUTATION problem especially when there is a update on 'ATTRIBUTE15' eventhough I am not updating the same table through the trigger.There are no triggers on SO_LINES_ALL or SO_HOLD_SOURCES.

Any help would be appreciated. Thanks in advance.

CREATE OR REPLACE TRIGGER ZIL_PROMISE_DATE
before update on SO_LINE_DETAILS
for each row
WHEN ((old.schedule_date <> new.schedule_date) OR (nvl(new.attribute15,'X') = 'Y'))
declare
v_request_id NUMBER ;
result BOOLEAN ;
pdate so_lines.promise_date%TYPE;
headerid so_lines.header_id%TYPE ;
lineid so_lines.line_id%TYPE;
shipline so_lines.shipment_schedule_line_id%TYPE ;
-- cursor to get all eligible lines for update
cursor c1 is
select l.promise_date,
nvl(l.shipment_schedule_line_id,l.line_id),
l.line_id,
l.header_id
from so_lines l
where l.line_id = :new.line_id ;
--and promise_date is null;
begin
OPEN c1 ;
LOOP
FETCH c1 INTO pdate,shipline,lineid,headerid;
if (c1%NOTFOUND) then
exit ;
-- return ;
else
begin
update so_lines_all l set promise_date=:new.schedule_date
where nvl(l.shipment_schedule_line_id,line_id) = shipline
and l.header_id = headerid
and line_id = lineid
and promise_date is null
and line_id in (select line_id from so_line_details
where line_id = line_id
and :old.schedule_date <> :new.schedule_date);
end ;
if (:new.attribute15 = 'Y') then
begin
update so_hold_sources set hold_until_date=sysdate
where hold_source_id = (select hold_source_id
from so_order_holds soh
where nvl(soh.header_id,headerid) = headerid
and nvl(soh.line_id,:new.line_id) = :new.line_id
and :new.line_id in (select l.line_id
from so_lines l,so_line_details ld
where l.header_id = headerid
and l.line_id = ld.line_id
and ld.line_id = :new.line_id
and :new.attribute15 = 'Y'));
end ;
--concurrent program submission to release holds
begin
result := FND_REQUEST.SET_MODE(TRUE) ;
v_request_id := fnd_request.submit_request('OE','Close Orders',
'Order Holds Removal',null,FALSE);
end ;
dbms_output.put_line('Concurrent Request id: '||to_char(v_request_id));
end if ; --attribute check
end if ; --cursor if
END LOOP ;
CLOSE c1 ;
end ; --main begin
------------------------------ Date: Tue, 23 Mar 1999 22:40:15 -0600
From: Nanda Kishore
Subject: Re: Mutation Problem
you may want to check
_request_id := fnd_request.submit_request('OE','Close Orders',
'Order Holds Removal',null,FALSE);
to see if this process is updating the same table.

HTH, Nanda

Date: Tue, 23 Mar 1999 21:17:41 -0800
From: Satish Reddy
Subject: RE: Mutation Problem
Even without the fnd request it is still a mutation problem.

Thanks

Date: Wed, 24 Mar 1999 18:42:50 +0100
From: "Blaise Rempteaux" brempteaux@auchan.com
Subject: RE: Mutation Problem

In a Database trigger, you can't do a select from the table on which the trigger is.

Your first update can be such as :
update so_lines_all l
set promise_date=:new.schedule_date
where nvl(l.shipment_schedule_line_id,line_id) = shipline
and l.header_id = headerid
and promise_date is null
and line_id = :new.line_id
and :old.schedule_date < :new.schedule_date;

Your second update can be such as :
update so_hold_sources set hold_until_date=sysdate
where hold_source_id = (select hold_source_id
from so_order_holds soh
where nvl(soh.header_id,headerid) = headerid
and nvl(soh.line_id,:new.line_id) = :new.line_id
and :new.attribute15 = 'Y');

Regards Blaise Rempteaux Cap Gemini France.

Trigger on RA_CUSTOMER_TRX

Date: Tue, 15 Jun 1999 14:37:38 -0700 (PDT)
From: Prasanna Ramam sukkuv@yahoo.com
Subject: Trigger on RA_CUSTOMER_TRX

Hi

1. I have written a DB trigger on RA_CUSTOMER_TRX table to perform some "custom" activity .. In order to check the trigger, just I have inserted values in temporary table.. but its getting inserted two times ? The trigger is on Update of any column Why - 2. Whenver I entered the data in a form , and I want to save .. I want to know which SQL stmts are firing ? How should I do.. this I want to know what fields are getting updated.

Can anyone pls help me out to solve these issues

Rgds Prasanna

Date: Tue, 15 Jun 1999 14:48:14 -0700
From: "Orth, Tom (torth)" torth@sequent.com
Subject: RE: Trigger on RA_CUSTOMER_TRX

On 1, you can have the trigger monitor only specific columns. Perhaps the form is updating the record a second time behind the scenes. But if your trigger fires only when your column or columns of interest is/are modified you will get the desired result.

Date: Wed, 16 Jun 1999 06:43:50 -0700 (PDT)
From: Prasanna Ramam sukkuv@yahoo.com
Subject: RE: Trigger on RA_CUSTOMER_TRX

Thanx Very much !

I have one more question . is there anyway to find out the "source code" of the report that I am executing from the Oracle Applications

Date: Wed, 16 Jun 1999 08:33:49 -0700
From: "Orth, Tom (torth)" torth@sequent.com
Subject: RE: Trigger on RA_CUSTOMER_TRX

First the brute force method! I'm not sure how this works with Oracle applications, but read on. If you are familiar with ALTER SESSION then there is a SQL_TRACE property that can be set to TRUE. This will log your sql statements to the trace file on the SERVER. This can be enabled by invoking the form via command line with the statistics=YES option.

I'm new to the Oracle applications themselves, so I don't know if there is a simpler switch that you can turn on to just log SQL statements. I always put one into the applications that I build! It's a great debugging tool!

Date: Thu, 17 Jun 1999 10:57:34 +0530
From: manoj_bhatnagar/Polaris@polaris.co.in
Subject: RE: Trigger on RA_CUSTOMER_TRX

Hi

You can find out the source code of the report.

1. After execution of the report , Go to Help-View my requests.
2. Select the Request id of your report and Press the Request Log button.
3. Here you can find out the Report Name and its location.
4. Open Reports Designer and open the report.

Have Fun.
Manoj Bhatnagar
Associate Consultant - Oracle Apps.