Oracle pl sql trigger updating
It is important to maintain the history of the prices of the products.We can create a trigger to update the 'product_price_history' table when the price of the product is updated in the 'product' table.1) Create the 'product' table and 'product_price_history' table Once the above update query is executed, the trigger fires and updates the 'product_price_history' table.4)If you ROLLBACK the transaction before committing to the database, the data inserted to the table is also rolled back.
A trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table.There are two types of triggers based on the which level it is triggered.1) Row level trigger - An event is triggered for each row upated, inserted or deleted. all I have a situation where I have to check for insert on a table or change for a perticular field in the table and populate two other fields of the same table.I am trying it at a smaller level with this example but it is not giving me updated value of the column col3. Created a new table : ================ create table mytest1(col1 number not null , col2 number not null, col3 number ) Created a procedure to update col3 ============================= create or replace procedure mytestproc(in_col1 in number) as pragma autonomous_transaction; begin update mytest1 set col3 = 500 where col1 = in_col1; commit; end; Created a trigger to check for insert or update (right now for any column) ================================== create or replace trigger Trig On Test1 after insert or update on mytest1 referencing OLD as old NEW as new for each row begin if :new.col1 = 100 then mytestproc ( :new.col1 ); end if; end; when trying to insert values it does not calcualte the value for column col3 and populated NULL insert into mytest1 values(100,2,3); Please let me know if any one has any suggestions Thanks The procedure is an autonomous transaction.