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


ADVERTISEMENT

Compound Triggers Not Firing

Nov 1, 2011

I have rather large compound triggers that I discovered were not firing this morning, so I created a simpler compound trigger to test:

CODECREATE OR REPLACE TRIGGER "test"
FOR INSERT OR DELETE OR UPDATE OF KI_NM ON CHEMAXON.CB1ASSAYS
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
ENABLE
[code]...

It's just not firing. The tables are all in the owner's schema (who has DBA rights). My Google-fu is failing me, and I'm not sure how to start troubleshooting general trigger failure.

View 2 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 :: Compound Trigger Equivalence?

Jun 23, 2011

I have a table T_TEST, i will update the table rows, i want to add a single record in some other table let say T_A, which contains the sum of the updated difference of all the records.

let say, if i have T_TEST with values of 100, 200 and if i update it to 500, 600 (oldvalue+200) then my T_A should contain a record which is 400.

I was able to implement the solution in Oracle 11.2.0.1 using compound triggers. I would like to know how to do it in earlier versions of oracle or Is ther any other way to do it.

create table t_test ( a number);

begin
insert into t_test values (200);
insert into t_test values (200);
insert into t_test values (200);
insert into t_test values (200);

[code]...

View 12 Replies View Related

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

SQL & PL/SQL :: Database Trigger Is Not Firing?

Jun 9, 2010

I have a database trigger that fire on delete or update. It works fine. But after few days, only delete is working. When I update, nothing is happening. Then I re-create the trigger and it works fine. Then the problem comes back after few days.

The code is for both action so I wonder why this happens:

AFTER
DELETE OR UPDATE
ON AGENCY.CCS_TEMPLATE REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW

I checked the trigger on all_objects table and it is valid. On schema browser it is compiled.

View 1 Replies View Related

SQL & PL/SQL :: Update Every Tow Trigger?

Apr 27, 2011

I have one table with one row with some coefficients. If I update a coefficient from my table I want to be updated all the rows from another table. I've made a trigger but in the second table I have a column for example TOTAL and I want that column to be multiplied with my new coefficient, and I want this for every row in the second table. How I can select that total column in the trigger for every single row and update it?

View 14 Replies View Related

SQL & PL/SQL :: Trigger After Update

Jun 4, 2012

How to make a trigger of changing value. For example I have a table of customer .

Customer_id, Cumtomer_name, cus_date and stats.
Values
102, ABC,02-06-2012 and Open

Now tell me how I can make trigger after two hours stats change OPEN TO CLOSE.

View 26 Replies View Related

Trigger Performance Impact When Not Firing?

Apr 30, 2013

I'm trying to find some information on the performance impact of a trigger on a heavily updated table when the condition to fire the update trigger is NOT met. In other words I guess what I'm really trying to find out is what the performance impact of the system checking the condition on the trigger to determine if it should fire or not is.

For example I have a batch job that inserts and updates a table heavily, but the batch job almost never updates the column in question on the trigger to the value that would cause it to fire, but it does update that column to other values often.

I know about the many downsides of using triggers in general, but I'm working with a third party application, so more optimal solutions aren't an option.

View 1 Replies View Related

SQL & PL/SQL :: Trigger To Update And Insert Together?

May 7, 2010

I have to write a trigger where when the table is updated then one column named 'Status' should be updated as 'U' and if arow is inserted in the table then the column 'Status' needs to be inserted with value 'I'.

Create table test_trig
(
vempno number,
vempname varchar2(20),
status char
)

New to triggers....how to go with both insert and update conditions together.

Can we write a trigger which takes care of both insert and update. I have used Merge statement where I can write conditions based on insert/update done.

View 4 Replies View Related

SQL & PL/SQL :: How To Implement Trigger After Update

Jan 13, 2011

SQL> CREATE OR REPLACE TRIGGER TRI_ABOVE_JOINTBOX
2 BEFORE UPDATE ON JOINT_BOX FOR EACH ROW
3 DECLARE
4 PRAGMA autonomous_transaction;
5 BEGIN
6 IF (:NEW.SCALE_X <> :OLD.SCALE_X) OR
7 (:NEW.SCALE_Y <> :OLD.SCALE_Y) THEN

[code]....

The above code is for the GIS project. When I am trying to implement the above trigger it is giving output in such a way that the joint box(which is point feature in the designed database) scale is fixed to 1 as written in the code,but it cannot be moved in the DGN(front end),this is because trigger is fired before update.

Actual intention is that the feature(joint box) need to move in the DGN then the trigger need to be fired so that then scale need to fixed to one even after changing.For that I implemented after update trigger in the above code,but then it is throwing error as

ORA-04084: cannot change NEW values for this trigger type. I guess this is because after update trigger cannot be implemented for bind variables old and new.

1.joint box can move in DGN(this can be acheived automatically if after implementing after update trigger).

2.after dragging in the DGN the scale to be fixed as 1.

View 2 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

SQL & PL/SQL :: Auto Update Trigger

Apr 2, 2012

I have 2 tables where the data is related.table ot_pack_list,ot_delv .ot_pack_list contains details of items information that are packed to be delivered sometimes what happens user may remove some items from this, and the summary column of dl_wt and dl_qty in ot_delv must get automatically changed whenever there is addition in ot_pack.

create table ot_pack_list (item varchar2(12),pack_qty number,pack_wt number,pack_no number)
insert into ot_pack_list values ('a',2,10,1);
insert into ot_pack_list values ('b',3,55,1);
create table ot_delv (d_no varchar2(12),d_qty number,d_Wt number,d_pack_no number)

[Code]...

--after updating the data in ot_delv should be like..

select * from ot_delv;

d_no d_qty d_wt d_pack_no
----- ----- ---- -------
1 5 75 1

View 15 Replies View Related

SQL & PL/SQL :: Exception At Update Trigger

Aug 14, 2012

I am writing a trigger TR_EMP on a table EMP which has columns EMP_ID, EMP_NAME, ALT_EMP_ID.

Now I am updating ALT_EMP_ID for an EMP_ID(PK) which is unique.

If ALT_EMP_ID is null
then
:new.ALT_EMP_ID = l_alt_emp_id;
end if;

As this ALT_EMP_ID is unique, same ID shouldn't be inserted again here. When data being inserted with 2 different sessions for 2 different EMP_ID there is a possible chance of inserting same ALT_EMP_ID for both which results in Unique error. I need to handle this exception. DUP_VAL_ON_INDEX or OTHERS Execption not able to handle this.

View 4 Replies View Related

Trigger To Fire Whenever Update Happens?

Jan 17, 2013

I need a trigger to fire whenever an update happens. Seems easy, but there are several problems there:

- I need it to fire in case both :OLD and :NEW are the same value. i.e. I've got "5" currently in the field, and I update and set the same field to "5" again.

View 1 Replies View Related

Forms :: Firing Of When-create-record Trigger

Nov 5, 2012

I am navigating from a Master form to a child form. The when-create-trigger in the child form is sometimes executing and sometimes not. Not firing of this trigger is causing the child form to open without any initially assigned values. What is the root cause and also the scenarios where the when-create-trigger fires.

View 15 Replies View Related

Delete Record In Update Trigger?

May 24, 2012

the following case is successfully done with mssql databases.

Case:

Table UserGroup
Columns id, name, handshake

When the handshake is set to 'd', this record should be deleted. I know it is bad behaviour by design.

What have I done so far:

- created an after update trigger (mutual error) Caused by trying a delete action in the update action, not possible.

- created a view in combination of instead of update trigger.

This causes also mutual error, or if ignored (PRAGMA AUTONOMOUS_TRANSACTION), an deadlock.

Code so far:

create or replace procedure Delete_UserGroup_sp(p_groupId in USER_GROUP.HMIUSERGROUPID%TYPE, p_handshake in USER_GROUP.HANDSHAKE%TYPE)
is
begin
if p_handshake = 'd' then
delete USER_GROUP WHERE HMIUSERGROUPID = p_groupId;
commit;
end if;
end;

create or replace view USERGROUP_V as select * from USER_GROUP

create or replace trigger USER_GROUP_T1
instead of update on USERGROUP_V
for each row
declare
PRAGMA AUTONOMOUS_TRANSACTION;
begin
Delete_UserGroup_sp(:new.HMIUSERGROUPID, :new.HANDSHAKE);
end;

View 5 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

SQL & PL/SQL :: After Update Trigger (with Multiple Tables)

Nov 21, 2012

I have to write a "after update trigger". Here, i have to update the stock table by other inventory tables (by complex query).
I have written trigger below. how to make it correct?

create or replace trigger trg_stk_upd_pur
after update on O_STOCK_EFFECTS
REFERENCING NEW AS new OLD AS old
FOR EACH ROW

[Code]....

View 5 Replies View Related

Insert / Update / Delete Trigger Creation?

Feb 6, 2009

I am trying to write a trigger that will do an insert/delete/update into a audit table when a change has occurred on the primary table. The change will be recorded in the audit table by a incemental sequence number and the updated data.

there will be an extra column in the audit table

how to get a simplified version of this trigger.

the primary table will look like so
Id_num varchar (20)
code Integer
desc varchar(20)
sequence_num Integer

audit table
Id_num varchar(20)
code Integer
desc varchar(20)
timestamp date
sequence_num Integer

View 6 Replies View Related

Simulate Delete Operation Through Using Update On Trigger

Jul 27, 2008

I'm trying to simulate a delete operation through using an update on a trigger my tables are

CREATE TABLE EMPLOYEE (
LNAME VARCHAR(15) NOT NULL,
SSN CHAR(9) NOT NULL,
salary FLOAT,
dno INT NOT NULL,
vst DATE,
vet DATE,
PRIMARY KEY (Ssn));
[code]....

What I want to do is whenever there is an update on vet( valid end time) in employee, delete the values from the employee table and insert the old values from employee into the emp_history table along with the new value for vet. Here's my trigger

CREATE TRIGGER trig4
AFTER UPDATE OF VET ON EMPLOYEE
FOR EACH ROW
BEGIN
INSERT INTO EMP_HIST VALUES( : old.LNAME, : old.SSN, : old.salary, : old.dno, : old.vst, :new.vet);
DELETE FROM EMPLOYEE WHERE(SSN = :NEW.ssn AND vet IS NOT NULL);
END trig4;
//ignore the space between : and o as it makes a smily

The problem is I get an error for a mutating change, what I'd like to know is if the above trigger is possible, and if so how to implement it without giving me an error. I mean it makes sense syntactically and logically(at least to me).

View 1 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

Replication :: Trigger - After Update On Materialized View?

May 30, 2008

i want to create "on update" database trigger on materialized view which is on replication schema.I would like to update rows in table when update occurs on materialized view on master side.I wrote some code and tried with this:

CREATE OR REPLACE TRIGGER a
after update on A@dirfep.us.oracle.com
FOR EACH ROW
BEGIN

[code]...

View 1 Replies View Related

SQL & PL/SQL :: Same Logic When AFTER INSERT OR UPDATE Trigger Is Written

Feb 19, 2011

SQL> CREATE OR REPLACE TRIGGER TRI_COMPL_FEATURES
2 AFTER INSERT OR UPDATE ON COMPLEMENTS FOR EACH ROW
3 DECLARE
4 v_fno NUMBER;
5 v_tab VARCHAR2(30);
6 v_unique_id VARCHAR2(40);
[code]....

Trigger created.When I am trying to insert into complements table it is throwing error as follows:

SQL> insert into complements values(19,NULL,'5',6,7,NULL,'W2023648',NULL,NULL);
insert into complements values(19,NULL,'5',6,7,NULL,'W2023648',NULL,NULL)
*
ERROR at line 1:
ORA-20010: ORA-04091: table TEEMNGWS.COMPLEMENTS is mutating, trigger/function
may not see it
ORA-06512: at "TEEMNGWS.TRI_COMPL_FEATURES", line 19
ORA-04088: error during execution of trigger 'TEEMNGWS.TRI_COMPL_FEATURES'

I can understand that I am trying to perform DML operation(i.e select) on table which trigger is fired.But how can I implement the same logic when AFTER INSERT OR UPDATE trigger is written.

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







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