CODEWITHSUNDEEP
oracle-databaseplsqltriggers
joparga3
joparga3

Reputation: 3

PLSQL ORACLE triggers - copy a specific update record from one table to another

=================================

Say you have a table A:

ID Name

1 Robert

2 Harris

You also have another table B.

This table is used to capture the "timeline" of changes that happen to table A. For instance: initially table B would be blank, but when the name "Harris" is changed to "Phillip", then a row has to be inserted into table B with all the information contained in that specific row of table A.

TABLE B (initially) --> TABLE B (after updating the name of Table A)

ID NAME --> ID NAME

-- ---- --> 2 Phillip

I have the following code tailored to the columns of my tables but I have errors using :new. (I don't really understand how to use it).

CREATE OR REPLACE TRIGGER trigger_TableA

AFTER UPDATE OF NAME ON TABLE_A

FOR EACH ROW

DECLARE

    CURSOR curCaseStatus IS

    SELECT ID, NAME

    FROM TABLE_A

    WHERE NAME = :new.NAME;

--

vRowCase curCaseStatus%ROWTYPE;

BEGIN

OPEN curCaseStatus;

FETCH curCaseStatus INTO vRowCase;

CLOSE curCaseStatus;

INSERT INTO TABLE_B

(ID, NAME)

VALUES (vRowCase.ID, vRowCase.:NEW.NAME);

END trigger_TableA

Thank you before hand by taking some time to help me. It would be really appreaciated any kind of help.

Upvotes: 0

Views: 1995

Answers (2)

joparga3
joparga3

Reputation: 3

Ok, I found a way around it.

The idea is to forget about cursors and understand that although 1 record is changed, all of the records in the same row should also be treated as new. So:

CREATE OR REPLACE TRIGGER trigger_TableA

AFTER UPDATE OF NAME ON TABLE_A

FOR EACH ROW

DECLARE

BEGIN

INSERT INTO TABLE_B

(ID, NAME)

VALUES (:new.ID, :new.NAME);

END trigger_TableA

Upvotes: 0

Michael Broughton
Michael Broughton

Reputation: 4055

For starters, if you want to track the history then you probably want to store the OLD values in table B as the new values already exist in table A? So after updating "2, Harris" to "2, Phillip" in table_a, table b should contain the previous "2, Harris" while table A holds the new "2, Phillip".

Assuming that to be correct, then first, you don't need to re-query the row as you have the :old and :new values available to the trigger. A far easier solution is to:

CREATE OR REPLACE TRIGGER trigger_TableA
AFTER UPDATE ON TABLE_A
FOR EACH ROW
BEGIN
   -- if someone performed an update that didn't do anything, do we want to archive it? Assuming not for illustration purposes.
   IF :NEW.name != :old.name
   THEN
     INSERT INTO TABLE_B (ID, NAME)
     VALUES (:old.ID, :old.NAME);
   END IF;
END trigger_TableA;

Now then, you will probably want to add a transaction timestamp to table B to order updates as a second update to the same row will give you two history records and you may want to be able to determine the order they were done in.

But this should get you started.

Upvotes: 2

Related Questions