Home » SQL & PL/SQL » SQL & PL/SQL » Trigger to move records to history
Trigger to move records to history [message #448418] Tue, 23 March 2010 05:17 Go to next message
Tovam
Messages: 3
Registered: March 2010
Junior Member
Hey

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_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 a
DROP TRIGGER IF EXISTS CISCOWORKS_HISTORY_TRIGGER $$

CREATE TRIGGER CISCOWORKS_HISTORY_TRIGGER
ON 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.
Re: Trigger to move records to history [message #448420 is a reply to message #448418] Tue, 23 March 2010 05:25 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Is this an oracle question?
Cause this:
DROP TABLE CISCOWORKS_HISTORY IF EXISTS;
Isn't valid oracle syntax.

Assuming it is an oracle question your logic is flawed. Mainly because you can't delete a record from a table from an insert trigger on that table.
I very much doubt you want to use triggers for this at all, archiving is not really suited to triggers. More likely you want a stored procedure.

Oh and I'm not sure what you mean by this:
Quote:

And how would I check every entry separately?
Re: Trigger to move records to history [message #448429 is a reply to message #448420] Tue, 23 March 2010 06:11 Go to previous messageGo to next message
Tovam
Messages: 3
Registered: March 2010
Junior Member
My SQL is a bit rusty, so my syntax could be off. Well, the drop there doesn't really matter, I just wanted to show what the tables I'm using look like. I'll remove it.

cookiemonster wrote on Tue, 23 March 2010 05:25

Oh and I'm not sure what you mean by this:
Quote:

And how would I check every entry separately?

I meant that I only wanted to move the entries that are being replaced (an entry with the same CISCOWORKS_MAC is being inserted) to the history table. I figured the only way to do that was to check every new entry with an if.

And I will look into stored procedures, thanks.
Re: Trigger to move records to history [message #448433 is a reply to message #448429] Tue, 23 March 2010 06:28 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Tovam wrote on Tue, 23 March 2010 06:11
My SQL is a bit rusty, so my syntax could be off.
It's not so much that the syntax is off as that there is no concept of checking if an object exists when doing a drop in oracle.

Tovam wrote on Tue, 23 March 2010 06:11

cookiemonster wrote on Tue, 23 March 2010 05:25

Oh and I'm not sure what you mean by this:
Quote:

And how would I check every entry separately?

I meant that I only wanted to move the entries that are being replaced (an entry with the same CISCOWORKS_MAC is being inserted) to the history table. I figured the only way to do that was to check every new entry with an if.

So whenever you insert a record into CISCOWORKS with the same CISCOWORKS_MAC as an existing record the older record is going to be copied into CISCOWORKS_HISTORY and then deleted, correct?

Why wouldn't you just update the ciscoworks record (instead of inserting a new one) and insert the old values into the history table?
Re: Trigger to move records to history [message #448579 is a reply to message #448418] Wed, 24 March 2010 02:30 Go to previous message
Tovam
Messages: 3
Registered: March 2010
Junior Member
I found out that my reasoning was indeed flawed. I don't have to worry about triggers and all, I just have to change my java import script a bit.
Well, thanks for the help.
Previous Topic: multiple rows to single
Next Topic: show result random
Goto Forum:
  


Current Time: Sun Sep 25 09:31:10 CDT 2016

Total time taken to generate the page: 0.09777 seconds