0

Say I have something like this :

UPDATE my_table set col2 = '5' where col1 = '111';
UPDATE my_table set col2 = '5' where col3 = '112';

And now I make a before update trigger and I want to know the columns used in UPDATE statement (i.e col1, col3). In other words, can I see the exact update statement that was used in trigger?. Is this possible ?

Thank you!

penguina
  • 185
  • 2
  • 10
  • Not clear what you are trying to achieve. What does the trigger do? Are the updates above within the trigger? – Hawk Apr 30 '16 at 09:04
  • See http://stackoverflow.com/questions/13135295/plsql-new-and-old – trincot Apr 30 '16 at 09:05
  • Do you want to know the column fields or the value assigned to the columns? For the second case, using :NEW and :OLD pointers you can get the value before the update and after the update. But I don't think you can get the column names dynamically inside a trigger. – Rolson Quadras Apr 30 '16 at 09:08
  • @Rolson I know that.. I shortly say what I want to do : I have to create a log table with the updates on my_table (something like a timestamp with specifying the operation, update in my case) and then I have to update an older version of table to the current one. Is it clear? – penguina Apr 30 '16 at 09:13
  • Ok. Would this link help - http://www.techonthenet.com/oracle/triggers/before_update.php ? – Rolson Quadras Apr 30 '16 at 09:17
  • You can use `if updating (mycolumn) then ...` and do it for each column. – trincot Apr 30 '16 at 09:27
  • That won`t work.. I need the column used in where clause not the updated column. – penguina Apr 30 '16 at 09:29

1 Answers1

0

No you can't. The trigger has no knowledge of what statement led to its execution.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • Ok, can you tell me something, of course, if you know. I have to make a log table (two columns, one with date & hour, and the other one with operation made, like update in my case). After I have made this log table I have to update an older version of my_table to the version of the current one (in other words to know exactly which updates were made), of course in cronological order(this is simple). The hard part is applying that changes provided that my trigger can not know which update was applied. – penguina Apr 30 '16 at 10:51
  • The trigger knows which update was made, because it has the pseudo records `new` and `old` in which you see the changes made (`old.colx` doesn't equal `new.colx`) and the record's primary, too, because this is also in those records. It only doesn't know which exact statement led to the update, but why should it? What would you gain from that information? The trigger gets executed for every row, so `UPDATE my_table set col2 = '5' where col1 = '111'` may update, say, three rows, and the trigger would be called three times. So you could as well write three updates using the three primary keys. – Thorsten Kettner Apr 30 '16 at 11:01
  • Ok, but what information I have to write in the second column of my log table? After I have made this table, I have to update my older version.. I don`t get it. – penguina Apr 30 '16 at 11:05
  • Well, let's say my_table has these columns: id, col1, col2. Usually IDs are never changed, so you'd need a log table with the same columns. Let's further say above update statement affected two records, one with id=9, col1=111 and col2=99 and one with id=21, col1=111, col2=88. After executing the statement you'd have two records in your log table: one with id=9, col1=111 and col2=5 and one with id=21, col1=111, col2=5. Later you can create two update statements: `update old_table set col1 = 111, col2 = 5 where id = 9` and `update old_table set col1 = 111, col2 = 5 where id = 21`. – Thorsten Kettner Apr 30 '16 at 11:38