CODEWITHSUNDEEP
sqldb2triggers
pmr
pmr

Reputation: 59841

A DB2 Trigger to calculate an average in another table

I got two tables Product(Productname VARCHAR(10), Rating REAL) and Review(RatingID INT, Productname VARCHAR(10), Rating INT). The Rating of the product is the average of all Ratings given in reviews for that specifig product.
I'd like to have a trigger, that updates a product whenever a new review is inserted or update, but I just can't wrap my head around how to do it. Is there some db2 equivalent to inserted() from SQL Server?

My apologies for the weak question.

Upvotes: 0

Views: 4024

Answers (1)

Rashmi Pandit
Rashmi Pandit

Reputation: 23848

DB2 After Insert trigger:

CREATE TRIGGER NEW_REVIEW
 AFTER INSERT ON REVIEW
  REFERENCING NEW AS N_ROW
   FOR EACH ROW
    UPDATE PRODUCT SET Rating = (
      Select AVG(Rating) 
      from Review 
      where ProductName = N_ROW.ProductName
      )

After Update:

CREATE TRIGGER NEW_REVIEW
 AFTER UPDATE ON REVIEW
  REFERENCING NEW AS N_ROW
   FOR EACH ROW
    UPDATE PRODUCT SET Rating = (
      Select AVG(Rating) 
      from Review 
      where ProductName = N_ROW.ProductName
      )

After Delete:

CREATE TRIGGER NEW_REVIEW
 AFTER DELETE ON REVIEW
  REFERENCING OLD AS O_ROW
   FOR EACH ROW
    UPDATE PRODUCT SET Rating = (
      Select AVG(Rating) 
      from Review 
      where ProductName = O_ROW.ProductName
      )

Check out CREATE TRIGGER statement for additional info.

Upvotes: 4

Related Questions