This week's book giveaway is in the JavaScript forum.
We're giving away four copies of Svelte and Sapper in Action and have Mark Volkmann on-line!
See this thread for details.
Win a copy of Svelte and Sapper in Action this week in the JavaScript forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

How to use NEW.<<Variable Name>> in Mysql Trigger?

 
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am trying to implement database audit task so I can track any backend changes made on any table.

For that, I have created audit table and implemented trigger on the table for which I want to log audit.

Following is my trigger code:

   

What I am doing is on the event of Insert on TABLE1, I am taking all the columns of table and traverse through each column and insert record in audit table. Here I am facing the problem with the insert query where I try to insert value using @new.columnname which inserts null value. Here columnname is not actual name, it is a variable taken from the column cursor which is not working for me. See following result:



Please provide the solution so I get the value of column in new_value.
 
Marshal
Posts: 25814
69
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I looked in the MySQL documentation (the 5.7 reference) and it said this:

The MySQL Reference Manual wrote:Within the trigger body, the OLD and NEW keywords enable you to access columns in the rows affected by a trigger. OLD and NEW are MySQL extensions to triggers; they are not case sensitive.

In an INSERT trigger, only NEW.col_name can be used; there is no old row. In a DELETE trigger, only OLD.col_name can be used; there is no new row. In an UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before it is updated and NEW.col_name to refer to the columns of the row after it is updated.



But you used "@new" instead of "NEW". Did you find a document somewhere which told you to use the @ character, or am I misreading the reference manual, or something else?
 
ChintanP Patel
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I know the usage of NEW and OLD in triggers. My question is how can we use NEW.<<Variable name which contains column name>> or not. We can user NEW.Column_name directly but what is the way to use a variable instead?
 
Paul Clapham
Marshal
Posts: 25814
69
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'd still recommend against "%new". Maybe "NEW.@columnname"?

I've been googling for answers to this question, because it seems like a perfectly normal thing to want to do, but I can't find any specific examples of that.
 
ChintanP Patel
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for your efforts. But the syntax NEW.@columnname is giving me compile time error like: ERROR 1054: Unknown column '@columnname' in 'NEW'
 
Paul Clapham
Marshal
Posts: 25814
69
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'd guess you are out of luck -- I didn't find anybody asking your question on e.g. StackOverflow or anywhere else. That's a pity because like I said, what you're trying to do looks like a reasonable thing to do. I could be wrong, but I don't have any other guesses.
 
Master Rancher
Posts: 4664
49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
MySQL has PREPARE and EXECUTE, which allows you to turn a string into a query and then execute it.
Wouldn't that be suitable for something like this?

Oh, hang on, scratch that...can't use dynamic SQL in triggers apparently.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
    Bookmark Topic Watch Topic
  • New Topic