Trigger That Will On Updating Table Order And Changing Status To GoodsReceived

Nov 30, 2009

my simplified database schema is:

Order
OrderID
Status
OrderItems
OrderID
EAN
Amount
Store
EAN
Amount

now,I need trigger that will on updating table Order and changing status to "GoodsReceived" increase amounts in Store according to values in OrderItems.

create or replace trigger order_received_trigger
before update
on Order
for each row
begin
if (:old.status=4 and :new.status=1) then
/*
select ean, sum(amount)
from OrderItems
where OrderID=:old.OrderID
group by ean;
*/
end if;
end;

but now i dont know how to apply that select on table Store.

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: Updating Table With Sequentially Changing Column Name?

Aug 9, 2012

I am trying to update all rows of 100 column of a table with '0'. The column name is sequentially increasing one like EMP_1,EMP_2,EMP_3, etc. I tried using the below code but I am getting ora-06550 and ora-00927 error's.

begin
FOR i in 1..100 loop
UPDATE EMP_DETAILS SET EMP_'||i||' =0
END LOOP;
COMMIT;
END;

View 6 Replies View Related

SQL & PL/SQL :: Trigger Updating Second Table With PK Value

Oct 20, 2011

Oracle 11.2 - The goal is to create a trigger on table and anytime an update, delete or insert is done on the table, write values to a second table. I have the trigger and it works except it is not loading my col1/PK values. I understand I need to do a new/old value. Col1 is my PK on Table that I want to load anytime there is an update/delete/insert on the table. How do I code the old/new variable?

My
CREATE OR REPLACE TRIGGER TRIGGER_NAME
AFTER INSERT OR UPDATE OR DELETE
ON TABLE_NAME
FOR EACH ROW
DECLARE
v_col1 TABLE_NAME.COLUMN%TYPE;
BEGIN
[code]...

View 5 Replies View Related

SQL & PL/SQL :: Trigger For Updating All Columns Of A Table

Sep 12, 2013

I have created the following trigger whcich will track all the column changes and insert the row in log table. here i have some doubt while substituting the cursor value.

create or replace trigger historylog_trigger
before update on log_dev_test
for each row
declare
PRAGMA AUTONOMOUS_TRANSACTION;
in_loamid number(10);
in_col_name varchar2(10);
in_old_val varchar2(100);

[Code]..

are the below assignement of values will work ?

in_old_val:= ':old.'||r.column_name;
in_new_val:= ':new.'||r.column_name;

i want to take the coulmname from the cursor and assign tat to psuedo columns like :new.r.column_name .

View 8 Replies View Related

SQL & PL/SQL :: Mutating Trigger While Updating Original Table

Feb 18, 2013

I have a table EMPLOYEE with columns employee_id and status. I have a requirement that when an employee status is getting changed, then even its linked employee's status also should be changed to the same status value. For this, I need to handle the updating of linked employee's status using a trigger.

Since we get mutating trigger issue when try to update the originating table, I am trying to go with the approach of "in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you may be able to use two triggers. The first is an AFTER row trigger that updates a temporary table, and the second an AFTER statement trigger that updates the original table with the values from the temporary table".

But however I am still facing the same issue.

Test case:

CREATE TABLE EMPLOYEE
(
EMPLOYEE_ID VARCHAR2(1),
STATUS NUMBER(9)
);

INSERT INTO EMPLOYEE VALUES ('A',1);
INSERT INTO EMPLOYEE VALUES ('B',1);
commit;
[code]....

Also, any alternate options (rather than using 2 triggers with temp table).

View 7 Replies View Related

Networking And Gateways :: Using A Trigger In OID And Updating EBS Table?

Apr 27, 2011

EBS version is 12.1.3. we need to update the e-mail address in per_all_people_f in Oracle EBS based on a trigger that will fire against a column in Oracle Internet Directory (OID). A trigger will be built against table ct_mail, column attrvalue. When a value is inserted or updated this value will use an api on the EBS database to update per_all_people_f.

Q => Is ct_mail the correct table to use in OID for email addresses? or can you confirm a different table?

Q => If Ok to use ct_mail as the table can you see any problems with using a trigger in OID?

Q => Finally is a database link between OID and EBS database the best way for the trigger to work?

View 2 Replies View Related

SQL & PL/SQL :: Changing Order Of Columns?

Apr 30, 2012

i have a table test on 4 different-different database, at the starting structure is same in all the database. and now i want to change the datatype of the primary key column named "testid" , then i add a temp column in test table (and it is added at the last in table).

i have copied the data of testid in temp column and renamed it as testid and i dropped the testid column , then the problem is that primary key column will comes at last and i want to make it like previous position so that there will not be any difference in all 4 database as in structure.

is it possible to changing column order as our desire without dropping the table?and i made all the script to changing the datatype of primary key column.

View 3 Replies View Related

Bring Record To Top Without Changing Ascending Order Of Dep-id?

Jul 18, 2010

i have a table named employees in which there are 2 columns dep_id and name. One record having name as annie has dep_id=null .

When i arrange the records of the table in ascending order using order by dep_id the record with name annie and dep_id is placed in the last because null is consired largest. How can I bring that record to the top without changing the ascending order of the dep_id.

View 7 Replies View Related

SQL & PL/SQL :: Updating Sequence With Order Of Old Composite PK

Sep 5, 2011

I am replacing a composite PK with a new single PK as business changed. I am creating sequence to fill the new field that should present the PK but I need to fill it according to the values of the old composite PK.

CREATE TABLE ABC
(
COMP_PK1 NUMBER,
COMP_PK2 NUMBER,
COMP_PK3 VARCHAR2(8),
VAL VARCHAR2(50),
PRIMARY KEY (COMP_PK1, COMP_PK2,COMP_PK3)
);
[code].....

I need to update ABC and set ABC_SERIAL = SEQ_ABC.nextval but i need this to be done according to the order of the old composite primary key... i.e. rows with COMP_PK1 = 1 are filled before rows with COMP_PK1 = 2 and so on..

View 33 Replies View Related

Workflow :: External Sales Order Lines Remain In Internal Req Requested Status

Feb 13, 2013

Customer sales order lines are fulfilled by IR-ISO. When line progresses, it is set to have Internal Req Requested status. A scheduled program runs amd IR are created. However, sales order line status does not change to Internal Req Open. It remains in Internal Req Requested status.

As a work around, workflow is rewound to 'Check ATR Qty' which is customized activity and then status changes to Internal Req Open. What could be the reason that workflow is not setting the status to Internal Req Open?

View 1 Replies View Related

Performance Tuning :: How To Tune Order By Clause Without Changing Sort Area

May 1, 2008

How to avoid sort operation by an order by clause without changing the sort area size.what hints or changes should be done in query so that order by clause work faster.

View 10 Replies View Related

SQL & PL/SQL :: Updating View Using Instead Of Trigger

Jul 11, 2013

I have two different tables having similar structure but data is coming from different source.finally i want to update the view so that the it should affect the base table.

create table emp1 as
select empno,ename,job,deptno,sal from emp where deptno=10;
create table emp2 as
select empno,ename,job,deptno,sal from emp where deptno=20;
create view emp_view as select * from emp1 union all select * from emp2;
[code].......

View 13 Replies View Related

SQL & PL/SQL :: Updating Old Values In Trigger?

Apr 16, 2013

I am learning oracle trigger, i have one query.

Can i increment the old column value in trigger.

eg: :new.cid := :old.cid+1;

is this is correct.

View 4 Replies View Related

SQL & PL/SQL :: Result Order In 2 Table Joins Without Order By

Sep 6, 2011

I have below tables,

describe rpthead

Name Null Type
--------------------------- -------- -------------
RPTNO NOT NULL NUMBER
RPTDATE NOT NULL DATE
RPTD_BY NOT NULL VARCHAR2(25)
PRODUCT_ID NOT NULL NUMBER

describe rptbody

Name Null Type
------------- -------- -------------
RPTNO NOT NULL NUMBER
LINENO NOT NULL NUMBER
COMMENTS VARCHAR2(240)
UPD_DATE DATE

The fact is that we store some header in RPTHEAD and store real data in RPTBODY, the question is that if I use below SQL to query all data for a 'PRODUCT_ID'.

SELECT t0.LINENO, t0.COMMENTS, t0.RPTNO, t0.UPD_DATE
FROM RPTBODY t0 , RPTHEAD rpthead
WHERE
(
t0.RPTNO = rpthead.RPTNO
AND
t0.UPD_DATE>=to_date('1970/01/01 00:00:00','YYYY/MM/DD hh24:mi:ss')
AND
rpthead.PRODUCT_ID IN ('4647')
)

I do not want to have 'ORDER by' clause since data set is too large, the sorting takes long time, is there any way to get the result rows in the order sorted by RPTNO? We have the index for RPTNO on RPTBODY.

View 5 Replies View Related

SQL & PL/SQL :: Trigger For Updating Two Accounts After Money Transfer

Feb 21, 2012

I get a "ORA-00922 missing or invalid option" after compiling the following trigger:

create or replace
trigger aifer_transfer
after insert on transfer
for each row
[code]......

I want the trigger to affect the transferring account (from_account) with minus the "amount" being transferred from it, and the account transferred to (to_account) with plus the "amount" being transferred to it. My tables looks as following:

TRANSFER
----------------------------------------- -------- ------------
ROWNR NOT NULL NUMBER(9)
PNO NOT NULL VARCHAR2(11)
FROM_ACCOUNTNO NOT NULL NUMBER(8)
TO_ACCOUNTNO NOT NULL NUMBER(8)
AMOUNT NUMBER(10,2)
DATE NOT NULL DATE

ACCOUNT Null? Type
----------------------------------------- -------- ------------
ACCOUNTNO NOT NULL NUMBER(8)
KTNR NOT NULL NUMBER(6)
REGDATE NOT NULL DATE
BALANCE NUMBER(10,2)

View 5 Replies View Related

Forms :: Trigger Execution Order

May 1, 2010

my question is ,

i have three trigger one at form level,second on database level and third is on library level.

three are same .

which one will fire first,

give me answer.

Changed title to something meaningfull. Next time think about your title for a second rather than just putting oracle.

View 7 Replies View Related

Forms :: Updating Records Values And Post Trigger

Jul 15, 2013

I have a multi record block based on a view. All records in the view are displayed in the block by use of Post-Query trigger when entering the form.

The block has 5 items as follows:

1) RECORD_STATUS = a non-base table column which is a checkbox.
2) ITEM_TYPE = a text-item which has an LOV attached.
3) ITEM_TEXT = a text-item which is free format text.
4) LAST_UPDATE_DATE a date column
5) STATUS = a text item either 'Open' or 'Closed'

The LOV is based on a table of Item Types with values say, 'Type1', upto 'Type9'.

I have a Wnen-New-Record-Instance trigger which 'Posts' changes to the database. This has been included as i want to limit the values of the ITEM_TYPE column to values which have not been previously used.

Consider this scenario...

The block has 3 records.

record 1 has 'Closed' status so no updates are allowed.
record 2 has 'Open' status so updating of Item_Text is allowed.
record 3 has 'Open' status so updating of Item_Text is allowed.

I check the RECORD_STATUS checkbox on record2.

(This sets the RECORD_STATUS checkbox to a checked value and changes the STATUS column to 'Closed' by When-Checkbox-Changed trigger.) At this point the record has not been saved so if you uncheck the checkbox , then the STATUS column will go back to 'Open'. However at this point i will leave it as Checked (Closed).

I then insert a new record, only values Item4 to Item 9 are correctly shown in the LOV. I select Item4.

I then go back to the previous record and uncheck the Checkbox to say that i wish to leave it 'Open' after all (in effect no changes have occurred), then the STATUS column correctly reverts back to 'Open' by my WCC trigger. If i then SAVE the changes, the new record has been inserted on the database correctly, however the LAST_UPDATED_DATE from the record which was checked and then unchecked has also been updated incorrectly even though no net changes have actually occurred.

(because i am using WNRI trigger to limit the List of Values on the LOV column, this has incorrectly set the previous records LAST_UPDATED_DATE column to be Sysdate.)

How can i stop this from happening?

View 1 Replies View Related

Forms :: ORA-01461 When Updating A LONG-Column Via Trigger

Jul 5, 2010

I have a non-base-table item which I want to update in the pre-update trigger of the current block.

If the content of the field exceeds 4000 characters, i get the error message

ORA-01461: can bind a LONG value only for insert into a LONG column.

The code is

update tab set long_col = :formsblock.long_col
where tab.tabpk = :formsblock.foreign_tabpk;

Workarounds would be,
1.) to delete the old dataset and insert the new one:

delete from tab
where tab.tabpk = :formsblock.tabpk;

insert into tab (tabpk, long_col) values
(:formsblock.foreign_tabpk, :formsblock.long_col);

or 2.) to change the Item from a non-database item to a database item and use the internal update of the forms-module, but both workarounds are not very satisfying.

Do you know another way to update the LONG-column within the pre-update trigger (or any other PL/SQL part of forms)?

View 2 Replies View Related

Create / Update Query To Use As Trigger For Updating Info In DB Dependent Upon Time

Dec 7, 2010

I am trying to create and update query to use as a trigger for updating information in a database which is dependent upon time.

I have a severity level which needs to be decreased in value after a set amount of time. the time of the action is recorded automatically on the creation of the the record. after an hour anything at severity level 2 needs to go up to 1, after 2 hours level 3 goes up to 2 and 3 hours level 4 goes up to 3

so far this is what I have

UPDATE calllog
SET timedate = current_timestamp, severityid = severityid - 1
WHERE ((severityid = 2 and timedate >= current_timestamp + (1/24)) or
(severityid = 3 and timedate >= current_timestamp + (2/24)) or
(severityid = 4 and timedate >= current_timestamp + (3/24)))

I am not getting any errors back from this sql but it is not updating the relevent information. I am using one cell for time and date in format = DD-MON-YYYY HH24:MM:SS.

View 3 Replies View Related

Application Express :: Changing Sequence In Content Frame Not Changing Ordering Of Regions

Apr 24, 2013

Using Application Express 4.2.1.00.08

Any problem using 4.2's One Level Tabs - Content Frame page where you have a parent region (of type content frame Body Container ) while number of child regions (of type Hide & Show Region - Borderless ), if you want to re-order them by changing the sequence, it has no effect when you run the page.

The regions still show in the same order top to bottom as it did before the update of sequences. Heading links (to show/hide child regons in content frames) has the same ordering as before.

View 3 Replies View Related

SQL & PL/SQL :: Change The Temp Table Status?

Jun 21, 2011

In one of the databases, we have created more temp tables as on commit preserve rows. but i want to change the on commit preserve rows to on commit delete rows as per application requirements.I have searched the google but i didnt find any alter scripts for it.?

View 1 Replies View Related

PL/SQL :: Joining Order Table With Asset Or CX_ACTION Table?

Apr 18, 2013

joining the Order table with the Asset table or the Order table with CX_ACTION table to Can i get reference on this .

select x.x_msisdn,
x.x_product_name,
x.x_action,
x.x_status_outcome,
a.recovered_dt,
x.x_amount,

[code].....

View 1 Replies View Related

Forms :: Changing A Block Not Base Table

Oct 15, 2012

How do I change the status of a block to change?

View 7 Replies View Related

SQL & PL/SQL :: Changing Field Type In Oracle Table

Dec 13, 2011

I have uploaded some tables from MS Access to Oracle and in the process, some Number fields have been converted to Text. This means that any Queries where there are calculations with those fields (which have been converted to text) don't work properly anymore.

I would like to change the field type from text to number (with cmd and sqlplus) as that's the only way I can access the oracle backend.

I am not sure what commands to use to change this.

Also, if I change the data type from text to number, is it likely I will lose some data?

And how do I backup the table before I do this?

View 3 Replies View Related

Updating Table With Same Key?

Nov 19, 2008

I am using oracle 9i, and having trouble with updating a table.

I get ORA-00001(unique constraint) Error on executing the sql below; I know sql below is little strange( which use unique key in 'SET' statement) . but It worked on My Oracle Server. but it didn't on Client's.

why this error occurs or why this error did not occur on my PC,

[Update sql](key is CD and SDATE)
Update TBL1
set CD = 'A',

[Code]....

View 4 Replies View Related

Performance Tuning :: Order Of Steps In Execution Path And Order Of Predicates In The Plan

Mar 20, 2012

Which step in the following plan is the first step of execution

I reckon it is "TABLE ACCESS BY INDEX ROWID| BANK_BATCH_STATE"

Is that correct?

In the "Predicate Information (identified by operation id):"

section the predicates - access and filter for the step "TABLE ACCESS FULL | PYMNT_DUES" are displayed first

Isn't there any relation between the order of execution steps and the order in which predicates are displayed?

Execution Plan
----------------------------------------------------------
Plan hash value: 538700484
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2364 | 15 (14)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 1 | 2364 | 15 (14)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 2364 | 14 (8)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 2313 | 13 (8)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 2281 | 12 (9)| 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 1 | 2255 | 11 (10)| 00:00:01 |
|* 7 | HASH JOIN | | 1 | 175 | 6 (17)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | INDX_2 | 12 | 612 | 2 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | PYMNT_DUES | 43 | 5332 | 3 (0)| 00:00:01 |
| 10 | VIEW PUSHED PREDICATE | | 1 | 2080 | 5 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 154 | 5 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 103 | 4 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID| BANK_BATCH_STATE | 1 | 32 | 2 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | INDX_BBS_1 | 3 | | 1 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID| DAILY_CHECK | 1 | 71 | 2 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | INDX_SEARCH | 1 | | 1 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | INDX_2 | 1 | 51 | 1 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | INDX_IAM_SR_NO | 1 | 26 | 1 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | INDX_2 | 1 | 32 | 1 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | INDX_2 | 1 | 51 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------

View 3 Replies View Related

Updating Two Table Via SQL Loader

Jun 27, 2011

I am trying to write a control file that will read information from two INFILES and update two tables with the different information via SQL Loader. I am using Oracle 11g on Linux. i am not sure how to take the result from the first insert query and use it as input to the second insert query. Currently I have the following control file:

LOAD DATA
INFILE 'table1.dat'
INFILE 'table2.dat'
APPEND
INTO TABLE table1
WHEN tid='1'
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
(pid "pid.nextval", p_fname, p_lname, tid)
[code]....

The pid in the second insert query should be taken from the pid generated in the first insert query. However, I do not know how to do this. Does SQL Loader run the first insert query for all entries in the table1.dat file and then the second insert query or will it do one record at a time? Here are my INFILES:

table1.dat
,John,Doe,1
,Joe,Smith,1

table2.dat
10,,abc,1,
11,,xyz,1,

The second field in table2.dat should be taken from the result of creating a record in the table1.dat. Is this something that can be achieved using SQL Loader? The first part of the control file is successful, I can see the table being updated with the contents on table1.dat, but the second query fails.

View 1 Replies View Related

Updating A Table Column Which Is XML Datatype

Nov 1, 2011

I am updating a table column which is xml datatype and am getting above error.Below is the process what i did. since the xml is too large i split them into small chunks.

DECLARE
conditionXML CLOB;
ls_xml_2 Clob;
ls_xml_3 clob;
ls_xml_4 Clob;
ls_xml_5 Clob;
ls_xml_6 clob;
ls_xml_7 Clob;
[code]...

View 1 Replies View Related

SQL & PL/SQL :: Updating Fields Of History Table

Aug 25, 2011

pgit_policy is transaction table having producer code field.

pgith_policy is history table, on that table if any endorsement passed new records created with same polh_sys_id and increment on POLH_END_NO_IDX.

I am trying to update all records of the history table but its updating only higest POLH_END_NO_IDX only. i need to update all producer code.

update pgith_policy a
set a.polh_producer_code= (select b.pol_producer_code
from pgit_policy b
where b.pol_no=a.polh_no
--and b.POL_END_NO_IDX= a.POLH_END_NO_IDX and b.POL_END_SR_NO = a.POLH_END_SR_NO
and b.pol_producer_code is NOT NULL
and b.pol_class_code='10')
where a.polh_class_code='10'
and a.polh_producer_code is null
and a.polh_appr_dt between to_date('01-06-2011', 'dd-mm-yyyy') and to_date('30-06-2011', 'dd-mm-yyyy')

View 4 Replies View Related

SQL & PL/SQL :: Updating Table Statistics - View Not Appropriate

Oct 22, 2010

how many rows certain tables have.

updating the statistics for a table (with GATER_TABLE_STATS) and using NUM_ROWS then. This works fine for me as long as I am the owner of the table, but when someone else is, I always get this error: ORA-20000: Table does not exist or insufficient privileges.what privileges do I need to use GATHER_ TABLE_ STATS on all Tables, which were created by Users?

when I tried to use ANALYZE TABLE TEST_TABLE COMPUTE STATISTICS on a certain table I got the following error: a view is not appropriate here. The strange thing is, TEST_TABLE is not a view (at least it is not listed in ALL_VIEWS and is listed in ALL_TABLES, so it cant be a view right?).

Besides, is there another way to gather Table Statistics (not using Analyze Table or Gather_Table_Stats)?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved