MySQL Forums
Forum List  »  Triggers

Re: fetch updated columns in 'before update' trigger
Posted by: Jon Beyer
Date: September 05, 2019 10:15PM

> > 'delete and recreate updated trigger'
> Can't be done while the Trigger is being invoked.

I think I'm still not getting across what I'm trying to do. I'm not trying to update a trigger while it is being invoked. As it currently stands, if I don't have access to a function inside the trigger that tells me what column is being updated, then I need a clause inside the trigger for every single column. Thus, if a month from now, I add a now column, I need to modify my trigger. This is brittle. So I'm looking for something equivalent to COLUMNS_UPDATED( ) in SQL Server.

> > I don't want to have to modify my trigger every
> time I add a column
> SQL isn't a complete computer language, Triggers
> and much else need specific table & column specs.
> And, in a correctly designed database, DDL changes
> should be rare except when the requirement changes
> enough to require a major upgrade, so if you
> expect to be adding columns often, what problem
> drives that expectation?

The changes will not be frequent, but it's pretty ugly to have a clause inside a trigger for every single column in the table.

> > list of updated columns
> You'd need to code that yourself, eg by storing a
> copy of the Update statement in a table and having
> the Trigger retrieve and parse it. But it might
> not be helpful---MySQL doesn't support dynamic SQL
> in Triggers.

This is probably not a good option. I'm using an ORM (ActiveRecord / Rails), so adding the complexity of storing an update statement before executing said statement, reading and parsing said statement from a table, isn't really cleaner than current solution of having a monstrously long trigger.

Options: ReplyQuote

Written By
Re: fetch updated columns in 'before update' trigger
September 05, 2019 10:15PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.