Oracle trigger if updating column
The following hierarchy is followed when a trigger is fired. 2) Next BEFORE row level trigger fires, once for each row affected.3) Then AFTER row level trigger fires once for each affected row.We would have expected (at least, I did, anyway) that Oracle would check to see if a SQL update statement failed to result in any row changes.A simple test could protect against “false updates”, and perhaps bypass the update and send an alert log message that a DML statement is doing superfluous updates.This events will alternates between BEFORE and AFTER row level triggers.4) Finally the AFTER statement level trigger fires.This is an undesirable situation where more than one trigger enter into an infinite loop.
If Oracle bypasses my update simply because it appears to be redundant then it may be subverting my intentions by leaving rows modifiable by other sessions when I want them locked.
Dear All, I have a table in which there are fields like Quantity, Price, Total etc. We have an interface where user can change the quantity (only quantity). How will I know for which row the quantity is updated..?
I want to write a trigger on this table which calculates Total (qty * price) whenever qantity is updated.
We can use the data dictionary view 'USER_TRIGGERS' to obtain information about any trigger.
The below statement shows the structure of the view 'USER_TRIGGERS' NAME Type -------------------------------------------------------- TRIGGER_NAME VARCHAR2(30) TRIGGER_TYPE VARCHAR2(16) TRIGGER_EVENT VARCHAR2(75) TABLE_OWNER VARCHAR2(30) BASE_OBJECT_TYPE VARCHAR2(16) TABLE_NAME VARCHAR2(30) COLUMN_NAME VARCHAR2(4000) REFERENCING_NAMES VARCHAR2(128) WHEN_CLAUSE VARCHAR2(4000) STATUS VARCHAR2(8) DESCRIPTION VARCHAR2(4000) ACTION_TYPE VARCHAR2(11) TRIGGER_BODY LONG This view stores information about header and body of the trigger.
overheads of roughly 5,000,000 redundant row updates per night. Burleson’s comment might seem quite sensible – after all it shouldn’t take much CPU to work out which rows should and should not be updated, and it ought to be quite simple to skip the rows that aren’t going to change. Burleson is trying to say as he fails to distinguish between updates to columns, updates to rows, and the entire update statement.