Bug #35177 NEW and OLD objects in BEFORE UPDATE triggers
Submitted: 10 Mar 2008 8:58 Modified: 14 Jan 2009 17:18
Reporter: Zigmund Bulinsh Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:5.1.23a OS:Any
Assigned to: CPU Architecture:Any
Tags: new, old, trigger

[10 Mar 2008 8:58] Zigmund Bulinsh 
Description:
I will be happy if there will be possibility to write

SET NEW = OLD; statement in before update.

It is verry uncomfortant to set all fields separately in situations where I must prevent changes to record. Raising error is not problem, but also is not the exit from this problem.

Imagine that you have table with >20 columns!!

set NEW.col1 = OLD.col1;
set NEW.col2 = OLD.col2;
set NEW.col3 = OLD.col3;
......
set NEW.col20 = OLD.col20;
......

How to repeat:
delimiter;;
create trigger sometrigger before update on sometable
for each row
begin
  if OLD.closed then
    set NEW = OLD;
  end if;  
end;
[19 Mar 2008 10:08] Susanne Ebrecht 
Many thanks for writing a feature request.
Unfortunately I don't understand your description.

I understand:
Lets say you have this row:
a=1, b=4, c='abc', d=27, e=3.514, f=42

Now you want to update this row because you want e=1.618 and all the rest should have the same values as before.

You can reach this for example by just using:
UPDATE tab SET e=1.618 WHERE e=3.514 AND a=1;

All columns that you don't add to the SET will keep the same value as before.

Is this really what you are searching for?
[19 Mar 2008 20:33] Zigmund Bulinsh 
No it is not that I wanna to tell.
I do not talk about "update" statement.

I want syntax SET NEW = OLD; to disacrd changes in TRIGGER!
I can explain you simple examle:

insert into table1(f1, f2, f3, f4) values (1, 2, 3, 4);

after inserting fields f1, f2 must be read-only because of business logic. So it will be verry usefull if there will be this type of syntax. I wrote that there can be VERRY much columns in the table. This syntax will be used only in "before update" statement (where we can modify NEW values and have OLD values).

Did you undestand now?
[15 May 2008 10:15] Zigmund Bulinsh 
Is there some progress? Or topic is recycled..?
[15 May 2008 12:23] Susanne Ebrecht 
Sorry, but we will not fix this. 
Your suggestion is not valid at SQL standard and also there is no other RDBMS that will provide it.
[11 Jul 2008 20:26] Snoop Randin 
> Sorry, but we will not fix this. 

Ok ok ... but how can i rebuild that logic ?
It's also very important for us to "cancel" a trigger / to reset the old field values. And we think, that there must be a way to do this.

... or can we cancel an update in the before-update trigger ?
[23 Jul 2008 13:09] Zigmund Bulinsh 
Maybe it is better to change the sttaus of this report to "To be fixed later"?

Because I am not the one who request this feature.
[14 Jan 2009 7:41] kafka0102 china 
I have the need.when triggering insert,I really want cancel the insert operation for some logic.For example:
DELIMITER //
CREATE TRIGGER insert_kk_trigger
BEFORE INSERT ON kk
FOR EACH ROW 
    BEGIN   
		IF 1 THEN
		--normal logic
		ELSE
		--need cancel
 		SET NEW = NULL;
    END;//  
DELIMITER ;

Now,mysql provide the function?
[14 Jan 2009 17:18] Zigmund Bulinsh 
Just write this line:

call ERROR_CANCEL();

An that is all. ERROR_CANCEL - not existing procedure. MySQL will cancel insert.