mysql default value NULL "unchageble" - is it possible?

Question

I am using a licensed software for my website, and of course the engine is encrypted. i have no access and modify to any php code... It has a feature that i want to change, and i can't modify the php code, since the vendor doesn't allow me to do it... The only option is to build a separate custom script and run it separately to update fields in database (which is not suitable for my project) OR to change the values manual in mysql database (again it's not suitable)

There is a third option, and i don't know if it's really possible....

here's the scenario: The script ads a new row in database whenever the user click on a particular link. That row has 10 fields. last 4 fields are NULL by default and they should stay NULL. The script insert values in that last 4 fields, and it's unusual, and of course the script is not working properly anymore.

So my question is: Is there any way for me to prevent the insertion of the values for that 4 fields in database ? Can it be locked to NULL ? Can "SET / UPDATE" function be ignored for that fields?

Ravinder Reddy · Accepted Answer · 2014-01-23 12:29:54Z

2

Options:

During insert do not use those columns to insert into.
Write before insert trigger to reset to null those new column values.
Writer before update trigger to reset to null those new column values, based on a where condition.

Update:

If you do not have access to your php code to modify the insert statement, you can only achieve this by defining triggers in database. For this to happen, you should at the least have various privileges like remote connect, create, execute triggers, etc. Unless which you can't do this.

If you have such privileges, you can try on your data table something similar to the following:

before insert trigger as below:

delimiter $$

drop trigger if exists bfimt_omit_colum_data $$

create trigger bfimt_omit_colum_data before insert on my_table
  for each row begin
    set NEW.col_name_4_to_set_null = NULL,
        NEW.col_name_5_to_set_null = NULL,
        NEW.col_name_6_to_set_null = NULL;
end;

$$

delimiter ;

Similarly the before update trigger as below:

delimiter $$

drop trigger if exists bfumt_omit_colum_data $$

create trigger bfumt_omit_colum_data before update on my_table
  for each row begin
    set NEW.col_name_4_to_set_null = NULL,
        NEW.col_name_5_to_set_null = NULL,
        NEW.col_name_6_to_set_null = NULL;
end;

$$

delimiter ;

edited Jan 23, 2014 at 12:29

answered Jan 23, 2014 at 11:46

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ravinder Reddy Over a year ago

Well, the insert statement may be there in your php code, which you can't modify. You have other options. Connect to your database and define suggested triggers.

john Over a year ago

how do i do that? please explain, show me an example. thanks!

john Over a year ago

I get this error: #1235 - This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

john Over a year ago

i tried this code:

delimiter $$  drop trigger if exists bfimt_omit_colum_data $$  create trigger bfimt_omit_colum_data before insert on mytable   for each row begin     set new.col4 = NULL; end;  $$  delimiter ;

I get this error: #1235 - This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

fthiella · Accepted Answer · 2014-01-23 12:07:26Z

1

You could define a trigger like this:

delimiter //
CREATE TRIGGER set_to_null_bi BEFORE INSERT ON tablename
FOR EACH ROW
BEGIN
  SET new.col4 = NULL;
  SET new.col5 = NULL;
  SET new.col6 = NULL;
END//
delimiter ;

and you could also create a BEFORE UPDATE trigger.

answered Jan 23, 2014 at 12:07

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

3 Comments

john Over a year ago

i get a stupid error... : #1235 - This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

fthiella Over a year ago

@john oh it's probably because your application defined some triggers already... try with SHOW triggers WHERE table = 'tablename' ... then you have to update your exising triggers

john Over a year ago

i think the problem was "DEFINER", now it's solved, i've posted the correct script in my case. thanks again for your help!

john · Accepted Answer · 2014-01-23 13:30:38Z

0

Thanks all for your help, i can't say you codes are not correct, but hey didn't work for me. I manage to solve this by myself, here's the code:

DROP TRIGGER IF EXISTS `trigger_null`;
CREATE DEFINER=`root`@`localhost` TRIGGER `trigger_null` BEFORE INSERT ON `mytable` FOR EACH ROW 
  SET new.create_action = NULL,
  new.col4 = NULL,
  new.col5 = NULL,
  new.col6 = NULL,
  new.col6 = NULL,
  new.col7 = NULL,
  new.col8 = NULL;

That's all...

Thanks for your reply's !!!

answered Jan 23, 2014 at 13:30

john

1431 gold badge2 silver badges9 bronze badges

Collectives™ on Stack Overflow

mysql default value NULL "unchageble" - is it possible?

3 Answers 3

4 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related