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 
;

seriously? You are missing a semi-colon on the update statement. — OldProgrammer
– OldProgrammer, Commented Apr 23, 2017 at 1:10
Updating the same table in an after update trigger... a mutating table exception seems like where this would end up. — MT0
– MT0, Commented Apr 23, 2017 at 1:17
Okay atleast if it works after insert then it should be fine. — Teja
– Teja, Commented Apr 23, 2017 at 1:17
Why not just use a job scheduled to run daily? Rather than updating the entire table after every change. — MT0
– MT0, Commented Apr 23, 2017 at 1:29

alexgibbs · Accepted Answer · 2017-04-23 17:02:51Z

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

edited Apr 23, 2017 at 17:02

answered Apr 23, 2017 at 14:50

alexgibbs

2,4802 gold badges18 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Teja Over a year ago

Is it possible to apply trigger for already inserted data in the table?

alexgibbs Over a year ago

Thanks @Teja Yes in the three examples in this post, the second (the simple statement-level trigger) will apply to data already in the table. You can test this out by creating the example table, adding some rows that should have fines but don't, then creating the trigger and adding new data. Both the new and old data will be updated. Or if you switch to a BEFORE trigger as recommended in the first example, you can do a one-time update of all existing data when you create the trigger, to ensure all existing data is in the expected state. Do these options work for you?

Teja Over a year ago

I tried your second example simple statement level trigger but it doesn't update existing data in the table...

alexgibbs Over a year ago

Thanks @Teja that is interesting. I'm not sure why you are not seeing existing data update. I'll update this post with an example updating existing data and follow up in just a min. Then, can you pleas run the exact example and let me know the output. Thanks

Teja Over a year ago

Sure... I can update u with the details.... both the triggers are working fine for any new inserts that are being done.... but the existing data doesn't get affected as I mentioned earlier...

|

Collectives™ on Stack Overflow

Update a column using trigger after inserting data into table

1 Answer 1

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related