Those of you who frequent the asktom.Web site know that I have an aversion to triggers.
Fortunately, Oracle Database and PL/SQL provide a set of true date and time datatypes that store both date and time information in a standard internal format, and they also have an extensive set of built-in functions for manipulating the date and time.
There are three datatypes you can use to work with dates and times: DECLARE l_today_date DATE := SYSDATE; l_today_timestamp TIMESTAMP := SYSTIMESTAMP; l_today_timetzone TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP; l_interval1 INTERVAL YEAR (4) TO MONTH := '2011-11'; l_interval2 INTERVAL DAY (2) TO SECOND := '15 '; BEGIN null; END; Working with intervals and time stamps with time zones can be very complicated; relatively few developers will need these more advanced features.
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.
Part 5 in a series of articles on understanding and using PL/SQL The previous articles in this introductory PL/SQL series focused on working with strings and numbers in PL/SQL-based applications.
Without a doubt, strings and numbers are important, but it is certainly a very rare application that does not also rely on dates. UPDATE bank_transactions SET txn_number = 'NEWTXN8080' WHERE txn_id = 1; UPDATE bank_transactions SET txn_number = 'NEWTXN9595' WHERE txn_id = 2; -- output 1 rows updated. Dhaval Dadhaniya is a software engineer by profession and reader/writter by passion. 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.This article represents the bare minimum you should understand about triggers before you consider writing one.clause define the timing point for the trigger, which is explained below.It has multiple parts (year, month, day, hour, and so on), and there are many rules about what constitutes a valid date.