CODEWITHSUNDEEP
phpmysqlnull
john
john

Reputation: 143

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

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?

Upvotes: 0

Views: 65

Answers (3)

john
john

Reputation: 143

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 !!!

Upvotes: 0

Ravinder Reddy
Ravinder Reddy

Reputation: 24012

Options:

  1. During insert do not use those columns to insert into.
  2. Write before insert trigger to reset to null those new column values.
  3. 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 ;

Upvotes: 2

fthiella
fthiella

Reputation: 49089

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.

Upvotes: 1

Related Questions