Home » SQL & PL/SQL » SQL & PL/SQL » Update the date upon record update
Update the date upon record update [message #254482] Thu, 26 July 2007 21:16 Go to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Hi,

I have a table with two dates, one is Creation_Date and one is Last_update_date, for the Creation_Date, I use default sysdate, is there any better way that I can insert the new date value into the Last_update_date upon my record update besides using the trigger????

Regards,
Ying

[Updated on: Thu, 26 July 2007 21:40]

Report message to a moderator

Re: Update the date upon record update [message #254483 is a reply to message #254482] Thu, 26 July 2007 21:19 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Are you certain that it can be done in a trigger?
As a general rule a trigger can't modify the table upon which it is based.
Re: Update the date upon record update [message #254485 is a reply to message #254482] Thu, 26 July 2007 21:26 Go to previous messageGo to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Hi,

Ya, it can be done by creating trigger as below:

AFTER UPDATE on MyTable
Begin
Update myTable set Last_update_date = sysdate;
End:

But, I just wonder is there any better way which can do it by default.

Regards
Ying

[Updated on: Thu, 26 July 2007 21:40]

Report message to a moderator

Re: Update the date upon record update [message #254486 is a reply to message #254482] Thu, 26 July 2007 21:31 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
In a well designed application all DML for a given table is done within a single package.
If so, then the DATE_UPDATED field gets SYSDATE as part of the application UPDATE statement.
Re: Update the date upon record update [message #254487 is a reply to message #254486] Thu, 26 July 2007 21:39 Go to previous messageGo to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Hi,

No, I set botg my creation_date and last_update_date with default sysdate, and both of the date value will be inserted upon my row inserting, when I try to update the row, the both of the date value will not be changed. What I want is my last_update_date will be changed upon my latest update.


pls advise....

regards
Ying
Re: Update the date upon record update [message #254488 is a reply to message #254482] Thu, 26 July 2007 21:42 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>when I try to update the row
I don't see any SQL UPDATE
Re: Update the date upon record update [message #254673 is a reply to message #254485] Fri, 27 July 2007 07:57 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
ying wrote on Thu, 26 July 2007 22:26
Hi,

Ya, it can be done by creating trigger as below:

AFTER UPDATE on MyTable
Begin
Update myTable set Last_update_date = sysdate;
End:

But, I just wonder is there any better way which can do it by default.

Regards
Ying


First off, this is invalid syntax. Second, this looks to me like an endless loop that will never end (until maximum number of cursors are exceeded).
Re: Update the date upon record update [message #254732 is a reply to message #254482] Fri, 27 July 2007 13:42 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Create a before update trigger.


CREATE OR REPLACE TRIGGER MY_TABLE_T1
BEFORE UPDATE
ON MY_TABLE
FOR EACH ROW
BEGIN
  SELECT SYSDATE
  INTO :NEW.LAST_UPDATE_DATE
  FROM DUAL;
END;
Re: Update the date upon record update [message #254736 is a reply to message #254732] Fri, 27 July 2007 14:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
or just:
:NEW.LAST_UPDATE_DATE := SYSDATE;

Don't need to switch to SQL.

Regards
Michel
Re: Update the date upon record update [message #254737 is a reply to message #254482] Fri, 27 July 2007 14:25 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
True, a bad habit left over from Oracle 5.
Re: Update the date upon record update [message #254746 is a reply to message #254737] Fri, 27 July 2007 15:47 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
But Oracle 5 didn't have triggers. you probably just meant that you needed to SELECT everything and couldn't just assign values in general.
Re: Update the date upon record update [message #254754 is a reply to message #254482] Fri, 27 July 2007 20:15 Go to previous messageGo to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Dear All,

Thanks for all the reply......so can I conclude that it can only be achieved by creating Trigger, right???

[Updated on: Fri, 27 July 2007 20:15]

Report message to a moderator

Re: Update the date upon record update [message #254786 is a reply to message #254754] Sat, 28 July 2007 05:16 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Wasn't there an issue with using sysdate in triggers in some version? If I recall correctly, sysdate was not available in triggers.

But, then again, maybe this is my memory playing games with me...
Previous Topic: query between dates (was: anyone can resolve this query)
Next Topic: PL/SQL - Adding data to a cursor multiple times
Goto Forum:
  


Current Time: Thu Dec 08 22:35:17 CST 2016

Total time taken to generate the page: 0.09683 seconds