SQL & PL/SQL :: ORA-04091 After Insert Trigger?

May 24, 2011

In

CREATE table ALPHA (
ID NUMBER,
FK_NR NUMBER,
INPUT NUMBER,
OUTPUT NUMBER,
MY_COUNT NUMBER,
ACTUAL_TOTAL NUMBER
);

I would like to record new inputs or outputs andthe column MY_COUNT should display the actual count of records for INPUT (actually MOD(INPUT, 10) ) regarding FK_NR and ACTUAL_TOTAL should display the actual sum for all input/output for FK_NR.

--ID normally comes from a sequence and BEFORE INSERT TRIGGER

EXAMPLE
INSERT INTO ALPHA(FK_NR, INPUT, OUTPUT)
VALUES (1 ,10,NULL);
INSERT INTO ALPHA(FK_NR, INPUT, OUTPUT)
VALUES (1 ,10, 50);

[code]...

Table ALPHA looks like:
1,1,10,NULL,1,10
2,1,10,50,2,-30
3,2,NULL,20,0,-20
4,2,20,40,2,-40
5,3,40,20,4,20
6,3,-10,NULL,3,10

How can this be achieved ?I started with a

CREATE OR REPLACE
TRIGGER "BI_ALPHA"
BEFORE INSERT ON ALPHA
FOR EACH ROW
BEGIN
SELECT ALPHA_SEQ.nextval INTO :NEW.ID FROM DUAL;
END;

and thought of a

CREATE OR REPLACE TRIGGER "AI_ALPHA"
AFTER INSERT ON "ALPHA"
FOR EACH ROW
DECLARE
l_maxidNUMBER;
l_fknr NUMBER;

[code]...

But by inserting a row, this results in ORA-04091

View 6 Replies


ADVERTISEMENT

ORA-04091 - Using A Trigger To Subtract Ncopies Values Of Table

Oct 30, 2008

i'm getting ORA-04091: error when using a trigger to subtract ncopies values of table (rental ; after i insert a NEW record in it ) from cno value of table (copies) to give me the remaining no of copies of a book .

my tables and code is as folllows:

1)COPIES;
CID CNO BOOK
----- ---------- --------------------
A1 5 BOOK1
B1 10 BOOK2

2)rental;
Name Null? Type
------------------------------- ---
RID NOT NULL VARCHAR2(5)
CID VARCHAR2(5)
NCOPIES NUMBER(2)

1 CREATE OR REPLACE TRIGGER NO_COPIES
2 AFTER INSERT ON RENTAL
3 FOR EACH ROW
4 DECLARE
5 K VARCHAR2(5);
[code]....

View 3 Replies View Related

Before Insert Trigger

Nov 12, 2006

I have a table known as Registration in which there are columns StudentID, ModuleId, year, semester

semester = 1 or 2

I want to write a trigger so that student can register on a maximum of 4 modules per semester

i.e.

select count(*), semester from Registration where studentid='241234' group by semester

I tried the following trigger but I am stuck

If I dont use "for each row", I am not allowed to use :new and ld and when I use "for each row", count would be for each row which would be 1 or 0

create or replace trigger checkmodulecount
before insert on Registration
DECLARE
noCourse NUMBER;
nCount NUMBER;
FAILED EXCEPTION;

[Code]....

WHEN FAILED THEN

raise_application_error(-20000,'Cannot reigster for more than four courses');
END;

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

PL/SQL :: Trigger For To Insert Other Rows

May 2, 2013

I must to build triggers that insert other two rows when the user insert the first record, but the First record must to change Seq to 2 (was 1) then in trigger to insert other record with seq equal 1 and more other record with seq equal 800, I tried some ways , but return error

First insert the user from application

INSERT INTO ARCTB_ACAO_IMEDIATA ( CD_ARC,  CD_TIPO_ACAO,  CD_ACAO_IMEDIATA,  DS_ACAO,  CD_RESPONSAVEL,  DT_ORIGINAL,  DT_ABERTURA,  NR_PRAZO,DT_PREVISTA, DS_HISTORICO ) VALUES (9732, 0,1, 'TESTE','ucrwilma', To_date('02/05/2013','DD/MM/YYYY'), To_date('02/05/2013','DD/MM/YYYY'), 1, To_date('02/05/2013','DD/MM/YYYY'), '');I create triggers
CREATE OR REPLACE TRIGGER ARCTR_ACAO_IMEDIATA_I
BEFORE INSERT

[Code]....

END ARCTR_ACAO_IMEDIATA_IUreturn me error
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "CLIBGF.ARCTR_ACAO_IMEDIATA_IU", line 221
ORA-04088: error during execution of trigger 'CLIBGF.ARCTR_ACAO_IMEDIATA_IU'
ORA-06512: at line 7using 9.2.08

View 2 Replies View Related

PL/SQL :: Insert Using Trigger And View

Jun 13, 2012

I'm trying to insert data in my_second_table using a trigger and a view when I insert the data in my_first_table but there's something wrong in the code.

CREATE OR REPLACE TRIGGER my_trigger
  AFTER INSERT ON my_first_table
  FOR EACH ROW
BEGIN

[Code]...

I'm suspecting that the problem is in the :new.cod_emp

P.S.: I'm using: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production on Enterprise Linux Server release 5.5 (Carthage)

View 15 Replies View Related

SQL & PL/SQL :: Trigger Insert - Create Or Replace?

Jun 12, 2013

I have make a new trigger.Create a trigger that inserting a new job_id MAX_SALARY assigned as the employee's salary more than 80 departmental charges

I have that code, is that correct?

CREATE OR REPLACE
TRIGGER TR27
AFTER INSERT ON JOBS FOR EACH ROW
BEGIN
(SELECT MAX(SALARY) FROM EMPLOYEES WHERE DEPARMENT_ID=80);
:NEW.MAX_SALARY := :OLD.MAX_SALARY;
END;

What I need to complete it?

View 2 Replies View Related

SQL & PL/SQL :: Trigger To Insert Record Into Another Table

Aug 17, 2010

writing a trigger body. My requirement is i need to insert a new record in a task table when ever a new record is inserted into employee table.Here in the trigger i need to select the name of the employee in the last inserted row in employee table and insert the name in task table.I tried to write the code as below

insert into task(name, date, type) values ((select name from employee where emp_id=(select max(emp_id) from employee), sysdate, 'document'));

When i am trying to insert record using trigger, it is taking last but one record from the employee table.

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

Oracle To MSSQL INSERT TRIGGER?

Oct 30, 2006

I must create an INSERT trigger, on an Oracle table, which will do an insert into my MS-SQL 2000 DB table.

The tables are exactly the same in this case and I desire to insert the entire row that was just insterted into the Oracle table into the MS-SQL table.

I understand how to create an ODBC connection between the DB servers, I just can't seem to understand the trigger syntax.

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

SQL & PL/SQL :: Insert With Select And Subquery Inside Trigger

May 5, 2011

I hit a bottleneck where my insert trigger won't execute the insert statement (with subquery). See illustration below:

Step# 1 - Table definition:
Table_A(a1 number, a2 varchar(10), a3 varchar(10))
Table_B(b1 number, b2 varchar(10), b3 varchar(10))
Table_C(c1 number, c2 varchar(10), c3 varchar(10))

Step# 2 manipulated the tables:
Inserted 3 records in Table_C.

Then I created an Insert Trigger to Table_A with an insert statement into Table_B and a subquery to Table_C. Please see below:

CREATE OR REPLACE TRIGGER TABLE_A_TR
after INSERT OR UPDATE OR DELETE ON TABLE_A
FOR EACH ROW
DECLARE
[code]......

Step# 3 compiled the created trigger and I've successfully compiled it.
Step# 4 Tested the trigger (TABLE_A_TR) using an insert statement to TABLE_A.
Insert into TABLE_A values (1,'testa','testb')

I've successfully insert the values into TABLE_A however I've observed that the trigger didn't execute the insert statement because TABLE_B has an empty rows. I tried to manually execute the insert statement just to see if there's an issue in my insert statement but I've successfully populated the values into TABLE_B. So I'm wondering why the trigger didn't execute the insert statement.

View 4 Replies View Related

Forms :: Date Validation In PRE-INSERT Trigger?

Feb 1, 2013

In the attached PRE-INSERT Trigger Code i need to do a validation. Validatation: If the Date range is between FEB 6 2013 and MARCH 1 2013 then the code should work ELSE It should throw a message

Check_Product_Title;
Begin
if :prod2.internet_product_flag = 'Y' and :prod2.brnd_code is null then
soft_messages('E',TRUE,'Brand code must be specified for CCH online product');
end if;

[Code]....

View 3 Replies View Related

Forms :: Calling Validation In Pre-insert Trigger

Nov 10, 2010

my purpose is when PRE-INSERT trigger fires validation should be done like date format , primary key in table if validation is ok then a value of text box should be set to sequence no . else it should generate message

View 9 Replies View Related

PL/SQL :: Trigger To Increment A Non-pk Field After Insert In Another Table

Feb 1, 2013

I have three tables.
One for projects, one for volunteers, and a bridge entity for the many to many relationship between the Project and Volunteer.

In Project table, I have a field called, Volunteers_currently_signed_up, which means the number of volunteers currently signed up to participate in a project.

When I add an entry to my bridge entity which is composed of Volunteer_ID and Project_ID, I want the Volunteers_currently_signed_up to increment by 1, where the Project_ID in the bridge entity corresponds to that in Project.

I have very very little PL/SQL, and this is my amateur attempt so far:

CREATE OR REPLACE trigger "BI_Volunteers_currently_signed_up"
BEFORE INSERT OR UPDATE ON Volunteers_in_project
for each row
WHERE Volunteers_in_project.Project_ID=Project.Project_ID;
begin
Project.Volunteers_currently_signed_up += 1;
end;
/

write a trigger that achieves the above

View 7 Replies View Related

SQL & PL/SQL :: Insert A Trigger That Jazz Concerts Cannot Be Run In January

Jun 1, 2010

I have 2 table events and concerts. The event table has event_id, concert_id and event_date fields, the concert table has concert_id, name, artist, type and cost.

I need to insert a trigger that 'jazz' concerts cannot be run in January. This is my attempt:

CREATE OR REPLACE TRIGGER rock_december
BEFORE INSERT OR DELETE OR UPDATE ON event
FOR EACH ROW
IF event.type = 'jazz' and AND ((event.event_date) BETWEEN
'01-DEC' And '31-DEC'))
RAISE_APPLICATION_ERROR(-20100,'Jazz event can not be booked in January');
END IF;
END;

View 8 Replies View Related

SQL & PL/SQL :: Difference Between After / Before Insert In Row Or Statement Level Trigger

Feb 8, 2012

what is the difference between after or before insert in row level trigger and statement level trigger.

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

PL/SQL :: Create Or Replace Trigger After Insert On Transfer?

Sep 1, 2013

create or replace trigger aifer_transfer after insert on transfer for each new row begin     

   UPDATE Account SET balance = balance-:new.amount        WHERE acc_id = from_acc_id;        UPDATE Account SET balance = balance+:new.amount        WHERE acc_id = to_acc_id;      end if; end;     create or replace trigger bifer_transfer before insert on transfer for each new row begin      if get_balance(:new.from_acc_id) < :new.amount  then        raise_application_error(-20001, 'Not enough money in account!');        end if; end;   create or replace function get_balance(p_acc_id in number) return number as v_balance account.balance%type; begin select balance into v_balance from account where acc_id = p_acc_id; return v_balance; end;    select get_balance(123) from dual..................................................i am geting this error when executing the trigger..................................................Error report:ORA-01912: ROW keyword expected01912. 00000 -  "ROW keyword expected"*Cause:    keyword missing*Action:  

View 12 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 :: INSERT Trigger Will Generate Sequence ID From AA001 To AA999 Value

Feb 25, 2013

I am using Oracle 10G version. I need a code base for new Sequence Trigger.

Requirement : As per the request, before INSERT trigger will generate the sequence ID from AA001 to AA999 value. But once the sequence is reached to AA999, the next sequence value will be generated normal (start from AB001 etc..).

View 6 Replies View Related

SQL & PL/SQL :: Create Trigger To Change Every Insert In Table To Sysdate

Feb 17, 2011

I have table 'A' with column 'ID','NAME','IN_DATE','PHONE','EMAIL'

Now I have to create a trigger such that on every insert in the table 'A' the value of column 'IN_DATE' changes to sysdate.I m not good in PL/SQL

View 4 Replies View Related

SQL & PL/SQL :: Insert Statement In Trigger Accessing And A Select From A Table

Jun 6, 2010

In a trigger(on update of a table t1) I am trying to write, I am doing an insert on t2 accessing ':new' values of the update on t1.

But in my Insert statement, I am having get one of the column values from another table. How can I write my insert statement in such a way as to insert values contained in ':new' pseudo columns and a select from another table. Below is my insert statement in the trigger :
-------

IF (:old.GROUP_YELLOW <> :new.GROUP_YELLOW) THEN
INSERT INTO TEST.W_THRESHOLD_LOG
(THRESHOLD_LOG_WID, CHANGE_DATE, MEASURE_TYPE_WID, MEASURE_NAME, CUSTOMER_WID, CUSTOMER_NAME, USER_ID, CHANGED_ITEM, PREV_VALUE, NEW_VALUE)
VALUES(TEST.W_THRESHOLD_LOG_SEQ.NEXTVAL, SYSDATE, :new.MEASURE_TYPE_WID, 'Rolling Stabilty' , :new.CUSTOMER_WID, 'Customer1', 'User1', 'GROUP_YELLOW', :old.GROUP_YELLOW , :new.GROUP_YELLOW);
END IF;
-------

In the above code if the hardcoded value 'Customer1' need to be picked from another table,
i.e .

SELECT NAME FROM W_CUSTOMER_DIM WHERE CUSTOMER_WID = THRESHOLD.CUSTOMER_WID

how can I rewrite my query to the above value from the select into my insert statement..?

View 24 Replies View Related

SQL & PL/SQL :: Creating Trigger - Check If Data Being Inserted Already In Table And If Not Insert It

Aug 24, 2011

I know this is an old thread and I just started working with triggers. I want to check if the data being inserted is already in the table and if not insert it:

create or replace trigger t_triggername
before insert on tbl_tablename
for each row
begin
if(:new.user_id <> :old.user_id) then
insert into tbl_tablename(user_id, location)
values (:new.user_id, :new.location);
end if;
end;

what if I wanted to keep the user but only update location if the user is already in the table. I've tried doing it this way:

create or replace trigger t_triggername
before insert on tbl_tablename
for each row
begin
if(:new.user_id <> :old.user_id) then
insert into tbl_tablename(user_id, location)

[code]...

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

Forms :: Post-INSERT TRIGGER Raised Unhandled Error

Feb 9, 2011

I am facing a post trigger problem.No error while compiling.

DECLARE
mYPACCT VARCHAR2(10);
mTRNNO NUMBER; mVNO NUMBER; mSR NUMBER; mNARRATION VARCHAR2(200); mCHCODE VARCHAR2(10); mPARTY VARCHAR2(200);
mBRAND VARCHAR2(200); mYARN VARCHAR2(200);
BEGIN
BEGIN
SELECT YPACCT INTO mYPACCT FROM CONFIG;
EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
END;

[code].....

View 7 Replies View Related

Forms :: Write Trigger To Insert System Clock Time In Oracle?

Jan 4, 2013

how can i write a trigger to insert the system clock time in the oracle form?

View 15 Replies View Related

SQL & PL/SQL :: Data Manipulation Insert - Procedure / Trigger Should Delete Initial Record Saved

Aug 30, 2012

i have one table ot_ins_item where user will enter the details of item, grade,item qty , later on user will go and update the same table the details of different grades received for the same item in different columns with qty breakup in 3 different fields it_qty_01 , it_qty_02,it_qty_03 respectively with different grades , what i need is i want is whenever he updates this table with different grades based on data entered in 3 different fields , a procedure or trigger should delete the initial record saved and insert three different rows based on newly updated values , it may be 3 or it may be 2 sometime depending upon input values that many records should be inserted same time controlling the qty's entered in breakup not exceeding the main qty.

CREATE TABLE ot_ins_item (it_ins_no NUMBER,it_no NUMBER,it_grade VARCHAR2(12),
it_code VARCHAR2(12),it_qty NUMBER,it_flex_01 VARCHAR2(12),
it_01_qty NUMBER,it_flex_02 VARCHAR2(12),it_02_qty NUMBER,it_flex_03 VARCHAR2(12),
it_03_qty NUMBER);

create sequence s_it_no start with 1 ;

INSERT INTO OT_INS_ITEM VALUES (1,s_it_no.NEXTVAL,'A','ITEM1',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO OT_INS_ITEM VALUES (1,s_it_no.NEXTVAL,'B','1TEM2',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO OT_INS_ITEM VALUES (1,s_it_no.nextval,'C','ITEM3',NULL,NULL,NULL,NULL,NULL,NULL);
SELECT * FROM OT_INS_ITEM;
[code]....

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







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