Oracle if inserting or updating

Rated 4.93/5 based on 937 customer reviews

DATE_OF_BIRTH,'DD-MON-YYYY'))/12 INTO EMP_AGE FROM DUAL; -- Check whether employee age is greater than 18 or not IF (EMP_AGE -- setting date format to to 'DD-MON-YYYY' alter session set nls_date_format = 'DD-MON-YYYY'; INSERT INTO employee_details VALUES (1,'Patel','Thomas','18-MAY-1999','01-MAY-2017','HR',sysdate); -- output 1 rows inserted.-- setting date format to to 'DD-MON-YYYY' alter session set nls_date_format = 'DD-MON-YYYY'; INSERT INTO employee_details VALUES (2,'Patel','Peter','18-MAY-2010','01-MAY-2017','HR',sysdate); -- error Error report - ORA-20000: Employee age must be greater than or equal to 18. TRG_BEFORE_EMP_INSR", line 18 ORA-04088: error during execution of trigger 'SYSTEM.The same is true for an " In the previous section we've seen what the timing points look like for individual statements. That depends on whether you are doing bulk inserts, updates or deletes using the statement.The following code builds a collection of 5 records, then uses that to drive bulk inserts, updates and deletes on the TRIGGER_TEST table.TRG_BEFORE_EMP_INSR' -- setting date format to to 'DD-MON-YYYY' alter session set nls_date_format = 'DD-MON-YYYY'; INSERT INTO employee_details VALUES (3,'Patel','Thomas','18-MAY-1999','01-MAY-2040','HR',sysdate); -- error Error report - ORA-20000: Date of death can not be Future date. TRG_BEFORE_EMP_INSR", line 23 ORA-04088: error during execution of trigger 'SYSTEM.TRG_BEFORE_EMP_INSR' CREATE OR REPLACE TRIGGER trg_before_emp_insr_userinfo BEFORE INSERT ON employee_details FOR EACH ROW DECLARE username varchar2(20); BEGIN -- Replaced by the current logged in user "HR" by a trigger. CREATED_DATE := sysdate; END; -- setting date format to to 'DD-MON-YYYY' alter session set nls_date_format = 'DD-MON-YYYY'; INSERT INTO employee_details VALUES (2,'Patel','Methew','01-JAN-1990','01-MAY-2005',null,null); INSERT INTO employee_details VALUES (3,'Patel','Methew','01-JAN-1990','01-MAY-2005','XYZ',null); select * from employee_details; Dhaval Dadhaniya is a software engineer by profession and reader/writter by passion.The workaround for this is to use variables defined in packages to store information that must be in scope for all timing points.The following code demonstrates the order in which the timing points are fired.

:

Leave a Reply