Create a date trigger.

[EM1107]

Good day Everyone!

I am new to the Oracle world, I usualy work with SQL server but recently, I was asked to export all SQL Server tables from my database to Oracle. As I am new with that pl/sql scripting, I need a hand to create a trigger.

The trigger as to verify a date field.

It as to place in the current date if the user does not insert one. It as to verify that date entered is not higher than the sysdate, and should not modify it if the date is = or less than today's date.

How would I go about getting that done.

 

[SantaMufasa]

EM,

Here is trigger code that will work when you replace the items in "<...>" with your valid names:

CREATE OR REPLACE TRIGGER date_check
    BEFORE INSERT OR UPDATE
    ON <table_name_here>
    FOR EACH ROW
BEGIN
	IF :new.<date_column_name_here> is null or
           :new.<date_column_name_here> > sysdate then
            :new.<date_column_name_here> := sysdate;
	END IF;
end;
/

You didn't mention if you want this trigger to fire for UPDATE activity, so I put in the code to do so. If you don't want it to fire on UPDATE, then remove the "OR UPDATE" specification.

Let us know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[EM1107]

This is perfect! The trigger as worked no problem. The only thing I did was to remove the / at the end as this caused error.

Thanks!

 

[SantaMufasa]

The "/" was there because the default execution environment for the Oracle fora on Tek-Tips is SQL*Plus, which requires the "/" to begin execution.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[EM1107]

This is probably why my trigger did not want to fire. I have modify it and recompiled it. But I get an other error.

it seams to want to insert a date like 01/01/1753.
If I am right this is not supported in Oracle. How can I get my trigger to look for the year of the date and change it to the sysdate if the year is = 1753.

Here how my error look:
UPDATE TABLE_NAME SET COLNAME_DATE=TO_DATE('01/01/1753','MM/DD/YYYY')
WHERE SYSTEM_ID = 8188406

 

[EM1107]

Here how I have formulate my trigger!
CREATE OR REPLACE TRIGGER TR_GETDATE_IU
BEFORE INSERT OR UPDATE
ON TABLE_NAME
FOR EACH ROW
BEGIN
IF :NEW.DOCUMENT_DATE IS NULL OR
:NEW.DOCUMENT_DATE > SYSDATE OR
:NEW.DOCUMENT_DATE = '01/01/1753' OR
:NEW.DOCUMENT_DATE = ' '
THEN
:NEW.DOCUMENT_DATE = SYSDATE;
END IF;
END;


When I try to update the database I received the following error.

trigger 'TR_GETDATE_IU' is invalid and failed re-validation

 

[carp]

01/01/1753 most certainly IS supported! And it complies with your original requirement:
"should not modify it if the date is = or less than today's date."
However, to adjust for your new requirements, you could try something like:

CREATE OR REPLACE TRIGGER date_check
    BEFORE INSERT OR UPDATE
    ON my_table
    FOR EACH ROW
BEGIN
    :new.date_column :=
        LEAST(NVL(:new.date_column,SYSDATE),               
              SYSDATE);
    IF (to_char(:new.date_column,'YYYY') = '1753') THEN
       :new.date_column := SYSDATE;
    END IF;
END;