SQL & PL/SQL :: Trigger To Move Records To History?
Mar 23, 2010
I have a table CISCOWORKS that contains dumps from Ciscoworks. Now I want to make a history table CISCOWORKS_HISTORY that contains all information I no longer need actively (entries that have the same CISCOWORKS_MAC as newer entries).
The two tables are like this, exactly the same.
DROP TABLE CISCOWORKS IF EXISTS;
CREATE TABLE CISCOWORKS
(
CISCOWORKS_IDNUMBER(9,0),
CISCOWORKS_MACVARCHAR2(20 BYTE),
CISCOWORKS_SWITCHVARCHAR2(10 BYTE),
CISCOWORKS_PORTVARCHAR2(10 BYTE),
[code].....
I guess I will have to check the INSERTED table and check every entry to see if there is a entry in the CISCOWORKS table with the same CISCOWORKS_MAC. If so, insert an entry in CISCOWORKS_HISTORY with the attributes of the CISCOWORKS entry, and then delete the CISCOWORKS entry. how would I check every entry separately?
View 4 Replies
ADVERTISEMENT
Jun 21, 2012
I have written a trigger which insert and update on same table and the select statement for update and insert is same. My trigger update the record but doesn't insert data and doesn't throw error as well.
if i substitue all the where condidion value of insert statement then it return row and insert data but doesn't insert when trigger fire so there is no issue with the syntex.
create or replace
TRIGGER SRVCCLLS_RVW
AFTER INSERT OR UPDATE OR DELETE ON SD_SERVICECALLS
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
DECLARE
[Code]....
if i substitue all the where condidion value of insert statement then it return row and insert data but doesn't insert when trigger fire so there is no issue with the syntex
View 2 Replies
View Related
Dec 10, 2012
Relevant tables:
create table messages
(
msgID number(10),
fromID number(10),
message varchar2(1024),
ToUserID number(10) default NULL,
ToGroupID number(10) default NULL,
dateSent date,
constraint messages_pk primary key(msgID),
constraint messages_fk1 foreign key(fromID) references profile(userID) on delete cascade,
constraint messages_fk2 foreign key(ToUserID) references profile(userID) on delete cascade
);
[code]....
I need to create a trigger that adds corresponding records into messageRecipient when a message is sent to a group. So, for example, the sql statement
insert into messages values(1, 2, 'message sent to group 3 from user 2', NULL, 3, currentdate);
should add records of the form (1, userID) into messageRecipient for every (3, userID) pair in groupMembership. Here are two triggers I've tried, neither of which works.
create or replace trigger SendGroupMessage
after insert on messages
FOR EACH ROW
when (new.ToUserID is NULL)
BEGIN
insert into messageRecipient(msgID,UserID)
[code]....
View 6 Replies
View Related
May 14, 2013
Using oracle 11.2.0.3 and wish to do the following.
Have trigger on table a on database a.
When a record is inserted into table a a record should be written first to table1 on database b , committed then a record written to table2 on database b as table2 has fk dependency on table 1.
How can we best achieve this.
Getting problems with the commit inside the trigger have tried pragma autonous transaction as well.
Get ora-02064 distributed transaction not supported when use the following code
create or replace trigger por_TRG_POP_IKNOW_MULTIPLE2
after INSERT OR UPDATE or delete on por_MULTIPLE
for each row
-- tO ENSURE I-KNOW KEPT UP-TO-DATE WITH HOMIS MULTIPLE
-- delib not delet form i-know as if has been used by customer cannot delete
-- due to referential inetgrity constraints in i-know
-- Note parent_num updated on IKW via support.maintain_latest_dim_version.update_multiple until
-- When IKW becomes master of multiple info, this trigger can be removed
-- and support.maintain_latest_dim_version.update_multiple removed
declare
PRAGMA AUTONOMOUS_TRANSACTION;
v_dml_type varchar2(1);
begin
IF INSERTING OR UPDATING
[Code]....
--when matched THEN update set
-- a.MGRP_NUM b.mult_link_multiple_num,
-- MGRP_DESCRIPTION = (select mult_name from por_multiple
-- where mult_code = mult_link_multiple_code
when not matched
then insert
(
a.MGRP_NUM,
a.MGRP_DESCRIPTION
)
[Code]...
View 7 Replies
View Related
Aug 5, 2010
why my logon trigger always creates 2 records with different timestamp. Also is this the proper way of excepting records to insert?
Script.
CREATE OR REPLACE TRIGGER TR_LOGON_AUDIT
AFTER LOGON ON DATABASE
BEGIN
If user<> 'DBSNMP' then -- don't want to insert this
if user <> 'SYS' then -- don't want to insert this
[code]....
Result when select.
OS_USERNAME LOGON_TIMESTAMP
----------- ----------------
GAN 2010-08-05 14:27:52
GAN 2010-08-05 14:27:55
View 12 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
Oct 30, 2012
i have requirement like this i don't know abt trigger
create trigger with the below:
Tables: TAB1 TAB2
Create a trigger, if any insertion in TAB1 then records should get inserted into TAB2
create a trigger, if any updation in TAB1 then record should get inserted into TAB2
Create a trigger, if any deletion in TAB1 then record should get inserted into TAB2
TAB1 and TAB2 can be any table.
View 2 Replies
View Related
Mar 8, 2007
I'm using an 'On-Update trigger' to replace the default Form Builder processing for updated records. When I try to change a column in the form I get: ORA- 01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.
The on-update doesn't fire. How do I get around this. The block is based on a view.
View 9 Replies
View Related
May 27, 2011
I have SQL ID , sql with bind variables ("SYS_B_005")from AWR report which is taken last week.I need find the full exact sql text with bind variable value (sql ran 1 week before).
View 4 Replies
View Related
Aug 16, 2010
two history tables with each record having effective date and end date needs to join (date is in dd/mm/yyyy)
table one
effdate enddate ID Name
01/08/2010 04/08/2010 01 devendra
04/08/2010 06/08/2010 01 deven
table two
effdate enddate ID Family
01/08/2010 02/08/2010 01 X
02/08/2010 03/08/2010 01 Y
03/08/2010 05/08/2010 01 Z
05/08/2010 06/08/2010 01 W
Expected output
effdate enddate ID Name Family
01/08/2010 02/08/2010 01 devendra X
02/08/2010 03/08/2010 01 devendra Y
03/08/2010 04/08/2010 01 devendra Z
04/08/2010 05/08/2010 01 deven Z
05/08/2010 06/08/2010 01 deven W
what can be optimum sql for this?
View 3 Replies
View Related
Jun 10, 2010
I can get a list of invalid objects, know how to recompile them but I was wondering if there was a way to query the date/time of when an object went invalid.
I'm working on a 10.2.0.4.0 (Enterprise Edition) database running on SLES10.
View 1 Replies
View Related
Jun 18, 2012
I am having a particular SQL, i want to know the frequency/count of execution of this particular sql in my prod DB. how can i get this.
I get the sql in v$sql but didn't get the same record in dba_hist_sqlstat view.
View 3 Replies
View Related
Mar 8, 2010
I have 2 tables that I am going to use. These are AUDIT_LOG, and AUDIT_PROPERTIES. AUDIT_LOG tells me that who made the change and at what time. AUDIT_PROPERTIES basically tells me that what the old value was and what the new value is.
These tables operate at the field level. For example, I have a module called BUG. Now a BUG (as a module entity or record) has several fields. There are around 25 fields. But I am interested in only 3 of these fields- these are BG_STATUS (Status), Priority (BG_PRIORITY) and Severity (BG_SEVERITY). This is my requirement:
Between a given period of time (let us say the month of January), give me the count of bugs that, 1) had an "Open" Status, 2) Were of "Medium" Severity, 3) And had a "Low" priority....each of these 3 conditions must be true simultaneously, means, that to be a "qualified" bug, the bug must be Open, Medium severity and low priority at the same given time within the month.
Below are the query and an image of the data looks like.
View 11 Replies
View Related
Aug 9, 2010
I have 3 main tables as projects, tasks, clients. Then I have a history table for each. I created a trigger on update/delete that takes the old records and inserts into the history.
Now, I need to retrieve the history but I have no clue how to do so. I need to join these 3 history tables with:
1. select from projects_history with projectID = 1
2. if no records, then go back to projects and get projectID = 1
3. select from task_history with taskID = 1
4. if no records, then go back to tasks with taskID = 1
5. select from client_history with clientID = 1
6. if no records, then go back to client with clientID = 1
where client.taskID = task.taskId and task.projectID = project.projectID.
View 1 Replies
View Related
Aug 25, 2011
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')
View 4 Replies
View Related
Nov 23, 2011
I have a history table which has a new record written to it for every time static data is updated. What I want to do is return any record that has been changed on a daily basis returning both the new record and the previous record so you can see the 2 records one below the other and compare.I have written the below but it returns all records per account:
SELECT H.Timestamp,H.AccountNo FROM History H
where exists (select H2.AccountNo,count(*) from History H2
where H2.AccountNo = H.AccountNo
group by H2.AccountNo having count(*) > 1 )
Where H.Timestamp > '20111101'
order by H.AccountNo,H.Timestamp
View 18 Replies
View Related
May 1, 2012
I developed a custom form upon which I developed a query find block to query the data on the main form. I have WHO columns in the table and referencing them in the main form. But Some reason when I query the data by pressing the find button on the query block it is retreiving the data properly, but record history is not enabling.
In the query find block I have 3 text fields, 1 check box and 2 date fields.
View 1 Replies
View Related
Mar 6, 2013
I want to know the DATE, LOG SEQUENCE NUMBER which was applied in the Standby (DR)one year before.
I am not able to get through "v$log_history".
View 5 Replies
View Related
Jan 1, 2013
As how do I query, as when was the last time or rather list of privileges changes done for a db user and what was the changes made?
View 1 Replies
View Related
Jul 29, 2011
find the recent SQL history from Oracle 11. could the following SQL retrieve all SQL history happened in last 5 mins?
SELECT h.user_id,
s2.username,
h.sample_time,
s1.sql_text
FROM v$active_session_history h,
v$sqlarea s1,
[code]....
View 3 Replies
View Related
Jan 17, 2013
I just applied the Jan 2013 SPU and the patch succeeded with no errors; however, when I query DBA_REGISTRY_HISTORY after running the catbundle.sql, the table is not reflecting the new patch. I checked through the catbundle log files and did not see any errors. What could be wrong?
I am running 11.2.0.3 single instance.
View 3 Replies
View Related
Jun 15, 2012
I've a one history table in which I'm putting approval history data.
For any transaction id you may have more than one record with approval status APPROVED,REJECTED,ERRORED,OVERLIMIT etc
Another program selects records from this table by passing transaction_id. For transaction id, it needs to check the most recent approved record exist or not ? If it's there then updating the record by adding comments to comments field of the same record and then delete all other records for the same transaction id.
If it does not exist then delete all other records and create one with approved status?
What's the simple and best approach to do this (sql or pl/sql)?
View 2 Replies
View Related
Mar 1, 2013
Issue with Date format. I am having data in date column in History table like
'2012/02/22 11:05:20 AM'
'2012/08/15 17:00:00'
I am doing extract from this table to txt file. i want date fomrat in "YYYYMMDD HHMMSS".
i tried below query:
select to_char(to_date('1998/05/31 11:00:00 AM','yyyy/mm/dd hh24:mi:ss'),'YYYYMMDD HH24MMSS') from dual;But it is giving error like "ORA-01830: date format picture ends before converting entire input string".
I am using ORACE 11g verion.
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
View 8 Replies
View Related
Mar 28, 2012
I want to find the history of manipulation the database by filtering the user who created the last tables, what tables are, when he created it etc ..
I'm using oracle XE and the client is toad.
View 3 Replies
View Related
Nov 20, 2012
I have written a windows service which grants and revokes based on request. How to trace in oracle data dictionary if those privilages were granted and at what time to whom and by whom. I mean is there any table which can be queried for past privilages granted to users.
View 2 Replies
View Related
Aug 9, 2010
I need to transfer 6 million records from fact tables to history table .. What is the better and fast process to do that.
View 3 Replies
View Related
Jan 28, 2011
how to get command history in sql*plus under windows
View 2 Replies
View Related
Jul 19, 2010
My stats jobs failed last night with "ORA-01652 :Unable to extend TEMP" error.
Is there any way to check this history data, what other session was using TEMP tablespace extensively ?
View 6 Replies
View Related
Jul 5, 2012
Below given is the sample data
SELECT *
FROM
(
SELECT 1 ORDR_ID, TO_DATE('01-JAN-2012', 'DD-MON-YYYY') INWD_DATE, 5 SIZE_, 'APD' Colr_CD FROM DUAL UNION ALL
SELECT 1 ORDR_ID, TO_DATE('15-JAN-2012', 'DD-MON-YYYY') INWD_DATE, NULL SIZE_, 'KPD' Colr_CD FROM DUAL UNION ALL
SELECT 1 ORDR_ID, TO_DATE('16-JAN-2012', 'DD-MON-YYYY') INWD_DATE, NULL SIZE_, 'ALD' Colr_CD FROM DUAL UNION ALL
SELECT 2 ORDR_ID, TO_DATE('02-JAN-2012', 'DD-MON-YYYY') INWD_DATE, 9 SIZE_, 'APD' Colr_CD FROM DUAL UNION ALL
SELECT 2 ORDR_ID, TO_DATE('05-JAN-2012', 'DD-MON-YYYY') INWD_DATE, 10 SIZE_, '' Colr_CD FROM DUAL UNION ALL
[code]....
Where every first row is the initial entry for an order and following rows are the changes done to the order with modification date
And the expected output is
ORDR_ID Start_date End_date SIZE_ COLR_CD
1 01-01-12 01/15/2012 5 APD
1 01/15/2012 01/16/2012 5 KPD
1 01/16/2012 00-01-00 5 ALD
2 01-02-12 01-05-12 9 APD
2 01-05-12 01-06-12 10 APD
[code]....
I cam use a select statement within select, but wanted to write an efficient SQL...
I'm using
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0
View 2 Replies
View Related
Sep 13, 2013
11.2.0.3 EVENTS: Null(blank) or called a 'null event' See link. Checked the docs. I don't see anythingI think a null (blank) is for an idle wait since per the V$ACTIVE_SESSION_HISTORY spec these are not record. Am I correct?What is a 'null event'. I see a paper from Oracle magician dating to 9i that says this is an 'oracle goof'. Descriptions of Wait Events
View 5 Replies
View Related
