The Oracle database has a table with multiple columns, with 1 column named DATEMODIFIED. When I update the table via script:

UPDATE TABLE1
  SET AMOUNT = 10
WHERE ID IN (3,4,5,6,7); 
COMMIT; 

Then the date is column named DATEMODIFIED would be updated automatically. I can find the trigger code as follows:

CREATE OR REPLACE TRIGGER SET_TABLE1_DATEMODIFIED
  BEFORE UPDATE ON TABLE1 FOR EACH ROW
BEGIN
  :NEW.DATEMODIFIED := SYSDATE;
END;

However, I can find multiple instances that the AMOUNT value was update, without triggering a change in DATEMODIFIED. How can the Oracle system achieve such change? Are there anyway query can amend database values disregarding TRIGGER statements?

CodePudding user response：

Most likely, one of the following options

• The trigger didn't exist when the row was updated
• The trigger was disabled when the row was updated
• Some other SQL statement set lastModified to null

Of course, there is also the possibility that you are not accurately identifying cases where the amount was updated. For example, perhaps someone or something deleted the row and re-inserted it rather than doing an actual update.

CodePudding user response：

Several options I can think about:

• trigger fires before update, which means that INSERT INTO wouldn't fire it
• trigger was created AFTER those amount values were updated (so there were no triggers to be fired)
• trigger was disabled / dropped before those amount values were updated, and then enabled / recreated