Jump to content

Creating a Trigger


ThePsion5

Recommended Posts

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

what database are you using??? SQL Server? Oracle? MySql?

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

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

Is there a simple way to specify for this trigger to execute on UPDATE, INSERT, and DELETE?

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

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.

AFAIK that should work!
Link to comment
Share on other sites

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...