intruder intruder - 28 days ago 16
SQL Question

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?

Answer
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;