Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 689
  • Last Modified:

Trigger to prevent update in Mysql

Dear All,

Please help me providing trigger in mysql that prevent update of some particular record in a table.

For example :
Name             Hobby
-------------      -----------
Willy                Swimming
Willy                Singing
Susan             Reading
Marie               Reading
Susan             Swimming
Michael            Soccer

As you can see Susan has 2 hobbies. I want to prevent update if there is a query that update all record with Susan as name.

0
softbless
Asked:
softbless
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in MYSQL, triggers are "for each row":
http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html

so, you cannot see inside the trigger if there are several rows being updated.

what you should do is to create a unique index or unique constraint on username + hobbyname, so an update cannot udpate all the rows from 1 username to the same hobby ...
0
 
SandeepratanCommented:
hi
if you want tp prevent the update only in case of the all records being updated where name = 'susan'
here is ur query... you can change according to ur requirement if i misunderstood it ..

CREATE TRIGGER updtrigger BEFORE UPDATE ON Employee
     FOR EACH ROW
     BEGIN
      
       DECLARE num_rows INTEGER;
       DECLARE tot_rows INTEGER;
      
       Select Count(*) into num_rows from Employee where name = NEW.name;
        Select Count(*) into tot_rows from Employee;
             
     IF num_rows = tot_rows THEN
     SET OLD.hobby=OLD.hobby;
     ELSE
     OLD.hobby=NEW.hobby;
     END IF;
     END
0
 
softblessAuthor Commented:
ok thanks Sandeepratan
I'll try
0
 
QlemoC++ DeveloperCommented:
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now