Trigger to create string to inverse SQL statement
Hello,
i have a trigger which is added log history:
It is possible to create additional column here with string with inversed
SQL statement?
So in this case: "Delete FROM t_trig WHERE ID=1".
And what i want is to have possibility to loop through table and execute
inversed sql statement for each row.
Please help,
Jacek Antek
Attachments:
Hello:
On Thu, Mar 8, 2018 at 1:32 PM, Łukasz Jarych <jaryszek@gmail.com> wrote:
i have a trigger which is added log history:
I'll trust it is, but I cannot really see any thing in there, it may be
better if in the future you used something like psql and pasted the data in
a text format, which people with no-so-good eyesight like me can zoom in.
It is possible to create additional column here with string with inversed
SQL statement?So in this case: "Delete FROM t_trig WHERE ID=1".
And what i want is to have possibility to loop through table and execute
inversed sql statement for each row.
It should be. For inserts it is not that difficult, for updates YMMV, it
is difficult to reverse a command exactly, but you get new and old values
so you could write it. For deletes just reinsert. But I doubt there is a
function which does it for you, you'll have to code something, and I
suspect it may be easier to just capture op, old and new in your triggers
and use an external program to build the inverted queries.
Francisco Olarte.
Attachments:
thank you,
what do you mean use external program to build inverted queries. Do you
have any examples?
Best,
Jacek
2018-03-08 13:44 GMT+01:00 Francisco Olarte <folarte@peoplecall.com>:
Show quoted text
Hello:
On Thu, Mar 8, 2018 at 1:32 PM, Łukasz Jarych <jaryszek@gmail.com> wrote:
i have a trigger which is added log history:
I'll trust it is, but I cannot really see any thing in there, it may be
better if in the future you used something like psql and pasted the data in
a text format, which people with no-so-good eyesight like me can zoom in.It is possible to create additional column here with string with inversed
SQL statement?So in this case: "Delete FROM t_trig WHERE ID=1".
And what i want is to have possibility to loop through table and execute
inversed sql statement for each row.It should be. For inserts it is not that difficult, for updates YMMV, it
is difficult to reverse a command exactly, but you get new and old values
so you could write it. For deletes just reinsert. But I doubt there is a
function which does it for you, you'll have to code something, and I
suspect it may be easier to just capture op, old and new in your triggers
and use an external program to build the inverted queries.Francisco Olarte.
Attachments:
maybe somebody wrote SP to invert update statement?
Best,
Jacek
2018-03-08 13:51 GMT+01:00 Łukasz Jarych <jaryszek@gmail.com>:
Show quoted text
thank you,
what do you mean use external program to build inverted queries. Do you
have any examples?Best,
Jacek2018-03-08 13:44 GMT+01:00 Francisco Olarte <folarte@peoplecall.com>:
Hello:
On Thu, Mar 8, 2018 at 1:32 PM, Łukasz Jarych <jaryszek@gmail.com> wrote:
i have a trigger which is added log history:
I'll trust it is, but I cannot really see any thing in there, it may be
better if in the future you used something like psql and pasted the data in
a text format, which people with no-so-good eyesight like me can zoom in.It is possible to create additional column here with string with
inversed SQL statement?So in this case: "Delete FROM t_trig WHERE ID=1".
And what i want is to have possibility to loop through table and execute
inversed sql statement for each row.It should be. For inserts it is not that difficult, for updates YMMV, it
is difficult to reverse a command exactly, but you get new and old values
so you could write it. For deletes just reinsert. But I doubt there is a
function which does it for you, you'll have to code something, and I
suspect it may be easier to just capture op, old and new in your triggers
and use an external program to build the inverted queries.Francisco Olarte.
Attachments:
Hello:
On Thu, Mar 8, 2018 at 1:51 PM, Łukasz Jarych <jaryszek@gmail.com> wrote:
what do you mean use external program to build inverted queries. Do you have any examples?
Please, do not top quote, or the thread will get difficult to follow fast.
That being said. Capturing a DML in a trigger is relatively easy. On
recent postgres I've seen just using json to capture the whole new and
old rows, which looks promissing.
But then, building a 'rollback' DML is difficult to do in sql or
pl-pgsql, you would normally want a more normal programming language.
i.e., I would normally turn to perl for this, having used it since the
mid 90s.
Then, you have pl-perl, but this is difficult to debug/manage. So what
I would normally do is to just capture the changes in a trigger and
then have a program which queries the log table, builds the anti-query
and executes it ( no point in keeping it, since once you execute it
there is nothing to undo ). The beauty of this is you can take a
sample from your log table and easily test the program just
implementing a debug flag which prints the queries instead of
executing ( and does not touch the log table, which I assume a real
undoer will need to fro record-keepint ).
This is assuming the log is used as an "undo log", which is what I
would assume from the very scarce information I have. And this kind of
programs normally are seldom used, so postponing the query building to
a later time and logging minimal info fast is normally better ( is
like backups, you normally make backups faster, then optimize what you
can of restores, as ideally they would never be used, or transactions,
you normally optimize for commits first, then rollbacks ).
Francisco Olarte.