Trigger creation when table is altered but there will same e_id where updation or deletion will happ-CodePudding

CREATE TABLE source_det (
det_id number(10) by default IDENTITY
    e_id       NUMBER(10),
    sys_name   VARCHAR2(20),
    ref_id     NUMBER(10),
    sys_other  VARCHAR2(30)
);

INSERT INTO source_det VALUES(1,11,'SOURCE',992,null);
INSERT INTO source_det VALUES(2,11,'SOURCE',637,null);
INSERT INTO source_det VALUES(3,11,'SOURCE',null,'Manual');
INSERT INTO source_det VALUES(4,11,'TARGET',637,null);
INSERT INTO source_det VALUES(5,12,'TARGET',637,null);

Audit table:

CREATE SEQUENCE audit_tab_sq;
CREATE TABLE audit_tab (
    a_id       NUMBER(10) default audit_tab_sq.nextval,
    l_transaction varchar2(20),--INSERT, UPDATE, DELETE
    e_id       NUMBER(10),
    sys_name   VARCHAR2(20),
    value_old  VARCHAR2(20),
    value_new  VARCHAR2(20)
);

I need to create a trigger that will get fired whenever there is new event occurred on the main table i.e source_det

Example: Lets say for this record INSERT INTO source_det VALUES(11,'SOURCE',null,'Manual'); For e_id 11 whose sys_name is SOURCE and ref_id is null and sys_other is Manual.

So, for this record if user has filled in the ref_id to 321 and sys_other to Document.

Then in my audit table, a new entry will get inserted only for this row even though we have multiple e_id.

Expected Output:

 ------ --------------- ------ ---------- ----------- ----------- 
| a_id | l_transaction | e_id | sys_name | value_old | value_new |
 ------ --------------- ------ ---------- ----------- ----------- 
|    1 | UPDATE        |   11 | SOURCE   | null      | Document  |
 ------ --------------- ------ ---------- ----------- -----------

Likewise, if there are any new records inserted or deleted same should be the case.

I am wondering if this can be handled using trigger because there can be multiple e_id

Tool used: Oracle SQL Developer(18c)

CodePudding user response：

From the information you have given, it seems as though you are missing a unique value (key) for your tables and this may be causing your problems.

Per your given code, you have

CREATE TABLE source_det (
    e_id       NUMBER(10),
    sys_name   VARCHAR2(20),
    ref_id     NUMBER(10),
    sys_other  VARCHAR2(30)
);

INSERT INTO source_det VALUES(11,'SOURCE',992,null);
INSERT INTO source_det VALUES(11,'SOURCE',637,null);
INSERT INTO source_det VALUES(11,'SOURCE',null,'Manual');
INSERT INTO source_det VALUES(11,'TARGET',637,null);
INSERT INTO source_det VALUES(12,'TARGET',637,null);

The database currently has no way of differentiating between the row inserted with INSERT INTO source_det VALUES(11,'SOURCE',637,null); and the row inserted in the following INSERT INTO source_det VALUES(11,'SOURCE',637,null);.

I would recommend adding a unique value (key) to each of your tables so that there is a unique value to identify each tuple. You can achieve this in multiple ways (id, unique pair, etc.), but your dataset doesn't seem to contain unique pairs, so an id would probably work best:

CREATE TABLE source_det (
    id         NUMBER GENERATED BY DEFAULT AS IDENTITY
    e_id       NUMBER(10),
    sys_name   VARCHAR2(20),
    ref_id     NUMBER(10),
    sys_other  VARCHAR2(30),
    PRIMARY KEY(id)
);

Then you can create a trigger that fires on update | insert | delete and will have a unique key to reference for each row.

CodePudding user response：

Here's how; trigger:

SQL> CREATE OR REPLACE TRIGGER trg_sdet_audit
  2     BEFORE INSERT OR UPDATE OR DELETE
  3     ON source_det
  4     FOR EACH ROW
  5  BEGIN
  6     IF INSERTING
  7     THEN
  8        INSERT INTO audit_tab (a_id,
  9                               l_transaction,
 10                               e_id,
 11                               sys_name,
 12                               value_old,
 13                               value_new)
 14             VALUES (audit_tab_sq.NEXTVAL,
 15                     'INSERT',
 16                     :new.e_id,
 17                     :new.sys_name,
 18                     NULL,
 19                     :new.sys_other);
 20     ELSIF UPDATING
 21     THEN
 22        INSERT INTO audit_tab (a_id,
 23                               l_transaction,
 24                               e_id,
 25                               sys_name,
 26                               value_old,
 27                               value_new)
 28             VALUES (audit_tab_sq.NEXTVAL,
 29                     'UPDATE',
 30                     :new.e_id,
 31                     :new.sys_name,
 32                     :old.sys_other,
 33                     :new.sys_other);
 34     ELSIF DELETING
 35     THEN
 36        INSERT INTO audit_tab (a_id,
 37                               l_transaction,
 38                               e_id,
 39                               sys_name,
 40                               value_old,
 41                               value_new)
 42             VALUES (audit_tab_sq.NEXTVAL,
 43                     'DELETE',
 44                     :new.e_id,
 45                     :new.sys_name,
 46                     :old.sys_other,
 47                     NULL);
 48     END IF;
 49  END;
 50  /

Trigger created.

Testing:

SQL> UPDATE source_det
  2     SET sys_other = 'Other'
  3   WHERE e_id = 11;

4 rows updated.

SQL> DELETE FROM source_det
  2        WHERE det_id = 2;

1 row deleted.

Result:

SQL> SELECT * FROM audit_tab;

      A_ID L_TRANSACTION              E_ID SYS_NAME             VALUE_OLD            VALUE_NEW
---------- -------------------- ---------- -------------------- -------------------- --------------------
        16 UPDATE                       11 SOURCE                                    Other
        17 UPDATE                       11 SOURCE                                    Other
        18 UPDATE                       11 SOURCE               Manual               Other
        19 UPDATE                       11 TARGET                                    Other
        20 DELETE                                               Other

SQL>

Note that it would probably be a good idea to include timestamp into the log table.