Conditional Insert in mysql

Question

I am really a beginner in mysql. In oracle we can use triggers , which can detect the insert elements and allows to fully break the insert command if something is wrong. I've found that mysql also supports triggers, but how can we use them for detecting insert parameters and stopping them to be inserted if they don't satisfy rules.

e.g. INSERT INTO accounts (userId, balance) VALUES ('12','450'); // Valid
     INSERT INTO accounts (userId, balance) VALUES ('12','-574'); // Invalid

if(balance<0){
  Do not insert;
}
else{
   Insert;
}

NOTE: I'm dealing with concurrent transactions, so STRICTLY need triggers, i.e. lowest level error detection so that no one can hack. Any help will be appreciated. Thanks,

Where's the problem, it's just about how you describe it in PHP as well. You check for the $balance variable for negativity, and act appropriately. — Madara's Ghost
– Madara's Ghost, Commented Oct 16, 2012 at 18:05
I'm dealing with concurrent transactions, so STRICTLY need trigger, i.e. lowest level error detection so that no one can hack. — Abhishek Bhardwaj
– Abhishek Bhardwaj, Commented Oct 16, 2012 at 18:07

Udan · Accepted Answer · 2012-10-16 18:15:36Z

2

Or use an BEFORE INSERT trigger

DELIMITER $$

CREATE TRIGGER au_a_each BEFORE INSERT ON accounts FOR EACH ROW
BEGIN
IF new.balance > 0 THEN
  BEGIN
    INSERT INTO b (id,balance) VALUES (new.id, new.balance);
  END 
END $$
DELIMITER ;

More info in the mysql documentation : http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html

PS: Programming lesson number 1(One with capital "o") - Befriend whatever programming/scripting language's documentation

answered Oct 16, 2012 at 18:15

Udan

5,6192 gold badges31 silver badges34 bronze badges

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

Comments

Ertunç · Accepted Answer · 2012-10-16 18:08:34Z

1

You may use INSERT IGNORE and set ALTER TABLE field constraints in mysql

answered Oct 16, 2012 at 18:08

Ertunç

8291 gold badge9 silver badges21 bronze badges

2 Comments

Abhishek Bhardwaj Over a year ago

As I said I'm recently switched to mysql, please provide a decent example.

Ertunç Over a year ago

I assume you have bulk data in a form of INSERT INTO statements. You can set field attribute UNSIGNED for the balance field while creating the table. Then change your slq statements to INSERT IGNORE INTO. Data where balance field values are greater or equal to zero will be inserted. The others will not give error, but also not inserted i.e. they will be ignored .

Collectives™ on Stack Overflow

Conditional Insert in mysql

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related