SQL & PL/SQL

Hi,

I am trying to create a trigger that will check an inserted date against SYSDATE and alter the value (i.e. make it SYSDATE) when the entered date is incorrect.

For example, I have a Customer table with a record named MemberDate which, when a date is entered to it, will be checked by the trigger to ensure it is not before the current date.

My code so far;

CREATE OR REPLACE TRIGGER Customer
BEFORE INSERT ON
Customer
FOR EACH ROW
DECLARE
e_invaliddate_ud EXCEPTION;
BEGIN
IF :NEW.MemberDate < SYSDATE THEN
RAISE e_invaliddate_ud;
END IF;
EXCEPTION
WHEN e_invaliddate_ud THEN
DBMS_OUTPUT.PUT_LINE('The date entered for MemberDate is invalid. The
MemberDate has been set to the current date');
END;
/

So far, this trigger only returns an error message but allows the date to be entered even if it is incorrect. I have tried a few ways of altering the entered date to SYSDATE but to no avail.

I am running scripts using SQL PLUS and inserting data using
INSERT INTO Customer
VALUES(CustomerID, ..., MemberDate);
Where the values entered will be checked to ensure the MemberDate is not before SYSDATE.

The MemberDate data-type is DATE and has a rule that it must not be before the current date. I am aware that a constraint may not be used to perform a check using SYSDATE, this is why I am trying to make a trigger.

However, my knowledge of triggers is limited

If anyone can help, I will be very Happy.

Thank you in advance.