How to analyze, what happend while updating rows

Question

I have a table like this:

| id |   address   |  name  |    oid    | state | event_id | ctrl |
-------------------------------------------------------------------
| 1  | test_addr_1 | test_1 | 25.345.17 |   1   |     0    |  15  |

I need to get event_id while update data in row.

I want to do something like this:

If new name not equals with old name event_id = event_id + 1
If new oid not equals with old oid event_id = event_id + 2
If new state not equals with old state event_id = event_id + 4
If new ctrl bigger then old ctrl event_id = event_id + 8

# Params to procedure
PROCEDURE Write(IN pAddr VARCHAR(20), IN pName VARCHAR(20), IN pOid VARCHAR(20), IN pState TINYINT, IN pCtrl INT)

#procedure body
SET @ev = 0;
SELECT
CASE
    WHEN name != pName THEN SET @ev = @ev + 1
    WHEN oid != pOid THEN SET @ev = @ev + 2
    WHEN state != pState THEN SET @ev = @ev + 4
    WHEN ctrl > pCtrl THEN SET @ev = @ev + 8
END 
FROM table1 

UPDATE table1 SET ..... , event_id = @ev WHERE address = pAddr

How can I do it? Or will it be better to make it not with the help of SQL?

It sounds to me like a trigger after update would a better choice here. By the way, can you be certain that multiple of your conditions won't fire at the same time? — Tim Biegeleisen, Apr 28 '16 at 04:42

score 0 · Answer 1 · edited May 23 '17 at 10:33

As already suggested, an audit trigger can be used here to ensure that changes from all sources are caught. However, I suggest two changes:

1) audit tables - use other tables to hold audit data, as these tables tend to grow and it is not recommended to mix operational and auditing data in the same structures (even in the same database)

2) use more friendly change flag - from your example, it seems that you are setting bits in an integer value. While this provides compact data (catch many changes within a single integer), it requires more convoluted operations to see when the name has changed for example. The audit table can simply have BIT(1) columns like nameChanged, oidChanged etc.

CREATE TRIGGER table1Audit BEFORE UPDATE ON <table1>
FOR EACH ROW BEGIN
    SET @ev = 
        (CASE WHEN OLD.name != NEW.name THEN 1 ELSE 0 END) + 
        (CASE WHEN OLD.oid != NEW.oid THEN 2 ELSE 0 END) + 
        (CASE WHEN OLD.state != NEW.state THEN 4 ELSE 0 END) +  
        (CASE WHEN OLD.ctrl != NEW.ctrl THEN 8 ELSE 0 END)

    -- INSERT INTO someaudittable
    --'table1', @ev
END;

Thank you! It was helpful for me. I to know, what was happend, if values are different. The idea is: if ev == 1 (object was renamed), if ev == 4 (object may be work), if ev == 12 (object was not work) and etc. Update event_id and insert this row to history table. Is it possible to update evend_it in the same table? — txid, Apr 28 '16 at 07:12

score 0 · Accepted Answer · answered Apr 28 '16 at 08:04

Solved! Thanks to @Alexei. I wanted to know, why the row was added to the history table.

The result is:

CREATE TRIGGER SetReason BEFORE UPDATE ON <table1>
FOR EACH ROW BEGIN
    SET NEW.event_id = 
        (CASE WHEN OLD.name != NEW.name THEN 1 ELSE 0 END) + 
        (CASE WHEN OLD.oid != NEW.oid THEN 2 ELSE 0 END) + 
        (CASE WHEN OLD.state != NEW.state THEN 4 ELSE 0 END) +  
        (CASE WHEN OLD.ctrl != NEW.ctrl THEN 8 ELSE 0 END)    
END;

How to analyze, what happend while updating rows

2 Answers2