CODEWITHSUNDEEP
sqloracle-databasetriggers
intruder
intruder

Reputation: 417

Trigger to see if a table is updated

To start off, I'm not a DB guy. Just in a learning phase of it. Let's consider a table named Accounts. It has four columns as mentioned below. 

---------------------------------------------
|  PK  |   AcType  |  FName | LName |  Zip  |
---------------------------------------------
|   1  |  Savings  |  AAA   |  ZZZ  | 11111 |
|   2  |  Checking |  BBB   |  YYY  | 22222 |
|   3  |  Checking |  CCC   |  XXX  | 33333 |
---------------------------------------------

How do we determine if a value is changed in the table?
(It may be a single change or multiple changes)

For ex: 

---------------------------------------------
|  PK  |   AcType  |  FName | LName |  Zip  |
---------------------------------------------
|   1  |  Savings  |  AAA   |  ZZZ  | 11111 |
|   2  |  Savings  |  BBB   |  YYY  | 22222 |
|   3  |  Checking |  CCC   |  XXX  | 33333 |
---------------------------------------------

We can see that data is changed in AcType of second row to Savings.

How do we implement a trigger to determine if there is a change (either of INSERT, DELETE, UPDATE) in any of the fields of the table?

Upvotes: 1

Views: 804

Answers (2)

bohuss
bohuss

Reputation: 336

If you did really mean it like you need to know any change of the table, you can do this:

CREATE or REPLACE TRIGGER AccountsChanged
AFTER INSERT OR DELETE OR UPDATE ON Accounts
BEGIN
    doWhatEverYouNeed;
END;

where doWhatEverYouNeed is your procedure taking an action after the table is modified

however it will trigger also on any update, even if the values wouldn't change if you need to trigger only when some value is changed, you need to use something like this:

CREATE or REPLACE TRIGGER AccountsChanged
AFTER INSERT OR DELETE OR UPDATE ON Accounts
FOR EACH ROW
BEGIN
    if updating 
    then
        IF :NEW.AcType <> :OLD.AcType or :NEW.FName <> :OLD.FName or :NEW.LName <> :OLD.LName or :NEW.Zip <> :OLD.Zip
        then
            doWhatEverYouNeed;
        end if;
    else
        doWhatEverYouNeed;
    end if;

END;

Upvotes: 1

Kacper
Kacper

Reputation: 4818

CREATE or REPLACE TRIGGER AccountsChanged
AFTER INSERT OR DELETE OR UPDATE ON Accounts
BEGIN
    insert into change_log_table(change_time, what_changed, change_status) values (sysdate, 'Accounts', 'TRUE')
END;

You can also recognize action

CREATE or REPLACE TRIGGER AccountsChanged
    AFTER INSERT OR DELETE OR UPDATE ON Accounts
    DECLARE
    action_type varchar2(1);
    BEGIN
      if inserting then action_type := 'I' end if;
      if updating then action_type := 'U' end if;
      if deleting then action_type := 'D' end if;
      insert into change_log_table(change_time, what_changed, change_status, action_type) 
             values (sysdate, 'Accounts', 'TRUE', action_type)
    END;

Upvotes: 1

Related Questions