HeyI 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_ID NUMBER(9,0), CISCOWORKS_MAC VARCHAR2(20 BYTE), CISCOWORKS_SWITCH VARCHAR2(10 BYTE), CISCOWORKS_PORT VARCHAR2(10 BYTE), CISCOWORKS_VLAN VARCHAR2(10 BYTE), CISCOWORKS_LASTSEEN TIMESTAMP (6), CONSTRAINT CISCOWORKS_PK PRIMARY KEY (CISCOWORKS_ID));DROP TABLE CISCOWORKS_HISTORY IF EXISTS;CREATE TABLE CISCOWORKS_HISTORY( CISCOWORKS_HISTORY_ID NUMBER(9,0), CISCOWORKS_HISTORY_MAC VARCHAR2(20 BYTE), CISCOWORKS_HISTORY_SWITCH VARCHAR2(10 BYTE), CISCOWORKS_HISTORY_PORT VARCHAR2(10 BYTE), CISCOWORKS_HISTORY_VLAN VARCHAR2(10 BYTE), CISCOWORKS_HISTORY_LASTSEEN TIMESTAMP (6), CONSTRAINT CISCOWORKS_HISTORY_PK PRIMARY KEY (CISCOWORKS_HISTORY_ID));
I figured triggers will do the trick, but I'm not sure how they work.I guess I will need aDROP TRIGGER IF EXISTS CISCOWORKS_HISTORY_TRIGGER $$CREATE TRIGGER CISCOWORKS_HISTORY_TRIGGERON CISCOWORKS FOR INSERT BEGIN ... END;$$
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.Did I make any flaws in my reasoning? And how would I check every entry separately?Thanks in advance.
Trigger to move records to history
Posted - 2010-03-23 : 04:35:29
