Triggers and resources

[bepai]

 

CREATE TRIGGER banmanagment AFTER UPDATE ON members
FOR EACH ROW BEGIN
UPDATE t1
SET open1 = NEW.TRUE, open2 = NEW.TRUE
WHERE temp_ban = 1 OR member_banned = 1
AND SET open1 = NEW.FALSE, open2 = NEW.TRUE
WHERE temp_ban =0
OR
member_banned =0
END
$$

 

That's what I'm playing with at the moment; I've never used triggers before so hopefully I'll get some insight. First question, other then what wrong with the above I'd like to ask if the trigger activates for all rows when any column is updated? Seems a bit of a waste of resources. Example, if a members table had 20,000 members and inside the same table had their post_count which obviously gets updated constantly, it would seem silly to have the trigger activate when it's purpose is for a specific field. Am I right in thinking this?

 

[bepai]

I guess there's a timer on the edit button. Here's my progress so far to the above trigger..

 

CREATE TRIGGER bans AFTER UPDATE ON members FOR EACH ROW
UPDATE t1, (SELECT member_old_name,temp_ban,member_banned FROM members WHERE temp_ban =1 or member_banned =1) AS members
SET t1.open1 = NEW.True, t1.open2 = NEW.True
WHERE t1.usernamex = members.member_old_name

[blacknight]

have a look at http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html i dont know much about triggers but this is teh offical doc's

[Adam]

First question, other then what wrong with the above I'd like to ask if the trigger activates for all rows when any column is updated? Seems a bit of a waste of resources. Example, if a members table had 20,000 members and inside the same table had their post_count which obviously gets updated constantly, it would seem silly to have the trigger activate when it's purpose is for a specific field.

 

MySQL only supports row level triggers, like most database platforms. You can check if a column was updated however using the NEW. and OLD. values:

 

IF OLD.col_name <> NEW.col_name THEN
    ...
END IF

 

That should allow you to only perform further statements in certain situations.

 

 

Am I right in thinking this?

 

Was that your second question?

[bepai]

"trigger activates for all rows when any column is updated?"

 

I meant field, not column. I.e. if the post_count field is updated will that fire off the trigger regardless? I suppose a better question would be can you put triggers ON tablename.columnname so that it is only fired when an update in that column for any row that is updated? Basically, like my trigger shows, I want the trigger to run after an update to 'members_banned' or 'temp_ban' and depending on the values apply new values else where.

[Adam]

I meant field, not column. I.e. if the post_count field is updated will that fire off the trigger regardless?

 

I suppose a better question would be can you put triggers ON tablename.columnname so that it is only fired when an update in that column for any row that is updated?

 

Contradicting yourself completely there. No, MySQL only supports row-level update triggers. As I said you can check which column was updated using IF statements, but you can't define the trigger to only execute when a certain column is updated.

[bepai]

Okay, Thank you.

[bepai]

Ok need help with the trigger now...

 

CREATE
    TRIGGER `bu_bans` BEFORE UPDATE ON `members` 
    FOR EACH ROW BEGIN IF NEW.member_banned > OLD.member_banned OR  NEW.temp_ban > OLD.temp_ban THEN
update t1 set column1="True", column2="True";
ELSEIF NEW.member_banned < OLD.member_banned OR  NEW.temp_ban < OLD.temp_ban THEN
update t1 set column1="False", column2="True";
END IF;
END;
$$

 

This works for the most part but what I need to do now is specify the row that gets altered by the "member_id" that is in both 'members' and 't1'.

 

I Don't know how to select and utilize the data WHERE t1.member_id = members.member_id