Oracle plsql if updating
CREATED_DATE); END; --Inserting values INSERT INTO BANK_TRANSACTIONS values ('1','TXN1234','Peter Thomas','12-MAY-2017','HR',sysdate); INSERT INTO BANK_TRANSACTIONS values ('2','TXN9999','Jemes Patel','10-JUN-2016','HR',sysdate); select * from BANK_TRANSACTIONS; --updating values.
All INSTEAD OF triggers are fired for each row and you cannot narrow down the event by column.
There are 2 syntaxes for an update query in Oracle depending on whether you are performing a traditional update or updating one table with data from another table.
You can also perform more complicated updates in Oracle.
create table Employee( 2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 / Table created. It works, but it won't tell you if the value changed, only that the column is being updated (even if the update doesn't change the value).Many applications update all of the columns in a table -- setting them to their old value, rather then trying to figure out exactly which columns have been updated by the client. Let's look at a simple example: create or replace trigger test_trigger 2 after update of x,y on test 3 for each row 4 begin 5 if updating( 'X' ) then 6 dbms_output.put_line( 'X is being updated' ); 7 end if; 8 9 if updating( 'Y' ) then 10 dbms_output.put_line( 'Y is being updated' ); 11 end if; 12 end; 13 / Trigger created. You can use updating (column) but it will only tell you that the column itself is being updated and not if the value has changed.Visit Stack Exchange Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Sign up to join this community CREATE OR REPLACE TRIGGER TRIGGER1 INSTEAD OF INSERT OR UPDATE OR DELETE ON PO_COSTED_BOM_V BEGIN UPDATE SRC_TBL SET CFG_VALUE = : NEW. CFG_NAME; IF SQL%NOTFOUND THEN INSERT INTO SRC_TBL (CFG_NAME, CFG_VALUE) VALUES(: NEW. CFG_VALUE); END IF; END; Detecting the DML Operation that Fired a Trigger If more than one type of DML operation can fire a trigger (for example, ON INSERT OR DELETE OR UPDATE OF emp), the trigger body can use the conditional predicates INSERTING, DELETING, and UPDATING to check which type of statement fire the trigger.Within the code of the trigger body, you can execute blocks of code depending on the kind of DML operation that fired the trigger: ...