Trigger for Updating Date Field

Hello,
I'm trying to write a simple trigger that will update a date field (named 'year') for an existing row. I get a bit confused as to syntax. Can someone validate as to whether I have this syntax correct? I just want to put the current year (i.e. 2010) into the date field 'year'.

create or replace trigger "SCHEMATEST".SET_YEAR
after insert on schematest.editorial for each row
begin
  update editorial set year=TO_DATE(SYSDATE,'YYYY');
end;


Thank you!
Ken
kencrestAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
Just to add to what everyone else said:  What data type is YEAR?

In the code you posted, sysdate is a date so you really want TO_CHAR(sysdate,'YYYY')

Also, there is no need to update/select in the trigger:

--Just copied kenwagers's code.
create or replace trigger SCHEMATEST.ti_SET_YEAR
BEFORE insert on SCHEMATEST.SET_YEAR for each row
BEGIN
   :new.YEAR := to_char(sysdate,'YYYY');
END;
/

0
 
sventhanCommented:
0
 
answer_dudeCommented:
2010 is not a valid date -- you need to put a valid date in a date field.  If you only want year then change the format of the year column to an integer.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
kenwagersCommented:
Try something like this - I don't think the AFTER trigger is doing what you wish.

create or replace trigger SCHEMATEST.ti_SET_YEAR
BEFORE insert on SCHEMATEST.SET_YEAR for each row
BEGIN
   select SYSDATE into :new.YEAR from dual;
END;

I assumed it's okay to have the full date.  You may still want to do as answer_dude suggest and add a new field that is a integer or character(4).
0
 
Mark GeerlingsDatabase AdministratorCommented:
In Oracle row-level triggers (those that include a "for each row" clause as yours does, you are not allowed to select from, nor insert, update or delete the triggering table itself.

What you can do in a row-level trigger,*IF* you use a "before insert' or "before update" trigger is what slightwv suggested, simply assign the value you want with a direct PL\SQL assignment statement to the :new.[column_name] you want the value in.  You do have to make sure that the datatype of that column will accept the value you try to store in it.  For example, a "date" column in Oracle will not accept a value for the year only.  But if this column is actually a char, varchar2 or number column, then (as long as it is four bytes or more) it should accept a value for the year.
0
 
kencrestAuthor Commented:
Thanks guys, your comments and suggestions worked perfectly!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.