CODEWITHSUNDEEP
sqloracle-databasetriggers
user2409
user2409

Reputation: 1

automatic update of date when inserting new row

I'm trying to create a trigger that will update a column date by one month whenever a new row is added.

This is what I have, can someone tell me what I'm doing wrong? 

CREATE OR REPLACE TRIGGER tg_nextupdate
BEFORE INSERT
ON Vehicle
FOR EACH ROW
BEGIN
    IF :NEW.NextUpdate = SYSDATE
    THEN
    SET NextUpdate = ADD_MONTHS(SYSDATE,1);
    END IF;
END;

Upvotes: 0

Views: 104

Answers (3)

Sylvain Leroux
Sylvain Leroux

Reputation: 52000

This is what I have, can someone tell me what I'm doing wrong?

Assuming NextUpdate having for default value SYSDATE, as it has already been say, you IF is maybe "not necessary"...

... but, as of myself, I think the real issue is SYSDATE not guaranteeing to return the same value upon each call. If you don't believe me, try that http://sqlfiddle.com/#!4/1f810/2

So, your column might very well be properly initialized by SYSDATE to, say "October, 26 2014 18:50:10+0000". But, in your trigger, SYSDATE might very well return "October, 26 2014 18:50:11+0000". This would be bad luck, I admit. And maybe this is acceptable in your application. But in a more general case, this might easily become a hard to track bug.

Depending your needs, I would suggest one of those three options instead:

1) Assuming SYSDATE is a "magical value" meaning "hey trigger! Compute the right value for NextUpdate":

CREATE OR REPLACE TRIGGER tg_nextupdate
BEFORE INSERT
ON Vehicle
FOR EACH ROW
BEGIN
    IF :NEW.NextUpdate <= SYSDATE
    THEN
        :NEW.NextUpdate := SYSDATE + INTERVAL '1' MONTH;
    END IF;
END;

So, any time in the past will trigger the calculation of a new NextUpdate. Including 1s in the past.

2) Override NextUpdate from the trigger -- always:

CREATE TABLE Vehicle (value NUMBER(10),
                NextUpdate DATE)
--              ^^^^^^^^^^^^^^^
--          No need for default here
--          as we override values
/

CREATE OR REPLACE TRIGGER tg_nextupdate
BEFORE INSERT
ON Vehicle
FOR EACH ROW
BEGIN
    :NEW.NextUpdate := SYSDATE + INTERVAL '1' MONTH;
END;
/

INSERT INTO Vehicle(value) VALUES (1)
/
INSERT INTO Vehicle VALUES (2, TO_DATE('30/10/2014','DD/MM/YYYY'))
/
INSERT INTO Vehicle VALUES (3, TO_DATE('30/12/2014','DD/MM/YYYY'))
/
INSERT INTO Vehicle VALUES (4, NULL)
/

3) Set NextUpdate defaults to SYSDATE + INTERVAL '1' MONTH, allow the user to change that when inserting. If you need it, a trigger might keep the LEAST value (+/- the 1 second error as explained in preamble):

CREATE TABLE Vehicle (value NUMBER(10),
                NextUpdate DATE DEFAULT SYSDATE + INTERVAL '1' MONTH)
--              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
--                   set default to the "most probable" value
/

CREATE OR REPLACE TRIGGER tg_nextupdate
BEFORE INSERT
ON Vehicle
FOR EACH ROW
DECLARE
    LimitNextUpdate DATE := SYSDATE + INTERVAL '1' MONTH;
BEGIN
    :NEW.NextUpdate := LEAST(LimitNextUpdate, 
                             NVL(:NEW.NextUpdate,LimitNextUpdate));
    --                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    --                      required if the used set the value to NULL
END;
/

INSERT INTO Vehicle(value) VALUES (1)
/
INSERT INTO Vehicle VALUES (2, TO_DATE('30/10/2014','DD/MM/YYYY'))
/
INSERT INTO Vehicle VALUES (3, TO_DATE('30/12/2014','DD/MM/YYYY'))
/
INSERT INTO Vehicle VALUES (4, NULL)
/

You will need to add extra logic (either in the trigger or as a check constraint) in order to reject NextUpdate in the past.

Upvotes: 0

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

There is no need of IF-END IF block, whenever a new row is inserted, it will have sysdate. So, just update the NextUpdate to ADD_MONTHS(SYSDATE,1) directly. The check on IF :NEW.NextUpdate = SYSDATE is not required.

CREATE OR REPLACE TRIGGER tg_nextupdate
BEFORE INSERT
ON Vehicle
FOR EACH ROW
BEGIN
   :NEW.NextUpdate = ADD_MONTHS(SYSDATE,1);
END;

Upvotes: 1

Dmitriy
Dmitriy

Reputation: 5565

You can encounter a problem with your code when NextUpdate contains only date, without of hours, minutes and seconds. Try this:

CREATE OR REPLACE TRIGGER tg_nextupdate
BEFORE INSERT
ON Vehicle
FOR EACH ROW
BEGIN
    IF :NEW.NextUpdate = trunc(SYSDATE)
    THEN
    SET NextUpdate = ADD_MONTHS(SYSDATE,1);
    END IF;
END;

Or give us more details about what you want and what you get with your code.

Upvotes: 0

Related Questions