CODEWITHSUNDEEP
sqloracle-databasepostgresql
KaptajnKold
KaptajnKold

Reputation: 10946

Update timestamp on related referenced row on update or insert

Ruby On Rails has a neat feature when a model Foo has a belongs-to relationship to another model Bar: Whenever a Foo is created or update, a timestamp field on the referenced Bar is updated. The thing is, the feature is implemented in the Rails layer and thus only works if rows are inserted or updated by the Rails application. In a hypothetical situation where several apps shared the same database, it wouldn't work, unless a similar mechanism existed in all the apps.

The question is: How can I implement this same feature in the database layer? Is there some incantation having to do with triggers and/or foreign keys that would do the trick?

If the question cannot be answered generally, I would be interested in specific solutions for both Postgres and Oracle.

Upvotes: 0

Views: 101

Answers (1)

Guneli
Guneli

Reputation: 1731

Assuming that you have tables Foo and Bar, you can create a trigger for table Foo on INSERT AND UPDATE events and update Bar table inside this trigger. For Oracle it will be something like that:

CREATE or REPLACE TRIGGER foo_trigger
  AFTER INSERT OR UPDATE ON foo
BEGIN
 UPDATE bar SET timestamp_field=some_value;
END;

Upvotes: 1

Related Questions