Modify One Table To Trigger Other Tables
Aug 20, 2012
We are running an Oracle 10g server.
We are adding support for barcode scanner in one part of our information system. So that the mechanics can add parts to an work orders bill of material themselves using barcode scanner to scan the part and enter the qty.
I can via SQL add a part to the bill of materials and reserve the part. But then the part needs to be issued to make it disappear from the stock.
I have found a table that contains information about the part and the bill of material, it has a column named "QTY ISSUED" I have tried via an update command to set the qty issued = 1 for the reserved part. The table is updated, and via SQL everything looks fine, but the part doesn't disappear from the stock. So my guess is that there is a trigger or function somewhere that I need for this.
View 13 Replies
ADVERTISEMENT
Sep 12, 2011
Moidfy trigger into procedure
I am quite a beginner One of trigerred jobs is freezing and I have to reproduce the issue. Thing is about extracion and export data from one table to another (from Rating to Xtexport_table_1).
I want to build query which will fill xtexport_table_1 with values, partialy declared and partialy fetched from Rating tabble.
I figured out that that I have to get rid of all condition statements(IF, WHEN etc ), and just declare variables for some columns and then use a SELECT statement to fetch data.
create or replace
TRIGGER TRG_RATING_EXP_DLS
BEFORE DELETE OR UPDATE OR INSERT
OF RATING_ID,EXTERNAL_ID_1,DN_RATING_ATTRIBUTE_ID,ALI GNMENT_ID,TEAM_ID,CUSTOMER_ID,AFFILIATION_ID,PRODU CT_ID,PERIOD_ID,VALUE_MIN,VALUE_MAX,TENANT_ID,USER _ACCOUNT_ID
ON RATING
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
[Code]...
-------------------
Don't play what's there, play what's not there
View 2 Replies
View Related
Nov 7, 2010
I would like to create a trigger on a table which populates a log table. In addition to using the table where the trigger will exist, I would like to populate a couple more fields in the log table with with data from 2 other tables.
e.g.
NAME_TABLE
-reg_id
-name
ADDRESS_TABLE *trigger to be fired when a new record is created here.
-reg_id
-srv_id
PROCESS_TABLE
-srv_id
-start_time
-end_time
This is what I would like the logging table to look like:
LOGGING_TABLE
-address_table_reg_id
-address_table.srv_id
-name_table.name
-process_table.start_time
-process_table.end_time
How can I go about creating this type of trigger?
View 2 Replies
View Related
Apr 12, 2013
is there any way we can find out which row is updated in oracle table if we don't have any date/time column in table.
let say.
Create table T1 (ename varchar2(200), age number);
insert into T1 values ('A',20);
insert into T1 values ('B',30);
insert into T1 values ('C',40);
insert into T1 values ('D',50);
commit;
update t1 set age=50 where ename='A'
commit;
looking some oracle query which identify which record is update in table in given time period.
View 5 Replies
View Related
Mar 8, 2011
i have a problem in my view. when i try to update a column in my view it prompts me with an error. i consulted the database error messages, and the description is as below:
ORA-01779: cannot modify a column which maps to a non key-preserved tableORA-01500 to ORA-02098 4-25
Cause: An attempt was made to insert or update columns of a join view which
map to a non-key-preserved table.
Action: Modify the underlying base tables directly
i dont understand this "non-key-preserved table". is there any way out from this????. why cant i perform update opearation???. parent tables and the view are as follows:
(
create view donations_n_tests as
select dn_regno, dn_blood_gp, bagno, sc_test_date, hbs, hcv, hiv, vdrl, mp, dn_base, camp_name,
camp.branch_id camp_branch, screening_tests.branch_id as testing_branch
from screening_tests left outer join camp using (campno) order by bagno
)
[code]...
View 2 Replies
View Related
Mar 11, 2011
We have some tables in our database in which for loading data we have the setup in place to do Exchange partition after data load into staging. Today we did changes to column length to one pair of main and staging table. Post that Exchange partition stop working.
View 1 Replies
View Related
May 1, 2013
I need to set up a trigger to check and enforce that the age of an employee at hire date is older than 18 when a new record is inserted. The age is on the PERSON table, and the hire date is on the EMPLOYEE table.
My attempt:
CREATE TRIGGER AGE_HIRE
BEFORE INSERT OR UPDATE ON EMPLOYEE
FOR EACH ROW
BEGIN
IF FLOOR EXTRACT (YEAR FROM (PERSON.BIRTHDATE - EMPLOYEE.HIREDATE)) > 18
THEN DROP NEW
ELSE INSERT NEW
FROM PERSON, EMPLOYEE
END;
View 8 Replies
View Related
Jan 14, 2013
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
i'm trying since some hours to get information from inside a trigger from another table of the same schema.My trigger table is PSE_BKB.NUM_PHANTOM_BP
First i tried simple solution ...
CREATE OR REPLACE TRIGGER PSE_BKB.NUM_TR_PHANTOM_BP
BEFORE DELETE OR INSERT OR UPDATE
ON PSE_BKB.NUM_PHANTOM_BP
REFERENCING NEW AS NEW OLD AS OLD
[code]...
The last version i tried was with a cursor definition inside the trigger as in the code-block below.For debugging purposes i've inserted a RAISE_APPLICATION_ERROR in the inner loop - see below. The variable v_obj_key is never set, like in all other variations i tried - i alway see the predefined 'gugus' from the declare section.
It seems oracle cannot read from other tables at this point. The :old.phantom_key is set (in this simple example there should come one obj_key back).
trigger:
CREATE OR REPLACE TRIGGER PSE_BKB.NUM_TR_PHANTOM_BP
BEFORE DELETE OR INSERT OR UPDATE
ON PSE_BKB.NUM_PHANTOM_BP
REFERENCING NEW AS NEW OLD AS OLD
[code]...
View 11 Replies
View Related
Nov 28, 2011
We have an application is must be connected to our database for specific requirements in our company but this application has a very bug thing as we must write the super DB password "Like HR password as example" clear in some files and these files must be shared so developers can use the HR password to do any action !!! I know that this application is a problem but we have to install
I can do this by creating trigger on each table will restrict DML. As example: if the operating system user is XXX, the trigger restrict the action. But not logic at all to create more than 1000 triggers on schema (This will impact badly on DB performance).
So, i need to create one trigger to fire before doing any DML on all schema tables. As example: If "MMM" the administrator operating system user trying to do insert action, he can do the action. BUT If "DEV" the developer operating system user trying to do insert action, the trigger must fire here to restrict this action.
Be noted also, i need this trigger not depend on any specific tool like Toad as any user can simply rename the exe file for toad then he can pass the trigger. At least, trigger must depend on (Operating system user & Action_type)
View 11 Replies
View Related
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
Nov 16, 2011
There is a customer_account table. It is partitioned by Range. In one of the partition, the range value is given wrongly, and irrelevant data sits in that partition.
How can i modify the range partition value and re-arrange the data ?
View 2 Replies
View Related
Jun 8, 2012
i have one table emp in this table already data exist now i have to modity this existing column data so how can i do this
example:existing emp table
emp_no ename
1 dk patel
2 sk patel
3 jk patel
now i want to change this ename column data i want output like this below table
emp_no ename
1 dk
2 sk
3 jk
how can i do this.
View 4 Replies
View Related
Mar 16, 2011
We have table with number as datatype which starts with number 10 like 1056723. Here we need to update the first 2 digits which is 10 to 04.Digit 10 will be always constant across all the data.
select * from temp;
id
----------
1056723
After update, it should be
id
----------
0456723
What function in pl/sql should be used for achieve the desired results.
View 6 Replies
View Related
Dec 24, 2010
I have a table containing BLOB column which stores scanned images. Due to an application error, few extra data was padded with BLOB data and now we want to remove it.
The table count will be near to 10 million rows.
We need to remove data from 161byte to 167byte of the blob data. I tried to do with DBMS_LOB.ERASE.But it will create blank spaces for the removed data. Here we need to reduce the size of BLOB data by 6 bytes by removing data from 161bytes to 167 bytes.
View 26 Replies
View Related
Jul 15, 2013
I need to know how to modify the spfile - I know how to create it from scratch (from pfile to spfile), but need to modify an existing spfile.
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
Feb 6, 2011
can i modify oracle data dictionary
View 6 Replies
View Related
Apr 18, 2011
I have created the following partition. but i want to alter in future . how to alter the reference partition. is it avilable in reference partition.
CREATE TABLE EMD_FILE_LOAD_DETAILS_PR
(
FILE_REFERENCE_ID ,
FILE_NAME ,
FILE_TYPE ,
FILE_GROUP_NAME ,
[Code]...
View 1 Replies
View Related
Aug 23, 2010
I have a database in which a user xxxx is assigned a password 'bbbbb'.I want to change the password to the one which was used before which was 'aaaaa'.But when I change the password it was saying "Password cannot be Reused".So I checked in user profile and found out that password_reuse_time=unlimited and password_reuse_max=5.
So what I did was change the password 6 times to something else(Since it is 5) and then tried changing it to 'aaaaa' but still it is saying "Old password cannot be reused".
View 3 Replies
View Related
Oct 19, 2010
I am working on Oracle reports 6i. Just for R&D purpose I have opened REP file in notepad. In the notepad file I could find the queries which are there in the data model.
I did a small addition to the query and saved it in the same location. I tried to open the modified REP file from RWRUN60, but it is throwing me error saying "REP-0110- Unable to open file".
Original Query in data model:
SELECT empno, sal, job, hiredate
FROM emp;
Modified Query:
SELECT empno, sal, job, hiredate
FROM emp
WHERE deptno in (10,30);
View 1 Replies
View Related
Oct 2, 2013
is there a way to modify supertypes without dropping / recreating its subtypes?
View 6 Replies
View Related
Oct 18, 2011
I am using 'Novell Sentinel Log Manager' to collect/fetch logs from my Oracle 11g R2.To enable auditing, first I did following:
login as sys, then
SQL> create user testuser identified by "testuser";
SQL> grant connect to testuser
SQL> grant dba to sharf
SQL> grant CREATE SESSION to testuser;
SQL> grant select on v_$session to testuser;
SQL> grant select on v_$version to testuser;
SQL> grant select on SYS.DBA_AUDIT_TRAIL to testuser;
SQL> grant select_catalog_role to testuser;
SQL> grant select any dictionary to testuser;
Now logon/logof of user 'testuser' are logged , as well as if testuser drops a table or creates a table, its also logged . but when 'testuser' insert a new record, this information does not logged ;( while I need to know exactly what was added SQL> insert into emp (empid, name, salary) values (10002, 'Ron', 6000)
likewise if 'testuser' modify/update an existing record it also does not logged.
SQL> update emp set salary=700 where empid=10001;
which sql statements I have to execute to start auditing 'insert' and 'update', so that I know what was added/inserted and exactly what was updated/ changed/modify by user 'testuser'.
View 12 Replies
View Related
Mar 1, 2010
I have created a job using DBMS_SCHEDULER with named schedule.
After i have modified the schedule using the below queries,
exec dbms_scheduler.disable( 'JOB1' );
exec dbms_scheduler.set_attribute_null('JOB1','schedule_name');
exec DBMS_SCHEDULER.SET_ATTRIBUTE( name => 'JOB1', attribute => 'repeat_interval', value => 'freq=WEEKLY;BYDAY=SUN,WED,FRI;BYHOUR=05');
exec dbms_scheduler.enable( 'JOB1' );
I am using the client system to change the setting and the time zone differs from that of production. Even though the job is scheduled to run at 5 AM it show the start date as 6:30 PM which is the client system time.
View 1 Replies
View Related
Jan 27, 2012
We have a large customer table so first thought was to partition.Also we see two union alls in the plan - can we introduce parallelism? Below is the plan - have attached a text file if difficult to read
SELECT V_IDENTIFIER_LOOKUP.UID_V_IDENTIFIER_LOOKUP AS "UID",
V_IDENTIFIER_LOOKUP.ABA, V_IDENTIFIER_LOOKUP.ADDRESS1,
V_IDENTIFIER_LOOKUP.ADDRESS2, V_IDENTIFIER_LOOKUP.ADDRESS3,
V_IDENTIFIER_LOOKUP.ADDRESS4, V_IDENTIFIER_LOOKUP.ALIAS,
V_IDENTIFIER_LOOKUP.CITY, V_IDENTIFIER_LOOKUP.COUNTRYCODE,
V_IDENTIFIER_LOOKUP.CUST_CODE, V_IDENTIFIER_LOOKUP.CUST_NAME,
V_IDENTIFIER_LOOKUP.HEAD_OFFICE_IN,
V_IDENTIFIER_LOOKUP.IDENTIFIER,
V_IDENTIFIER_LOOKUP.IDENTIFIER_TYPE,
[code]...
View 1 Replies
View Related
May 8, 2012
I have made an application in forms 6i. Now i want if user can update, or modify the data it will required automatic authentication. Administrator will gives his password then the updation will continue otherwise rollback the transaction.
View 1 Replies
View Related
Aug 25, 2010
I am trying to create a custom package that creates users as well as modify user information in OID using DBMS_LDAP package. I was able to create the package but I am stuck on specifying the orcladmin password for the procedure DBMS_LDAP.simple_bind_s(my_session,'orcladmin','orcladminpwd');
I need to get the orcladmin password dynamically. Is there a way to achieve this?
We are planning to provide our application users the ability to add new users / modify their own OID information.
View 2 Replies
View Related
Nov 14, 2012
I'm trying to modify my dbca template to include two control files. One on ASM +RECO and we we're thinking of adding the second one on the database filesystem $ORACLE_BASE/something/something Within the template there are references to controlfiles two places:
Initparameter:
<initParam name="control_files" value="("{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/control01.ctl", "{ORACLE_BASE}/fast_recovery_area/{DB_UNIQUE_NAME}/control02.ctl")"/>
and
<StorageAttributes>
<ControlfileAttributes id="Controlfile">
<maxDatafiles>32767</maxDatafiles>
<maxLogfiles>1024</maxLogfiles>
<maxLogMembers>3</maxLogMembers>
<maxLogHistory>32767</maxLogHistory>
<maxInstances>8</maxInstances>
<image name="control01.ctl" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>
<image name="control02.ctl" filepath="+RECO"/>
</ControlfileAttributes>
With this configuration there is only one controlfile created and that is on +RECO. What am I doing wrong and also should I add the second controlfile to +DATA instead of fielsystem $ORACLE_BASE?
View 6 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 30, 2010
I need to modify an existing form, thus I have to resize a canvas and a window for it. In windows, I run it and I got the right display. When I transfer it to dev server running in linux, then I compile and run it, it display the previous/unmodified size. I'm sure my form is the one that running, I placed some text there and it was displayed.
View 4 Replies
View Related
Apr 16, 2013
I have my database structured to where i have my tables, constraints, and Rows populated in each table which is 4 tables in particular. for one I want to be able to List all four tables with its content and constraints all at once. What i want to with the PL/SQL is to be able to program a prompt to where the user can just enter the individual order information and the data goes to what ever appropriate column or Table it needs to go to. Now should I be mostly be using procedures, functions or triggers to make this possible.
View 9 Replies
View Related
