Home » RDBMS Server » Server Administration » How to use Trigger move record from one table to another table?
How to use Trigger move record from one table to another table? [message #374130] Sat, 26 May 2001 19:40 Go to next message
Mon
Messages: 7
Registered: May 2001
Junior Member
I want trigger example to move record from one table to another table by check from specific field
example

table ALLUSER have field (ID,NAME,SURNAME,STATUS)
when I update field STATUS to "D" it mean user dead
trigger will remove that record from table ALLUSER and insert into table DEADUSER
and when I update STATUS to "I" it mean user inactive
trigger will remove that record from table ALLUSER and insert into table INACTIVEUSER

Thanks

Report message to a moderator

Re: How to use Trigger move record from one table to another table? [message #374155 is a reply to message #374130] Mon, 28 May 2001 15:54 Go to previous messageGo to next message
Sudhakar Atmakuru
Messages: 58
Registered: May 2001
Member
CREATE OR REPLACE TRIGGER ALLUSER_TRIGGER
BEFORE UPDATE OF STATUS ON ALLUSER
FOR EACH ROW
WHEN (NEW.STATUS='D' OR NEW.STATUS='I')
BEGIN
IF (:NEW.STATUS='I') THEN
INSERT INTO INACTIVEUSER VALUES (:OLD.ID,:OLD.NAME,:OLD.SURNAME,:NEW.STATUS);
ELSE IF (:NEW.STATUS='D') THEN
INSERT INTO DEADUSER VALUES (:OLD.ID,:OLD.NAME,:OLD.SURNAME,:NEW.STATUS);
END IF;
END IF;
END;
/

Report message to a moderator

Re: How to use Trigger move record from one table to another table? [message #374156 is a reply to message #374130] Mon, 28 May 2001 16:09 Go to previous message
Sudhakar Atmakuru
Messages: 58
Registered: May 2001
Member
Better to write a trigger that inserts inactive and deaduser records into INACTIVEUSER table and DEADUSER tables respectively when an update takes place in ALLUSER for STATUS with either 'I' or 'D', and create another proc to remove all the records from ALLUSER where their STATUS match either 'I' or 'D'. I dont think it is possible to remove the records by one trigger, which is triggered during the update of records, for deletion of same that is being checked. In reply to this same question, I gave a trigger example for it. Please check it up.

Report message to a moderator

Previous Topic: ORA-01722
Next Topic: What's Wrong with My View
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Dec 23 12:26:51 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.