Validating date during insert operation in pl sql with examples
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.Just select the first option Create and Configure Database using the radio button and click the Next button to proceed.We assume you are installing Oracle for the basic purpose of learning and that you are installing it on your PC or Laptop.If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.I have various validation rules to be applied while inserting the records in this table.
Data validate rules can be enforced within your application code in the application layer; or data can be validated by the database engine.now when I insert any record with acct_id as 2 and cust_id as 23,it doesent allow.But I get an error saying ORA-04098: trigger 'OPS24769.create trigger tri_account before insert or update on account for each row begin IF (:new.acct_id == 2 and :new.cust_id == 23) THEN DBMS_OUTPUT.PUT_LINE('No insertion with id 2 and 23.'); rollback; END IF; end; so this trigger is created , but with compilation error.