Update a column using trigger after inserting data into table

Question

I am trying to update some data using after insert triggers but I am confused with the syntax and not sure what am I doing wrong. Can anybody share your inputs on this. Thank you.

Trigger code:-

CREATE OR REPLACE TRIGGER calculate_fine_amt
AFTER INSERT OR UPDATE ON BORROWED_BY
FOR EACH ROW
BEGIN
      
      UPDATE BORROWED_BY B
         SET B.FINE =   CASE WHEN B.DUEDATE-B.RETURNDATE < 0
                                  THEN ABS( B.DUEDATE-B.RETURNDATE )*5 
                                  ELSE 0
                              END                 
END 
;

Answer 1

If possible in your use case, I would suggest switching to a BEFORE TRIGGER and mutate the incoming :NEW.FINE, provided there aren't multiple triggers in play that would interfere with each other. I'll outline AFTER alternatives further below, if BEFORE is not workable for you.

Using a BEFORE TRIGGER avoids the cost of updating the whole table, and avoids the mutating table exception that would occur from any UPDATE on the attached table in its trigger as well, and allows for a trigger on INSERT OR UPDATE.

Here's an example:

First create the test table (Presumably there are other columns here about the borrowed item.)

CREATE TABLE BORROWED_BY (
  DUEDATE    DATE,
  RETURNDATE DATE,
  FINE       NUMBER
);

Then create the trigger (I'd suggest truncating dates {or getting some rounding into the mix}, so your FINEs are whole numbers):

CREATE OR REPLACE TRIGGER CALCULATE_FINE_AMT
BEFORE INSERT OR UPDATE ON BORROWED_BY
FOR EACH ROW
  BEGIN
    :NEW.FINE := CASE WHEN (:NEW.DUEDATE - :NEW.RETURNDATE < 0)
      THEN ABS(TRUNC(:NEW.DUEDATE) - TRUNC(:NEW.RETURNDATE)) * 5
                ELSE 0 END;
  END;
/

Then test it:

Returned on-time

INSERT INTO BORROWED_BY (DUEDATE, RETURNDATE) 
VALUES (SYSDATE - 10, SYSDATE - 20);

SELECT * FROM BORROWED_BY;

DUEDATE    RETURNDATE  FINE  
13-APR-17  03-APR-17   0     

Then update it:

UPDATE BORROWED_BY SET RETURNDATE = SYSDATE;

DUEDATE    RETURNDATE  FINE  
13-APR-17  23-APR-17   50    

Then update again:

UPDATE BORROWED_BY SET RETURNDATE = SYSDATE - 100;

SELECT * FROM BORROWED_BY;

DUEDATE    RETURNDATE  FINE  
13-APR-17  13-JAN-17   0     

New item returned late:

ROLLBACK;
INSERT INTO BORROWED_BY (DUEDATE, RETURNDATE) 
VALUES (SYSDATE - 5, SYSDATE);

SELECT * FROM BORROWED_BY;

DUEDATE    RETURNDATE  FINE  
18-APR-17  23-APR-17   25    

If this really must be an AFTER trigger, then there would be higher cost associated, and I would suggest that you switch to only AFTER INSERT rather than INSERT OR UPDATE to avoid recursive trigger execution. As others have pointed out in the comments, you'd need to switch to a statement-level (or compound) trigger.

Here is an example:

As a statement-level trigger. This is costly (and can have undesirable interactions with other triggers, etc), since it updates the whole table after every statement:

EDIT: adding demonstration of statement-level trigger updating existing data.

DROP TABLE BORROWED_BY;

CREATE TABLE BORROWED_BY (
  DUEDATE    DATE,
  RETURNDATE DATE,
  FINE       NUMBER
);

Add some incorrect initial data:

INSERT INTO BORROWED_BY VALUES (SYSDATE - 100, SYSDATE, -1919);
INSERT INTO BORROWED_BY VALUES (SYSDATE - 200, SYSDATE, -1919);
INSERT INTO BORROWED_BY VALUES (SYSDATE - 300, SYSDATE, -1919);
COMMIT;

And verify it:

SELECT * FROM BORROWED_BY;

DUEDATE    RETURNDATE  FINE   
13-JAN-17  23-APR-17   -1919  
05-OCT-16  23-APR-17   -1919  
27-JUN-16  23-APR-17   -1919  

Then create the trigger:

CREATE OR REPLACE TRIGGER CALCULATE_FINE_AMT
AFTER INSERT ON BORROWED_BY
  BEGIN
    UPDATE BORROWED_BY
    SET FINE = CASE WHEN (DUEDATE - RETURNDATE < 0)
      THEN ABS(TRUNC(DUEDATE) - TRUNC(RETURNDATE)) * 5
               ELSE 0 END;
  END;
/

And add new data:

INSERT INTO BORROWED_BY VALUES (SYSDATE, SYSDATE, NULL);

And check the old and new records:

SELECT * FROM BORROWED_BY;

DUEDATE    RETURNDATE  FINE  
13-JAN-17  23-APR-17   500   
05-OCT-16  23-APR-17   1000  
27-JUN-16  23-APR-17   1500  
23-APR-17  23-APR-17   0  

All the records have had their FINEs updated.

End Edit

If the table gets big, you can (possibly, benchmarking is always recommended) reduce the cost (somewhat) via a compound-trigger. Here's a general example, but I'd suggest comparing alternatives and tuning for your use case.

Recreate the table with a primary key:

CREATE TABLE BORROWED_BY (
  BORROWED_BY_ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  DUEDATE    DATE,
  RETURNDATE DATE,
  FINE       NUMBER
);

Then create a compound-trigger:

CREATE OR REPLACE TRIGGER CALCULATE_FINE_AMT
FOR INSERT ON BORROWED_BY
COMPOUND TRIGGER
  TYPE NUMBER_TAB IS TABLE OF NUMBER;
  V_KEYS NUMBER_TAB;

  BEFORE STATEMENT IS
  BEGIN
    V_KEYS := NUMBER_TAB();
  END BEFORE STATEMENT;

  AFTER EACH ROW
    IS
  BEGIN
    V_KEYS.EXTEND;
    V_KEYS(V_KEYS.COUNT) := :NEW.BORROWED_BY_ID;
  END AFTER EACH ROW;

  AFTER STATEMENT IS
  BEGIN

    FOR BORROWED_POINTER IN 1..V_KEYS.COUNT
    LOOP
      UPDATE BORROWED_BY
      SET FINE = CASE WHEN (DUEDATE - RETURNDATE < 0)
        THEN ABS(TRUNC(DUEDATE) - TRUNC(RETURNDATE)) * 5
                 ELSE 0 END
      WHERE BORROWED_BY_ID = V_KEYS(BORROWED_POINTER);
    END LOOP;
  END AFTER STATEMENT;

END CALCULATE_FINE_AMT;
/

Then Test it:

INSERT INTO BORROWED_BY (DUEDATE, RETURNDATE) VALUES (SYSDATE - 5, SYSDATE);
INSERT INTO BORROWED_BY (DUEDATE, RETURNDATE) VALUES (SYSDATE - 20, SYSDATE - 10);
INSERT INTO BORROWED_BY (DUEDATE, RETURNDATE) VALUES (SYSDATE - 30, SYSDATE - 40);

SELECT *
FROM BORROWED_BY;

BORROWED_BY_ID  DUEDATE    RETURNDATE  FINE  
4               18-APR-17  23-APR-17   25    
5               03-APR-17  13-APR-17   50    
6               24-MAR-17  14-MAR-17   0     

​