Trigger for Updating Date Field

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
  update editorial set year=TO_DATE(SYSDATE,'YYYY');

Thank you!
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
   :new.YEAR := to_char(sysdate,'YYYY');

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.
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.

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
   select SYSDATE into :new.YEAR from dual;

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).
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.
kencrestAuthor Commented:
Thanks guys, your comments and suggestions worked perfectly!
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.