SQL & PL/SQL :: Trigger For Updating All Columns Of A Table
Sep 12, 2013
I have created the following trigger whcich will track all the column changes and insert the row in log table. here i have some doubt while substituting the cursor value.
create or replace trigger historylog_trigger
before update on log_dev_test
for each row
declare
PRAGMA AUTONOMOUS_TRANSACTION;
in_loamid number(10);
in_col_name varchar2(10);
in_old_val varchar2(100);
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]...
I have a table A, whose table structure is in the below format.
Table A
ID DESC VALUE 123 A 454 123 B 1111 123 C 111 123 D 222 124 A 123 124 B 1 124 C 111 124 D 44
Now i need to insert the data from this table to another table B, the sturcture of which is as below
Table B
ID A B C D 1234541111111222 124123111144
How do i frame a query to fetch data from table A and insert that into table B? I don't want to use max and decode combination. as it would return only single row for an ID. I need all the id's to be displayed.
I have a table EMPLOYEE with columns employee_id and status. I have a requirement that when an employee status is getting changed, then even its linked employee's status also should be changed to the same status value. For this, I need to handle the updating of linked employee's status using a trigger.
Since we get mutating trigger issue when try to update the originating table, I am trying to go with the approach of "in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you may be able to use two triggers. The first is an AFTER row trigger that updates a temporary table, and the second an AFTER statement trigger that updates the original table with the values from the temporary table".
But however I am still facing the same issue.
Test case:
CREATE TABLE EMPLOYEE ( EMPLOYEE_ID VARCHAR2(1), STATUS NUMBER(9) );
INSERT INTO EMPLOYEE VALUES ('A',1); INSERT INTO EMPLOYEE VALUES ('B',1); commit; [code]....
Also, any alternate options (rather than using 2 triggers with temp table).
EBS version is 12.1.3. we need to update the e-mail address in per_all_people_f in Oracle EBS based on a trigger that will fire against a column in Oracle Internet Directory (OID). A trigger will be built against table ct_mail, column attrvalue. When a value is inserted or updated this value will use an api on the EBS database to update per_all_people_f.
Q => Is ct_mail the correct table to use in OID for email addresses? or can you confirm a different table?
Q => If Ok to use ct_mail as the table can you see any problems with using a trigger in OID?
Q => Finally is a database link between OID and EBS database the best way for the trigger to work?
Order OrderID Status OrderItems OrderID EAN Amount Store EAN Amount
now,I need trigger that will on updating table Order and changing status to "GoodsReceived" increase amounts in Store according to values in OrderItems.
create or replace trigger order_received_trigger before update on Order for each row begin if (:old.status=4 and :new.status=1) then /* select ean, sum(amount) from OrderItems where OrderID=:old.OrderID group by ean; */ end if; end;
but now i dont know how to apply that select on table Store.
I have two different tables having similar structure but data is coming from different source.finally i want to update the view so that the it should affect the base table.
create table emp1 as select empno,ename,job,deptno,sal from emp where deptno=10; create table emp2 as select empno,ename,job,deptno,sal from emp where deptno=20; create view emp_view as select * from emp1 union all select * from emp2; [code].......
I need to update the current column with sum of the previous column values. Following are the creation scripts
DROP TABLE TEST_LOG; CREATE TABLE TEST_LOG (
[Code]....
Above query is working fine to retrieve the previous column values.But when we are updating the SUM_PRE_COLS column with those values it's not working fine.
I tried by using the following query
UPDATE TEST_LOG T SET SUM_PRE_COLS = ( SELECT LAG(T2.KEY0, 1, 0) OVER(ORDER BY T2.KEY0) + LAG(T2.KEY1, 1, 0) OVER(ORDER BY T2.KEY0) FROM TEST_LOG T2 WHERE T2.ROWID= T.ROWID);
is there any possibility or hint to avoid updating record if columns do not change? For example, I have UPDATE:
UPDATE MY_TABLE SET COLUMN_A = 'ABC' WHERE COLUMN_B = 12
if the value in COLUMN_A is 'ABC' I do not want to do this update. Of course I can add in WHERE 'AND COLUMN_A<>'ABC' but when I am updating a lot of columns it will be annoying
I get a "ORA-00922 missing or invalid option" after compiling the following trigger:
create or replace trigger aifer_transfer after insert on transfer for each row [code]......
I want the trigger to affect the transferring account (from_account) with minus the "amount" being transferred from it, and the account transferred to (to_account) with plus the "amount" being transferred to it. My tables looks as following:
TRANSFER ----------------------------------------- -------- ------------ ROWNR NOT NULL NUMBER(9) PNO NOT NULL VARCHAR2(11) FROM_ACCOUNTNO NOT NULL NUMBER(8) TO_ACCOUNTNO NOT NULL NUMBER(8) AMOUNT NUMBER(10,2) DATE NOT NULL DATE
ACCOUNT Null? Type ----------------------------------------- -------- ------------ ACCOUNTNO NOT NULL NUMBER(8) KTNR NOT NULL NUMBER(6) REGDATE NOT NULL DATE BALANCE NUMBER(10,2)
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.)
I have a non-base-table item which I want to update in the pre-update trigger of the current block.
If the content of the field exceeds 4000 characters, i get the error message
ORA-01461: can bind a LONG value only for insert into a LONG column.
The code is
update tab set long_col = :formsblock.long_col where tab.tabpk = :formsblock.foreign_tabpk;
Workarounds would be, 1.) to delete the old dataset and insert the new one:
delete from tab where tab.tabpk = :formsblock.tabpk;
insert into tab (tabpk, long_col) values (:formsblock.foreign_tabpk, :formsblock.long_col);
or 2.) to change the Item from a non-database item to a database item and use the internal update of the forms-module, but both workarounds are not very satisfying.
Do you know another way to update the LONG-column within the pre-update trigger (or any other PL/SQL part of forms)?
I am trying to create and update query to use as a trigger for updating information in a database which is dependent upon time.
I have a severity level which needs to be decreased in value after a set amount of time. the time of the action is recorded automatically on the creation of the the record. after an hour anything at severity level 2 needs to go up to 1, after 2 hours level 3 goes up to 2 and 3 hours level 4 goes up to 3
so far this is what I have
UPDATE calllog SET timedate = current_timestamp, severityid = severityid - 1 WHERE ((severityid = 2 and timedate >= current_timestamp + (1/24)) or (severityid = 3 and timedate >= current_timestamp + (2/24)) or (severityid = 4 and timedate >= current_timestamp + (3/24)))
I am not getting any errors back from this sql but it is not updating the relevent information. I am using one cell for time and date in format = DD-MON-YYYY HH24:MM:SS.
I have to audit some table to log all modifications (inserts, updates, deletes). I wrote the following trigger (works as expected) :
-- Trigger for DOCUMENT table audit CREATE OR REPLACE TRIGGER TAUDIT_IUD_DOCUMENT AFTER INSERT OR UPDATE OR DELETE ON DOCUMENT FOR EACH ROW DECLARE l_user VARCHAR2(64); [code].........
Is there any way I can improve this ?I mean getting columns name and value (:NEW and :OLD) dynamically, instead of specifying them one by one (the trigger will have to be updated in case a new column is added). Something like this :
FOR i in 1..DOCUMENT.COLUMN_COUNT LOOP l_row := l_row || DOCUMENT.COLUMN_NAME(i) || :NEW.COLUMN_VALUE(i) || l_separator; END LOOP;
I am using oracle 9i, and having trouble with updating a table.
I get ORA-00001(unique constraint) Error on executing the sql below; I know sql below is little strange( which use unique key in 'SET' statement) . but It worked on My Oracle Server. but it didn't on Client's.
why this error occurs or why this error did not occur on my PC,
[Update sql](key is CD and SDATE) Update TBL1 set CD = 'A',
I am trying to write a control file that will read information from two INFILES and update two tables with the different information via SQL Loader. I am using Oracle 11g on Linux. i am not sure how to take the result from the first insert query and use it as input to the second insert query. Currently I have the following control file:
LOAD DATA INFILE 'table1.dat' INFILE 'table2.dat' APPEND INTO TABLE table1 WHEN tid='1' FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' (pid "pid.nextval", p_fname, p_lname, tid) [code]....
The pid in the second insert query should be taken from the pid generated in the first insert query. However, I do not know how to do this. Does SQL Loader run the first insert query for all entries in the table1.dat file and then the second insert query or will it do one record at a time? Here are my INFILES:
table1.dat ,John,Doe,1 ,Joe,Smith,1
table2.dat 10,,abc,1, 11,,xyz,1,
The second field in table2.dat should be taken from the result of creating a record in the table1.dat. Is this something that can be achieved using SQL Loader? The first part of the control file is successful, I can see the table being updated with the contents on table1.dat, but the second query fails.
I am updating a table column which is xml datatype and am getting above error.Below is the process what i did. since the xml is too large i split them into small chunks.
pgit_policy is transaction table having producer code field.
pgith_policy is history table, on that table if any endorsement passed new records created with same polh_sys_id and increment on POLH_END_NO_IDX.
I am trying to update all records of the history table but its updating only higest POLH_END_NO_IDX only. i need to update all producer code.
update pgith_policy a set a.polh_producer_code= (select b.pol_producer_code from pgit_policy b where b.pol_no=a.polh_no --and b.POL_END_NO_IDX= a.POLH_END_NO_IDX and b.POL_END_SR_NO = a.POLH_END_SR_NO and b.pol_producer_code is NOT NULL and b.pol_class_code='10') where a.polh_class_code='10' and a.polh_producer_code is null and a.polh_appr_dt between to_date('01-06-2011', 'dd-mm-yyyy') and to_date('30-06-2011', 'dd-mm-yyyy')
updating the statistics for a table (with GATER_TABLE_STATS) and using NUM_ROWS then. This works fine for me as long as I am the owner of the table, but when someone else is, I always get this error: ORA-20000: Table does not exist or insufficient privileges.what privileges do I need to use GATHER_ TABLE_ STATS on all Tables, which were created by Users?
when I tried to use ANALYZE TABLE TEST_TABLE COMPUTE STATISTICS on a certain table I got the following error: a view is not appropriate here. The strange thing is, TEST_TABLE is not a view (at least it is not listed in ALL_VIEWS and is listed in ALL_TABLES, so it cant be a view right?).
Besides, is there another way to gather Table Statistics (not using Analyze Table or Gather_Table_Stats)?
In the above result, We need to go from bottom up and when we hit some value we need to update with the lastest record as below.("Blank" space are considered as null.)
im trying to create a trigger that, when i insert a new song in the songs table, it will check the category of the song (song_cat) and increase the respective cat_total (from table categories) by 1.
here is what i've done so far:
drop trigger countcat; CREATE TRIGGER countcat AFTER INSERT ON songs FOR EACH ROW update categories SET cat_total= cat_total +1 WHERE cat_name = (select song_cat FROM inserted);
What to write in the cat_name = (select ...). I have tried lots of stuff but still nothing. when i use this, i get the error that mydatabase.inserted doesnt exist
this is the correct syntax for updating a table with a select statement included. Table created easily and the alter table ran fine, but the update is running quite a log time.
My ultimate goal is to populate the "children" field with a count of children for each household id.
create table NON_GBC_Members nologging as select distinct hcp.household_master_ID from mrtcustomer.household_child_profile hcp where hcp.child_birth_dt between '31-OCT-2000' and '30-OCT-2011' group by hcp.household_master_id minus
I am trying to learn Oracle and trying to use Visual Basic 7 to connect to an Oracle database, upload a text file to an empty table and then update a second Oracle table based on the info in the first table. I know you can do this with SQL*Loader, but how about VB? I did some research and found this posting: Re: Using Bulk insert or SQL Loader in VB6 I borrowed the code from there and altered it for my application (see below).
Dim con As New ADODB.ConnectionDim rst As New ADODB.RecordsetDim rst2 As New ADODB.RecordsetDim rst3 As New ADODB.Recordset con.ConnectionString = "Provider=OracleDB;User ID=john;Password=doe;Data Source=TestDB;"con.Open 'open the text filerst.Open "C:OracleTestCombinedCombined.txt" 'rst2 is an empty recordset because TempTable is empty at this time.rst2.Open "select * from TempTable", con, adOpenDynamic, adLockOptimistic 'adding rows into TempTable.Do Until rst.EOFrst2.AddNew Array("PACKET", "DATERET"), Array(rst.Fields("PACKET"), rst.Fields("DATERET"))rst.MoveNextLoop rst.Closerst2.Closecon.Close
My second question is this - say I had a second Oracle table in the same DB called "MasterTable", with the same two fields as "TempTable" (PACKET and DATERET). I want to update the DATERET field in "MasterTable" with the DATERET field in "TempTable" ONLY when the "PACKET" field matches in both tables.
I have a situation where there are multiple records for a join criteria. I am trying to find a way to update a particular column for all the records returned by the join criteria. Example :
I create the sample for master/detail form. In detail for prdcode,rate,qty,amount is there. When select prdcode it fetching prdcode,rate in a record and if you type the qty the amout will come based on formula(property) :qty*:rate.
It is available on screen. But when i store the data, in backend table the amount is be a null.
i m doin simple application, in which all data is retrieving from table in to form(all the
field are textbox and button on form) ename,dept,salary,dept_id etc.
i took one textbox field on form which is non table field
(no db field bt linked to another table..via LOV m retrieving value into this textbox which suppose dept_id ) and now after selecting one record from list box into textbox which is non table field.... after immdiately selecting i want to update one field into db field which all other form field contains...
so how to do this??which trigger i hav to use for this?
if :dept_id is not null then begin update dept set loc =:emp.loc where dept_id = :emp.dept_id; forms_ddl('commit'); exception when others then null; end; end if;
it giving error.......if m using commit in this...i hav used post query trigger ...is dere
