Re: MQT, triggers and row change timestamp problem

[DFreinkel <dfreinkel@xxxxxxxxxxxxxxxxx>]

Filip

As far as I know, you cannot update directly in the view.

You need to update the table directly.

In rpg you can update most index files as a logical file.

Darryl 

Sent from my iPhone

> On Jul 31, 2020, at 8:43 AM, Filip Drzewiecki via RPG400-L <rpg400-l@xxxxxxxxxxxxxxxxxx> wrote:
>
> Hi,
>
> I have a setup which looks like below:
>
> SQL View -> MQT -> SQL Table
>
> So when I update the view, instead of trigger update mqt and later SQL table is updated. It works very well until I've decided to add a row change timestamp column to SQL Table.
> ALTER TABLE SQLtable
> ADD COLUMN change_timestamp timestamp FOR EACH ROW ON UPDATE AS ROW CHANGE timestamp NOT NULL;
>
> After that, if I try to update the SQL view, I got the trigger error that null values are not allowed in a specific field. Indeed, this field is not null but I'm not updating it directly(just changed one other column) and I've checked SQL statement with performance monitor and there is no sign of null in the update statement. I can, of course, update the SQL table without any issues. It only fails when I update the view(cuz no triggers I guess).
>
> When I remove row change timestamp from the SQL table, everything start works normally.
>
>
> Filip Drzewiecki
> RPG Developer
>
> e-mail: fdrzewiecki@xxxxxxxxxxxxxxxxx<mailto:fdrzewiecki@xxxxxxxxxxxxxxxxx>
> kom.: +48 608 438 591
> [cid:image001.png@01D66733.DD0D2EB0]
> Objectivity Sp. z o.o.
> ul. Strzegomska 142a, 54-429 Wroclaw, Poland
> e-mail: info@xxxxxxxxxxxxxxxxx<mailto:info@xxxxxxxxxxxxxxxxx>
> [cid:image002.png@01D66733.DD0D2EB0]<http://www.objectivity.co.uk/>
> [cid:image003.png@01D66733.DD0D2EB0]
> Objectivity Sp. z o.o. is a 100% subsidiary of Objectivity Ltd.
> Statutory information: NIP: 8942941304 | REGON: 020745428 | Sad Rejonowy dla Wroclawia-Fabrycznej | VI Wydzial Gospodarczy Krajowego Rejestru Sadowego | KRS: 0000303475 | Kapital zakladowy: 500 000 PLN
>
> This email and the information it contains are confidential. If you have received this email in error please notify the sender immediately.
>
> -- 
> This is the RPG programming on IBM i (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com