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


ADVERTISEMENT

SQL & PL/SQL :: Create Trigger Not Working For Auto-increment

Jun 16, 2010

Step1

CREATE TABLE test
(id NUMBER PRIMARY KEY,
name VARCHAR2(30));

Step2

CREATE SEQUENCE test_sequence
START WITH 1
INCREMENT BY 1;

Method1 Follow Step1 and Step2 and create a Trigger as below :

CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT
ON test
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT test_sequence.nextval INTO :NEW.ID FROM dual;
END;
/

Method 2 Follow Step1 and Step2 and directly have an insert statement as below:

INSERT INTO test (id, name) VALUES (test_sequence.nextval , 'Jon343');

Method 1, the Trigger is not getting created, however by following Method 2, I am able to generate auto-increment number.

View 8 Replies View Related

SQL & PL/SQL :: How To Auto Increment Number - Create Sequence And Trigger

Nov 26, 2012

I am switching database from access to oracle 11g. I have create all the required tables, but I am stuck at one point. The previous person who created access database had auto increment with SG0101, SG0102,........ In oracle I know we can auto increment primary keys but only with the numbers not with characters.

So I have customerid which is a primary key and it automatically increments the number, but I have one more column with memberid where I am inserting all the ids that start with SG0101 bla bla.....

I already have 800 member ID's that start with SG, but that value doesnt automatically increment because I dont have any sequence or trigger to do that.

So how do I create a sequence and trigger that will automatically start value with SG and keeps auto incrementing?

View 12 Replies View Related

Update With Case Clause Auto Commits

Nov 26, 2012

i tried the following update on one table:

update siebel.s_contact
set marital_stat_cd =
case
when (marital_stat_cd = 'Casado') then 'Married'
when (marital_stat_cd = 'Solteiro') then 'Single'
when (marital_stat_cd = 'Divorciado') then 'Divorced'
end

As you can see i forgot the else, so my update is wrong.

I thought i could rollback the update issuing the rollback statement, but when i have issue the rollback, the i query the table to confirm that the update was rollbacked and for my suprise the update is commited.

I didn�t issue the commit statement after the update and i confirmed that the auto-commit feature to worksheets is disabled, so i don�t understand whit the update was commited.

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

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

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 :: 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

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

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

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

SQL & PL/SQL :: Create Update Trigger That Allows Any Quantity Reduction Of 5 Units

Oct 27, 2011

i am trying to create a update trigger that allows any quantity reduction of 5 units and if the reduction is more than 5 units it blocks it and just reduces it by 5.here are two scenarios

SQL Update statement where the new Quantity value being smaller than 5 is allowed by the trigger.

-- old qty 20
update purchase_order
set quantity = 18
where po_no = 'PO11';
-- new qty 18

SQL Update statement where the new Quantity value being larger than 5 is only reduced by 5 by the trigger.

--old qty 25
update purchase_order
set quantity = 19
where po_no = 'PO15';
-- new qty 20

i just have the basic trigger code, but i think it is the math that i am not getting (i was never good at math)

create or replace trigger purchase_quantity_updt
before update of quantity on purchase_order
for each row
when (new.quantity < old.quantity )
begin
-- not sure what to put here
end;

View 5 Replies View Related

SQL & PL/SQL :: Trigger To Update Table1 Based On Condition Of Values In Table2

Feb 20, 2012

I have two tables as

Table LEAVE
Column Type Null Description
APP_NO Number(6,0) Not Null PK Leave Application Number
ECN Number(6,0) Not Null FK Employee Code Number
APP_Date Date Not Null Date of Application
From_Date Date Not Null Date from which the leave starts
TO_Date Date Not Null Date upto which the current application leave remains i.e. end of leave applied for date
NO_OF_Days Number(2,0) Not Null Difference between TO_Date and From_date
LEAVE_TYPE VARCHAR2(3) Not Null Can be one of SL, CL, LWP or LTA
Status VARCHAR2(25) Not Null Can be one of Saved, Rejected or Approved
Remark VARCHAR2(100) Nullable Reason to be put if status is rejected
[code]....

What I really want to do is that when a record is inserted in the LEAVES table (an application for leave is submitted by any employee and if it is approved) then I want to update the _USED values of the corresponding LEAVE_TYPE in the LEAVEENTITLE table which holds values of types of leaves entitled to employee.

For example if 3 rows are inserted in the LEAVES table as
INSERT INTO LEAVES (APP_NO,ECN,FROM_DATE,TO_DATE,APP_DATE,NO_OF_DAYS,LEAVE_TYPE,STATUS,REMARK)
(1,1234,'2012-01-01','2012-01-05','2012- 01-01',5,'SL','APPROVED',null);
INSERT INTO LEAVES (APP_NO,ECN,FROM_DATE,TO_DATE,APP_DATE,NO_OF_DAYS,LEAVE_TYPE,STATUS,REMARK)
(2,1235,'2012-01-01','2012-01-05','2012- 01-01',5,'CL','SAVED',null);
INSERT INTO LEAVES (APP_NO,ECN,FROM_DATE,TO_DATE,APP_DATE,NO_OF_DAYS,LEAVE_TYPE,STATUS,REMARK)
(3,1236,'2012-01-01','2012-01-05','2012- 01-01',5,'LTA','REJECTED','Clash with the annual meet, revise dates');

Then the value of SL_USED in the LEAVEENTITLE table of record corresponding to the ECN = 1234 should be updated with +5 and naturally the SL_ UNUSED value of the record should be updated as SL_ENTITLED - SL_USED. For the APP_NO 2 and 3 none of the values in LEAVEENTITLE should be updated as the STATUS is not 'APPROVED'

I tried with the following trigger, but is compiling with a warning (not showing what the warning is)

CREATE OR REPLACE TRIGGER leaveentitle
AFTER INSERT ON LEAVES
FOR EACH ROW
BEGIN
UPDATE LEAVEENTITLE LVE
SET LVE.SL_USED = SL_USED+(CASE
WHEN :NEW.LEAVE_TYPE = 'SL'&& NEW.STATUS='APPROVED'
THEN :NEW.NO_OF_DAYS
SL_UNUSED=SL_ENTITLED - SL_USED
ELSE 0
END),
[code]....

View 9 Replies View Related







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