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
ADVERTISEMENT
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
View Related
Mar 14, 2012
I am trying to write a trigger to reduce 1 day from 4 date fields of a table (order)
table name = order
fields = date_1, date_2, date_11, date_12 and dest_id
for example: the trigger will verify if orders that are dropping is for a specific customer (dest_id). if yes, the trigger will subtract one day from these date fields (by updating them); if no, (the dest_id is not for this customer) the trigger will not update anything in the DB.
View 1 Replies
View Related
Apr 18, 2012
Oracle version:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
how to subract the timestamp values.
SELECT TO_TIMESTAMP ('10-Sep-02 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF') FROM DUAL;
This is my output.
TO_TIMESTAMP('10-SEP-0214:10:10.123000','DD-MON-RRHH24:MI:SS.FF')
---------------------------------------------------------------------------
10-SEP-02 02.10.10.123000000 PM
Now how to subtract the FF value in sysdate.(ex:10000 this is the input of FF value )
example output like: "18-APR-12 09.46.44.005196 AM"
View 10 Replies
View Related
Feb 14, 2013
I have two tables one is
1)create table abc(c_name varchar2(10),c_number number(6),c_loc varchar2(8)); --having values
2)create table temp(sname varchar2(10),sid number(4),address varchar2(10));---no having
and my question is how to insert a values into temp using when-button-pressed trigger based on abc table in oracle form.
View 16 Replies
View Related
Jan 16, 2012
I have a trigger on insert which sets a :new field if it is net set.Basically:
CREATE OR REPLACE TRIGGER my_schema.T_OBS_BEF_IU
BEFORE INSERT OR UPDATE ON my_schema.OBS FOR EACH ROW
BEGIN
If :new.work_db is null Then
:new.work_db := my_package$PARAMETER.Get_label ('DEFAULT_WORK_DB'); -- function returning varchar2(200)
End if;
END;
/
It usually works fine. With jdbc, when the application insert a null value, the trigger replaces it with the good value.When the application can determine the non-null value, it is written in the table.
But when the trigger is restarted to a savepoint, due to a table lock, the second execution of this trigger goes wrong. The input :new.work_db value is not null anymore, nor has the correct value. Most of the time, it has the ascii 00 value (NUL), sometimes another weird character.
Do you know if my trigger code is correct, or if there would be some limitations ?
View 1 Replies
View Related
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
Aug 10, 2010
I am working on convertion of triggers from SQL SERVER to Oracle. I got them converted using a tool. But not sure how to correct the below trigger which uses '*' in sql server.
Code in SQL SERVER :-
CREATE TRIGGER INSERT_AUDIT ON T_AUDIT
FOR INSERT AS
INSERT INTO T_AUDIT_LOG
SELECT 'INSERT','AFTER', CURRENT_TIMESTAMP, NULL,*
FROM INSERTED
Converted into Oracle:-
CREATE OR REPLACE TRIGGER INSERT_AUDIT
BEFORE INSERT
ON T_AUDIT
FOR EACH ROW
INSERT INTO T_AUDIT_LOG
VALUES ( 'INSERT', 'AFTER', SYSTIMESTAMP, NULL, * );
END;
how can I remove the *. I tried replacing the * with the rest column names prefixing with :NEW.
the last 3 columns when are c_data1,c_data2,c_data3 as :-
INSERT INTO T_AUDIT_LOG
VALUES ( 'INSERT', 'AFTER',SYSTIMESTAMP NULL, :NEW.c_data1,:NEW.c_data2,:NEW.c_data3);
But this gives error:-
PLS-00049: bad bind variable 'NEW.C_DATA1'
PLS-00049: bad bind variable 'NEW.C_DATA2'
PLS-00049: bad bind variable 'NEW.C_DATA3'
how can I convert this code.
View 7 Replies
View Related
Sep 30, 2010
I am facing a problem while inserting primary keys using a sequence. Following is my case
I have a Master Table, say M_A, and 2 detail tables D_1 and D_2. I am trying to generate primary keys for the master and detail table as well as the reference keys for the detail tables using sequence.
I created a pre-insert trigger, say preInsertTRIG
proc_ABC(pri_key_master OUT VARCHAR2,
pri_key_detail1 OUT VARCHAR2,
fk_detail1 OUT VARCHAR2,
pri_key_detail2 OUT VARCHAR2,
[Code] ........
I am able to insert the P.K of the master table as well as P.K of one of the detail table. However, it fails to insert the P.K of 2nd detail table and reference keys for both the detail tables. I know there are other simple methods available in Forms, but I have to do it by this procedure only
View 5 Replies
View Related
Jan 10, 2012
i have a table students create table students (name varchar2(10),rolno number(10),sub1mark number(10),sub2mark number(10),total number(10),percentage number(10),status varchar2(10))
i am go to create trigger on students after entering the values of name,rolno,sub1mark,sub2mark the values of total & percentage come automatically .trigger is
CREATE OR REPLACE TRIGGER UCSETH.students_comm_trig
BEFORE INSERT ON UCSETH.STUDENTs FOR EACH ROW
BEGIN
set NEW.total = new.sub1mark+new.sub2mark;
set new.percentage=new.total/2;
[code]....
View 17 Replies
View Related
Mar 31, 2010
I need below proc like...
procedure p1
(
i_time_min number -- minutes to be substracted from timestamp
)
is
v_end_timeinstamp timestamp(6);
begin
[code]....
The problem with above procedure is passing parameter is in minutes and i need to substract the same from sys_extract_utc(current_timestamp) and store result in v_end_timeinstamp in timestamp format only... substracting directly will reduce the days and not the minutes.
View 6 Replies
View Related
Jul 6, 2010
I want to substract two dates to get the age of a person. One of the date is sysdate and the other is date of birth. Assuming we remove the time part of the date.
View 16 Replies
View Related
Sep 17, 2012
How to subtract Time_Out - Time_IN
SELECT EMP_CODE, TO_DATE(INTIME,'DD/MM/RR')IN_DATE,
MIN(TO_CHAR(INTIME,'HH24:MI')) TIME_IN,
MAX(TO_CHAR(OUTTIME,'HH24:MI')) TIME_OUT
--MAX(TO_CHAR(OUTTIME,'HH24:MI')) - MIN(TO_CHAR(INTIME,'HH24:MI'))
FROM HCM.einout
WHERE EMP_CODE ='470' AND INTIME >= '01-APR-12' AND INTIME <= '15-APR-12'
GROUP BY EMP_CODE, TO_DATE(INTIME,'DD/MM/RR')
ORDER BY 2 ASC
[code]....
View 5 Replies
View Related
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
Nov 13, 2012
New to using Oracle and SQL Developer. I am trying to subtract a maximum date from today and adding back 1 to get a field named daysSinceLastActivity....
Syntax I am trying is (date()-max(activity_date))+1 as daysSinceLastActivity
I keep getting a error message of missing expression. After googling, I am not finding anything. This should be a simple calculation.
View 3 Replies
View Related
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
Dec 23, 2010
My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE
i_e NUMBER(10);
BEGIN
FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL)
LOOP
FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
[Code]...
View 9 Replies
View Related
Mar 28, 2011
I am developing form, but there is issue when I Press F11 to query data.I make trigger form when-new-record-instance to assign values for item.But when i Press F11 then Block no clear.
View 1 Replies
View Related
Oct 28, 2013
I am searching the simplest way for ad hoc MINUS.I do:
SELECT *
FROM uam_rss_user_XXXXXXX
WHERE host_name IN
('XXX0349',
'XXX0362',
'XXX0363',
'XXX0343',
'XXX0342',
'XXX0499',
[code]....
and look in the table which values are missing (values that are in host_name IN but not in actual table).is there a simpler way for doing an ad hoc MINUS? I know to insert values in temp. Table. How are experienced Oracle pros doing this task?
View 6 Replies
View Related
Oct 6, 2010
I want to convert numbers into hours and minutes.I have two numbers say like first number is 1234 and second number is 1235.4 now i want to find the different between these two numbers. so am subtracting number 2 - number 1
1235.4 - 1234 i will get 1.4 so the result of this 1.4 to be converted as hours and minutes like 1 hr and 40 minutes. How can i convert numbers in hrs and minutes.
View 7 Replies
View Related
Oct 18, 2012
I have two varchar variable which has value like this
v_outpunch1:='17:50:00'
and v_Shifttime:='18:00:00'
this both time i am subtracting here and storing in another varchar variable which is like this.
v_EarlyLeaverstimeformat := LPAD((extract(hour from TO_TIMESTAMP (v_ShiftTime,'HH24:mi:ss')) - extract(hour from TO_TIMESTAMP (v_OutPunch1,'HH24:mi:ss'))), 2, '0')||':'|| LPAD((extract(minute from TO_TIMESTAMP (v_ShiftTime,'HH24:mi:ss')) - extract(minute from TO_TIMESTAMP (v_OutPunch1,'HH24:mi:ss'))), 2, '0')||':'|| LPAD((extract(second from TO_TIMESTAMP (v_ShiftTime,'HH24:mi:ss')) - extract(second from TO_TIMESTAMP (v_OutPunch1,'HH24:mi:ss'))), 2, '0');
it's not subtracting value correctly.
View 3 Replies
View Related
Jul 24, 2010
I have a multi record control block (basically a text item displaying 6 records) where user enters values and I want to process the values using pre-insert trigger.
I want to read value in each record and then do some tasks using a pre-insert trigger before I commit the values. To navigate between the records I was using first_record, next_record, clear_record built-ins but it gives errors like "40737-illegalrestricted procedure next_record in pre-insert trigger".
View 3 Replies
View Related
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
Sep 27, 2007
I am a novice in oracle
I have 2 columns in my table
->Col1 with experience in years entered as an integer
->Col2 with current date
I need to add another column as a date value adn for that i need to subtract Currentdate-Col1 when i tried currentdate-Col1 it just subtracted the days i need the formula to subtract years and give a date
I have worked in DB2 and all u need to do there was add the keyword years at the end but in oracle the same does not work
View 8 Replies
View Related
May 21, 2011
I have got two tables emp_dtl and iou_tab. i have already made entries i.e booking no, emp_cd, emp_name etc in emp_dtl snc its my master table. I want to retrieve the booking nos through lov in iou_tab which are generated in emp_dtl and corresponding info of emp_cd and emp_name should come in the respected fields in iou_tab.
View 1 Replies
View Related
Aug 31, 2010
I have the below data in table test_1.
select * from test_1
IDNameTotal
-----------
1A100
2B100
3C100
4D100
test_2 table contains the concatination of ID's with comma seperated. Actually in this table ID column is of datatype varchar2.
select * from test_2
ID
----
1,2,3
My requirement is to select the data from test_1 table where the id values in this table exists in test_2 table. I tried with the belowselect statement, but could not get any data.
SELECT * FROM test_1 WHERE to_char(id) IN (SELECT id FROM test_2)
create table test_1 (id number, name varchar2(100), total number)
create table test_2(id varchar2(100))
insert into test_1 values (1,'A',100)
insert into test_1 values (2,'B',100)
insert into test_1 values (3,'C',100)
insert into test_1 values (4,'D',100)
View 4 Replies
View Related
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
View Related
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
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
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
