Oracle pl sql trigger updating

posted by | Leave a comment

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.

Leave a Reply

  1. Adult dating svcs for plump women 23-Mar-2017 11:15

    If the answer's 'yes', and you're in good shape, 22-35, and can drive to the Greenwich area, then get in touch now! Please only reply if you are looking to receive quality oral, are in dece…

fender ultimate chorus amplifier dating