0

I have a SQL view, which resolves some foreign key dependencies. The View looks like this:

id | title | plantName | customerName | lastUpdated

Where plantName and customerName are 2 resolved values of a foreign key.

There are 3 Tables in total:

Table 1
id | name | surname | idTable2 | idTable3 | lastUpdated

Table 2
id | plantName

Table 3
id | firstName | lastName

What i want to archieve is a trigger which updates the lastUpdated column in the View everytime a row shows another value. The Problem here is, that lastUpdated inside the View should also be updated when a foreign key changes it's value.

Example view Output:

1 | hotel | mock | meier | 2020-02-26 10:03:03.817
2 | hotel | raddison | mueller | 2020-02-26 10:04:03.000

Let's say I'll update Table 2 and update "raddison" to "mercure hotel". The View will look like this afterwards:

1 | hotel | mock | meier | 2020-02-26 10:03:03.817
2 | hotel | mercure hotel | mueller | 2020-02-26 10:04:03.000

I now want that the lastUpdated Column in the View of row 2 representing the datetime where the foreign key has changed. Desired output:

1 | hotel | mock | meier | 2020-02-26 10:03:03.817
2 | hotel | mercure hotel | mueller | 2020-02-26 13:44:03.000

Is there any way to archieve this?

Thanks in Advance

xTheProgrammer
  • 74
  • 10
  • Only one of your tables has "last updated". Is that an oversight? – Neville Kuyt Feb 26 '20 at 12:48
  • No thats not an oversight. The view has it initially from Table 1, but my aim is to only overwrite the value of "last updated" in the view. – xTheProgrammer Feb 26 '20 at 12:49
  • 1
    Views don't *store* any data of their own (outside of indexed views, which effectively have *caches*, not independent data). You can't update the "last updated" of the view. It doesn't exist. – Damien_The_Unbeliever Feb 26 '20 at 13:09
  • Damn, that's what I expected. – xTheProgrammer Feb 26 '20 at 13:11
  • Are you using front end programming or plain sql? – cci sugar Feb 26 '20 at 12:46
  • It helps no one to imply some sort of botanical schema when your sample data implies hotels. – SMor Feb 26 '20 at 13:53
  • So, you want to update the "last_updated" column in table 1 whenever a value changes in tables 1, 2 or 3, and include that "last_updated" value in your view? – Neville Kuyt Feb 26 '20 at 14:09
  • Sounds like you just want an update trigger on table1 so that when that row is changed it updates the last_updated column. – Sean Lange Feb 26 '20 at 14:33
  • You can create `update` triggers on all three tables and they can all update `Table 1`. Since there is no view involved it would have to be based on the relations, e.g. a change in `Table 2` may effect 7 rows in `Table 1` that share the same plant because there is no way to differentiate between them within the trigger. – HABO Feb 26 '20 at 14:34
  • Can you change the underlying table structure? If so, you could put a `lastUpdated` column on all the tables and then return the most recent `lastUpdated` in the view. – iamdave Feb 26 '20 at 15:00
  • @iamdave I can change it. How do I then use the mostReced lastUpdated column in the view? – xTheProgrammer Feb 27 '20 at 07:08
  • @NevilleKuyt Exactly. – xTheProgrammer Feb 27 '20 at 07:27
  • Do you already have the view? – Neville Kuyt Feb 27 '20 at 15:18

1 Answers1

1

In the comments, you confirmed that you want the following:

you want to update the "last_updated" column in table 1 whenever a value changes in tables 1, 2 or 3, and include that "last_updated" value in your view

This question shows you how to create a trigger to update a column when a record is modified.

You will need to create that trigger on tables 1, 2 and 3, and the update statement should update table 1's lastUpdated column.

Alternatively, you could create a lastUpdated on all 3 tables, and select the highest value in your view.

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52