CODEWITHSUNDEEP
mysqltriggerssql-update
jahajee.com
jahajee.com

Reputation: 3743

Mysql Trigger for updating table involving multiple table

I have 2 table doc and link with following structures.

doc - did|inlink|inlinkvalue|outlink|outlinkvalue|pagerank

link - did|linkto Both did and linkto has Foreign key to doc.did

I am trying to create a TRIGGER where by when links are added to link table then inlink,inlinkval and outlinkgets updated automatically in doc table.

While I am able to update the inlink and outlink count by the below TRIGGER, am unable to make sql query for inlinkval. The inlinkval value is SUM of all outlinkvalue of all documents(did) which links to this document. My present TRIGGER is

DELIMITER $$
CREATE
TRIGGER T_after_link AFTER INSERT 
ON link 
FOR EACH ROW 
BEGIN
UPDATE doc SET outlink=outlink+1 WHERE doc.did=NEW.did;
UPDATE doc SET inlink=inlink+1 WHERE doc.did=NEW.linkto;
END $$
DELIMITER ;

Upvotes: 0

Views: 37

Answers (1)

Shadow
Shadow

Reputation: 34231

You need the sum of outlinkvalue for all those dids those linkto equals with the linkto being inserted: 

select sum(outlinkvalue) from link inner join doc on link.did=doc.did where link.linkto=NEW.linkto;

Place the above query in an update:

update doc set inlinkvalue=(select outlinksum from (select sum(outlinkvalue) as outlinksum from link inner join doc on link.did=doc.did where link.linkto=NEW.linkto) t)

I used the extra subquery because mysql is fussy about selecting from a table that is being updated.

Upvotes: 1

Related Questions