Using Trigger On One Table To Update Another Table

Aug 28, 2011

My problem is the following:

At the time when P_delivery_date changes in P_ORDERS I want to transfer P_delivery_date to S_delivery_date in S_ORDERS for corresponding records.

Tables:

purchase orders table P_ORDERS:
P_order_number,
P_poz_number,
S_order_number,
S_poz_number,
P_delivery_date

sales orders table S_ORDERS:
S_order_number,
S_poz_number,
S_delivery_date

My question is:

Is it possible and what would be the solution using the TRIGGER on table P_ORDERS to update S_delivery_date with P_delivery_date in S_ORDERS?

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: Create Trigger To Update Table B After Update On Table A

Jul 21, 2011

I have table test1(id,name) and table test2(id,,name)

Now when I update name column of a row on test1 I want the same value to be updated for the same id in test2.

So I wrote this trigger but its not working

create trigger test_trigger after update on test1 for each row
begin
update test2 set name=new.name where test2.id=id
end
/

View 9 Replies View Related

Forms :: Insert And Update Directly Into Table From Pre Update Trigger Of Block?

May 14, 2010

I have a base table (Table A) block with multiple records displayed. I need to track audits to this underlying table in the following way:

If user updates a field in the block I want the pre-changed record's audit fields to be set and I need to create a copy of the record with the changed values. Basically any changes will result in the record being logically deleted, and a copy record created with the newly changed values.

Tried to implement in the block's pre-update trigger which will call a package to directly update Table A then Insert into Table A, then requery the block. Is there a clean and efficient way to do this?

View 4 Replies View Related

SQL & PL/SQL :: Create Trigger That Will Update Table When There Is Insert / Update

May 29, 2012

i want to create a trigger that will update a table when there is an insert or update.i can't across this error that i don't even know what it means "table %s.%s is mutating, trigger/function may not see it".

*Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.

*Action: Rewrite the trigger (or function) so it does not read that table.

CREATE OR REPLACE TRIGGER set_date_end
BEFORE INSERT OR UPDATE OF issued ON shares_amount
FOR EACH ROW
DECLARE
BEGIN
INSERT INTO shares_amount(date_end) VALUES(SYSDATE);
END set_date_end;
/

View 3 Replies View Related

SQL & PL/SQL :: How To Update Another Table Using Trigger

Nov 24, 2011

CREATE or REPLACE TRIGGER Emp_Ins_Upd_Del_Trig
BEFORE delete or insert or update on EMP
FOR EACH ROW
BEGIN
if UPDATING then

[Code] .....

View 2 Replies View Related

SQL & PL/SQL :: Trigger - Does Not Update Table?

Nov 24, 2010

I am ceating a trigger to...when I update or insert a record in table and if salary of that record is less than 1600 than it should be updated to 2000. My trigger is

CREATE or REPLACE TRIGGER myTrigger
BEFORE UPDATE OR INSERT ON newemp
FOR EACH ROW
BEGIN

[code]...

There are no errors with trigger while compiling, but when I update or insert a record, it does not update the table. It looks like the trigger is being ignored.

View 2 Replies View Related

Oracle Trigger - Update Same Table?

Mar 30, 2010

I am trying to create some PL/SQL that will create a trigger for UPDATES and INSERTS which will update a column on the same row to the system date.

So far I have:

DECLARE
tablename VARCHAR(30) := 'table';
triggername VARCHAR(30) := 'mytrigger_' || tablename;

[Code]....

I have declared the tablename and triggername outside the trigger creation as I need this to be parameterised for a list of tables.

The procedure IsCDCUser just checks it should be updating for this user.

The problem I get is the following error:

Bind Variable "NEW" is NOT DECLARED

View 3 Replies View Related

SQL & PL/SQL :: Insert Or Update Trigger On Table B

Jun 2, 2011

Select * from Table A where emp=1;
Emp AID
1 111

select * from Table B where emp=1 ;
----------
AID, emp, start_dt, End_dt
111 1 01-jan-2011 31-dec-2011
112 1 01-jan-2011 31-dec-2011
113 1 01-jan-2011 31-dec-2011

I have After insert or update trigger on Table B. This will update AID column on Table B.

after insert update on table B
update A
set AID=:new.AID where emp=:new.emp;

if i end date any record on table B i wanted to update max(AID) on Table A.

EX update table b
set end_dt= sysdate-1
where aid=111;

So I wrote the trigger on Table A as below

before insert or update on trigger A
declare
v_aid number;
v_strt_dt date;
v_end_dt date;
begin

select max(AID) from table B
where emp=:new.emp;
select v_end_dt from table B where aid=:new.Aid;
if v_end_dt<sysdate then
:new.aid:=v_AID;
end if;

But the problem here is its giving mutating error.Then i tried with autonomus transaction but it willreturn old value when it fiers.

So how can i achive both the task at a time.That means i have to endate Table A , Same time i have to update active max(AID) to table B.

View 13 Replies View Related

SQL & PL/SQL :: Create Trigger On Table After Update?

Apr 16, 2013

i have created a trigger on a table after update. i am using if condition if the condition is true i am passing a value and then inserting into audit table.

if(condtion)
L_change_type='value'
end if;
if(condtion)
L_change_type='value'
end if;
if(condtion)
L_change_type='value'
end if;
if(condtion)
L_change_type='value'
end if;

Then i am inserting

Insert into audit_table(change_type.....)
values(..L_change_type)...)

If i want to skip the insert statement for particular condition wt i have to do.

View 7 Replies View Related

SQL & PL/SQL :: Update Table In After Insert Trigger

Feb 1, 2011

I am calling an after insert Trigger on table1.

In the trigger I am calling a procedure that returns an error if there is any error returned from procedure. I have to update the table table1's column error_desc (for the same new inserted record for which the trigger was called) with the error received by OUT parameter of procedure called in trigger. I have to update the same record on whose insert this trigger was called.

View 3 Replies View Related

Create A Trigger Before Update On Table Test1?

Dec 25, 2010

i have two table:

QUOTE create table test1(
num number(4),
name varchar2(50));
create table test2(
num1 number(4),
name1 varchar2(50));

then i want to create a trigger before update on table test1 that i want to insert in table test2 the old values in table test1:

for example

QUOTE insert into test1(1,'cocol');
QUOTE update test1
set name='oracle' where num=1;

so i want the trigger to insert 1 and cocol into the table test2.

View 1 Replies View Related

Can Trigger Prevent Insert And Update To Table

Mar 4, 2013

We would like to create functions to insert and update our tables and would like to make it not possible to update and insert the table directly outside of the function. Is there a way to do that in the trigger?

View 1 Replies View Related

SQL & PL/SQL :: Trigger Fired - Final Update To Base Table Not Happening

Jul 1, 2011

In my DB,We have a table rqst_list

Create table rqst_list(id Number, --(PK)
pkg_name varchar2(100),
status varchar2(100))

When a record is inserted into this table(rqst_list), a trigger is fired which calls the package which is there in pkg_name.The function does some function.If there are no exceptions while executing the package, then SUCCESS needs to be updated in the table for the id for which the trigger got fired.I have used AFTER INSERT ROW level trigger.

But,the final UPDATE (UPDATE the rqst_list table to SUCCES/FAILURE)to the base table inside the package is not happening.

View 5 Replies View Related

SQL & PL/SQL :: AFTER INSERT Or UPDATE Trigger Failed - Mutating Table Error

Jul 4, 2013

Table A basically has 4 rows of interest, an outside event changes/inserts let's say row 1.

Row 2 and 3 have to be changed in a trigger depending on the new value of 1.(or not if the value stays the same)

Row 4 is the reference for the update and will not be changed.

My first simple AFTER INSERT or UPDATE trigger obviously failed because of the mutating table error.

View 19 Replies View Related

Creating SQL Script That Can Update Info From One Table In Dbase1 To Another Table In Dbase2?

May 16, 2013

creating an sql script that can update info from one table in dbase1 to another table in dbase2 that has the same columns and if possible insert date and time in one column when the synchronized is done?

View 3 Replies View Related

Application Express :: Insert Into History Table When Update Action Is Performed On Tabular Form View Or Table

Aug 24, 2013

My scenario is I need to insert into History table when a record is been updated into a tabular form(insert the updated record along with the additional columns Action_by,Action_type(Like Update or delete) and Action Date Into History table i.e History table contains all the records as the main table which is been visible in tabular form along with these additional columns ...Action_by,action_type and action_date.

So now i dont want to create a befor/after update trigger on base table rather i would like to create a generic procedure which will insert the updated record into history table taking the page alias and pade ID as the parameters(GENERIC procedure is nothing but whcih applies to all the tabular forms(Tables) contained int he application ).

View 2 Replies View Related

PL/SQL :: Trigger To Insert In One Table From Other And Truncate 2nd Table

Aug 2, 2012

I have table t1 and t1 , I want a procedure that will insert all records from t1 into table t2 and after successfull insert table t1 should be truncated .

If their is any problem in insert in to table t2 , the truncate command should not work .

Truncate command should work only after successfully insert command .

View 3 Replies View Related

Performance Tuning :: Update Columns Of One Table Using Another Table

Feb 6, 2011

I am trying to update columns of Table A with the columns of Table B. Both these tables have 60,000 rows each. I tried this operation using following 2 queries:

Query 1

Update TableA A
set
(A.col1,A.col2,A.col3)=(select B.col1,B.col2,B.col3
from TableB
where A.CODE=B.CODE)

Query 2
Update TableA A
set
(A.col1,A.col2,A.col3)=(select B.col1,B.col2,B.col3
from TableB
where A.CODE=B.CODE)
where exists
A.code = (select B.code
from TableB B
where A.code=B.code)

When i execute these two above queries, it keeps executing indefinitely.

View 4 Replies View Related

Oracle 10g - Take Snapshot Of Table Before Insert Or Update Happens To That Table

Dec 28, 2010

I need to take a snapshot of a table before insert or update happens to that table.... in oracle 10g. I am reading the MV docs from oracle and below link..

[URL].......

how MV should be written for this and how to schedule it in dbms_jobs for auto refresh?

assuming that t1 is the table where DML operation are goin to happen so before any insert or update, snapshot has to be taken, and I am assuming that to do this it would look something like this?

create materialized view my_view refresh fast as select * from t1;

create materialized view log on my_view;

and then schedule in a dbms_jobs?

View 1 Replies View Related

SQL & PL/SQL :: Update A Table Using Another Schema Table Of Same Style?

Aug 14, 2011

Can we update a table using another schema table of same style?

Example:

Update employees
set employee_name=hr.employees.employees_name
where employee_id=100;

View 8 Replies View Related

SQL & PL/SQL :: Update Table From Other Table Over Database Link

Aug 14, 2010

i have two databases and created the link between them. I can easily query the data but when i need to update my local records from the remote its showing an error

SQL> update laptop set name =
2 (select name from laptop@ora_link1 where id between 2 and 4)
3 where id between 2 and 4;

(select name from laptop@ora_link1 where id between 2 and 4)
*
ERROR at line 2: ORA-01427: single-row subquery returns more than one row

select multiple rows from the remote db and update them in the local db.

View 2 Replies View Related

SQL & PL/SQL :: Update Table After Insert To Another Table

Aug 8, 2012

how to adjust a total (counter) after a record is inserted into a table.

the dilemma i am facing is we are using third party software for our fundraising operations so I have no control over what gets done in the background as users process their daily batches into the system. below is the scenario:

create table paytable(
idnumber number(12),
appealcode varchar2(20),
batchno number(8),
trantype varchar2(3),
transnum number(8),
split_transnum number(8));

[Code]..

IDNUMBER APPEALCODE BATCHNO TRA TRANSNUM SPLIT_TRANSNUM
---------- -------------------- ---------- --- ---------- --------------
16084 DVFG1206 20120808 PP 23853576 1133821
16084 DVFG1206 20120808 PPO 23853577 1133821
1234 DVFG1206 20120808 PP 23853578 1133822

create table appealtable
(appealcode varchar2(20),
total# number(9),
total$ number(13,2));

[Code]...

APPEALCODE TOTAL# TOTAL$
-------------------- ---------- ----------
DVFG1206 100 2500

during batch posting records are inserted into the paytable, on some pledge donations donors will send overpayments when fulfilling a PLEDGE(as is the case with donor 16084) therefore the system will split the payment during the process and will assign a trantype of 'PP' to the exact pledge amount and a 'PPO'(pledge payment overage) towards the balance. additionally as records get inserted into paytable there is counter of those paytable records going into the appealtable for that particular appealcode so in the case above when batchno 20120808 is completed appealtable.total# will show 103 and total$ will show $2532($10,$12,$10,,,I did not include payment$ since that is not the focus of this issue and will not change).

mgt wants the counter into the appealtable to be 2 instead of 3 records since the two records that were split(same split_transnum) should be recorded as one response not two.

I have tried writing an after insert trigger(dreaded mutating table error) and can't seem to figure out how to update the counter to the appealtable after records are inserted into paytable. below is some code I've been working with but it's not working.

CREATE OR REPLACE TRIGGER PPO_Payment
AFTER INSERT ON paytable
FOR EACH ROW

DECLARE
p_cnt NUMBER :=0;

[Code]...

show errors

View 1 Replies View Related

PL/SQL :: Update Table Based On The Value Of Another Table

Apr 21, 2013

Using Oracle 11g SOE R2...

I used to have two tables to store details of PROPERTIES e.g UNIT_COMMERCIAL , UNIT_RESIDENTIAL. I need to combine both of them into one table called UNIT. I moved the data to the new table, but now I am stuck how to update the values of the child table which called MARKETING.

---

Now, I have two tables:

MARKETING (ID   NUMBER  PK , OLD_UNIT_ID NUMBER FK ...)
UNIT (NEW_ID  NUMBER  PK , OLD_UNIT_ID  NUMBER  ... )

I need to update the value of OLD_UNIT_ID in Marketing table to be equal to NEW_ID in the table of UNIT. As you can see the values of OLD_UNIT_ID in both tables are the same.

I used this statement

update ( SELECT  distinct  M.UNIT_ID , U.OLD_ID , U.ID FROM MARKETING M INNER JOIN UNIT U ON (M.UNIT_ID = U.OLD_ID))  set unit_id = idBut I got this error:

ORA-01732: data manipulation operation not legal on this view??

View 8 Replies View Related

PL/SQL :: Update Table With Values From Another Table

Dec 19, 2012

I've tried to Update table with values from another table, but I couldn't.Is there a single way to do that with just one query?Below goes an example:

CREATE TABLE TEST1 (
     COD NUMBER,
     DCR VARCHAR2(10) );

CREATE TABLE TEST2 (
     COD NUMBER,
     DCR VARCHAR2(10),
     DCR2 VARCHAR2(10) );
[code]....

I want to UPDATE the field DCR of the table TEST1 with the VALUE of the field DCR2 of the table TEST2.At the end, after the update, the table TEST1 would be like that:

SELECT * FROM TEST1

COD --- DCR
------------------
1 'TESTE3'

View 6 Replies View Related

PL/SQL :: Update Row In Table And Insert Same Row Into Another Table?

Sep 6, 2013

I want to update a row in a table say Table A and the updated row should be inserted into another table say Table B. I need to do it in a single SQL query and i don't want to do it in PL/SQL with triggers. And i tried with MERGE statement but its working with this scenario.

(Note: I'm using Oracle Database 10g Enterprise Edition Release 10.2.0.1.0).

View 8 Replies View Related

SQL & PL/SQL :: How To Update Multiple Table With Single UPDATE

Jul 19, 2011

I have a column "empno" in EMP table and "deptno" in DEPT table . I want to update both the columns with single UPDATE statement. With out a creation of stored procedure or view(updating it through view).

View 4 Replies View Related

SQL & PL/SQL :: Compound Trigger Update Firing Update

Jan 25, 2012

After many tests I can't make work and update of the same table inside the same table.

Trying to avoid Mutating Table Error now I have
ORA-00036: maximum number of recursive SQL levels (50) exceeded

Sample Data :

create table test_compound (USERID VARCHAR2(10),APP VARCHAR2(15),LAST_UPDATED_ON TIMESTAMP);

insert into test_compound values ('user1','1',systimestamp);
insert into test_compound values ('user2','2',systimestamp-4);
insert into test_compound values ('user3','3',systimestamp-6);

CREATE OR REPLACE TRIGGER trigger_test
FOR UPDATE ON test_compound
COMPOUND TRIGGER
TYPE t_tab IS TABLE OF VARCHAR2(50);
l_tab t_tab := t_tab();
[code].......

When I execute :

update test_compound
set last_updated_on=systimestamp
where userid='user1' and app='1';

The trigger should update the first row and all the data from test_compound table where userid='user1'. Maybe the problem is that updating the same table inside the trigger is firing in a recursive way the trigger.

View 13 Replies View Related

Update Statement Which Updates User IDs In One Table With User IDs In Another Table?

Jun 17, 2009

I am trying to write an update statement which updates the User IDs in one table with the User IDs in another table. However I need to update statement to ignore any duplicates that are in the tables.

View 4 Replies View Related

SQL & PL/SQL :: Update Table From Another Table?

Feb 23, 2011

I want to update table1 from table2 I wrote statement like this:

UPDATE
TABLE1 t
JOIN TABLE2 tmp
ON t.username = tmp.username
SET
t.password = tmp.password;

It works fine in my MYSQL DATABASE. BUT in ORACLE 9.2 DATABASE, I got Error like this:

JOIN TABLE2 tmp
*
ERROR at line 3:
ORA-00971: missing SET keyword

I tried inner join or natural join => same error.

View 6 Replies View Related

SQL & PL/SQL :: Update Table From Other Table?

May 8, 2010

i want to update one table form an other table, for example

SQL> select * from zz;

X VALU
---------- ----
25 abc
18 xyz
14 uvw
12 uvw
26 abc
12 uvw
30 asdf
14 uvw
22 kmn

SQL> select * from xy;

X
----------
12
14
18
22
14
30
25
12
26

9 rows selected

we have two table ZZ AND XY (There is no primary key constraint on these table), ZZ contain two field i.e "X" and "VALU" and XY contain only one field i.e is X. In table ZZ and XY the field X have the same value but not in the same sequence and X have also the duplicated value in both table. now i Add new column in Table XY i.e Valu

SQL> alter table xy
2 add (valu varchar2(6))
3 ;

Table altered.

SQL> select * from xy;

X VALU
---------- ------
12
14
18
22
14
30
25
12
26

9 rows selected. now i want to update the table XY from the table ZZ and set the "valu" in such a manner that the value of field ZZ.Valu insert in the table XY. and it final display the result i.e.
X VALU
---------- ------
12 uvw
14 uvw
18 xyz
22 kmn
14 uvw
30 asdf
25 abc
12 uvw
26 abc

View 12 Replies View Related







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