ThePsion5 Posted February 3, 2006 Share Posted February 3, 2006 Hi again,My database consists of 3 tables, two of which contain pure data (Clients, Keywords) and the third (ClientsToKeywords) that is a relationship mapping between the two. I want to record the number of clients per keyword and the number of keywords per client and determined that the most efficient way to do this would be by creating a trigger that updates the value every time the ClientsToKeywords table is modified, but to be honest I'm new to triggers and I'm not sure how to do this. Unfortunately, I havn't been able to find a good online reference for triggers. What would the appropriate syntax be to do this, and where might I be able to find a good trigger tutorial?Database SchemaClients(ClientID, ClientName, ClientWebsite, NumKeywords)Keywords(KeywordID, Keyword, NumClients)ClientsToKeywords(ClientToKeywordID, ClientID, KeywordID, Rating)Note: Underlined fields are primary keys, bold fields are foriegn keys. Link to comment Share on other sites More sharing options...
aspnetguy Posted February 3, 2006 Share Posted February 3, 2006 what database are you using??? SQL Server? Oracle? MySql? Link to comment Share on other sites More sharing options...
ThePsion5 Posted February 3, 2006 Author Share Posted February 3, 2006 what database are you using??? SQL Server? Oracle? MySql?<{POST_SNAPBACK}> I'm using MySQL version 5.0.18...and I think I have a partial solution to my problem. If I understand the syntax correctly, this trigger should increment the NumKeywords field if a new entry is added to the ClientsToKeywords table or if a current entry is modified:CREATE TRIGGER Increment_Num_Keywords AFTER UPDATE OF ClientID ON ClientsToKeywordsFOR EACH ROWWHEN new.ClientID = Clients.ClientIDUPDATE Clients SET NumKeywords = NumKeywords + 1 I believe this should take care of incrementing the number of Keywords, but I still don't know how to decrement the old one if a client-keyword mapping is modified. Link to comment Share on other sites More sharing options...
ThePsion5 Posted February 3, 2006 Author Share Posted February 3, 2006 Sorry to double-post.I think I may have found an easier solution to implement, although more computationally expensive for the Database. CREATE TRIGGER Recount_Num_Keywords AFTER UPDATE OF ClientID ON ClientstoKeywordsBEGINUPDATE Clients SET NumKeywords (SELECT COUNT(*) FROM ClientsToKeywords WHERE ClientID = new.ClientIDEND I believe that this would work properly...my main question is would this trigger function when values are inserted, deleted, and updated? Or only when values are modified using the UPDATE command? Link to comment Share on other sites More sharing options...
aspnetguy Posted February 3, 2006 Share Posted February 3, 2006 only after update Link to comment Share on other sites More sharing options...
ThePsion5 Posted February 3, 2006 Author Share Posted February 3, 2006 only after update<{POST_SNAPBACK}> Is there a simple way to specify for this trigger to execute on UPDATE, INSERT, and DELETE? Link to comment Share on other sites More sharing options...
Kcarson Posted February 3, 2006 Share Posted February 3, 2006 Is there a simple way to specify for this trigger to execute on UPDATE, INSERT, and DELETE?<{POST_SNAPBACK}> I believe you can just list the three types one right after the other meaning:CREATE TRIGGER Recount_Num_Keywords AFTER UPDATE, INSERT, DELETE OF ClientID ON ClientstoKeywordsBEGINUPDATE Clients SET NumKeywords (SELECT COUNT(*) FROM ClientsToKeywords WHERE ClientID = new.ClientIDENDI am not positive though, so you might just give it a try. This was based off of some old documentation I read for Oracle. Link to comment Share on other sites More sharing options...
aspnetguy Posted February 3, 2006 Share Posted February 3, 2006 I believe you can just list the three types one right after the other meaning:CREATE TRIGGER Recount_Num_Keywords AFTER UPDATE, INSERT, DELETE OF ClientID ON ClientstoKeywordsBEGINUPDATE Clients SET NumKeywords (SELECT COUNT(*) FROM ClientsToKeywords WHERE ClientID = new.ClientIDENDI am not positive though, so you might just give it a try. This was based off of some old documentation I read for Oracle.<{POST_SNAPBACK}> AFAIK that should work! Link to comment Share on other sites More sharing options...
ThePsion5 Posted February 3, 2006 Author Share Posted February 3, 2006 Ok, I think I have it figured out. Here are the triggers I plan on using: CREATE TRIGGER Increment_After_Insertion AFTER INSERT ON ClientsToKeywordsFOR EACH ROW BEGINUPDATE Clients SET NumKeywords = NumKeywords+1 WHERE ClientID = NEW.ClientID;UPDATE Keywords SET NumClients = NumClients+1 WHERE keywordID = NEW.KeywordID;END;CREATE TRIGGER Decrement_After_Deletion AFTER DELETE ON ClientsToKeywordsFOR EACH ROW BEGINUPDATE Clients SET NumKeywords = NumKeywords-1 WHERE ClientID = OLD.ClientID;UPDATE Keywords SET NumClients = NumClients-1 WHERE KeywordID = OLD.KeywordID;END;CREATE TRIGGER Modify_After_Update AFTER UPDATE ON ClientsToKeywordsFOR EACH ROW BEGINUPDATE Clients SET NumKeywords = NumKeywords+1 WHERE ClientID = NEW.ClientID;UPDATE Keywords SET NumClients = NumClients+1 WHERE keywordID = NEW.KeywordID;UPDATE Clients SET NumKeywords = NumKeywords-1 WHERE ClientID = OLD.ClientID;UPDATE Keywords SET NumClients = NumClients-1 WHERE KeywordID = OLD.KeywordID;END; The Increment_After_Insertion trigger is activated when a row is inserted into the ClientsToKeywords table and increments the count when it finds a match in the Client table, and does the same for Keywords.The Decrement_After_Deletion trigger is activated when a row is removed from the ClientsToKeywords table and decrements the count when it finds the client who's info was removed.The Modify_After_Update does both since one client gained a keyword and another lost one, and does the same for keywords.Does this make sense? (Unfortunately, I couldn't combine the UPDATE, DELETE, and INSERT values in trigger syntax.) Link to comment Share on other sites More sharing options...
aspnetguy Posted February 3, 2006 Share Posted February 3, 2006 looks good...it is probably better to keep each action seperate anyways Link to comment Share on other sites More sharing options...
ThePsion5 Posted February 3, 2006 Author Share Posted February 3, 2006 It appears to be functioning perfectly. Thanks for all the help guys! Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now