Using the Materialized View 1. First, CALL REFRESH_MV_SALESBYMONTH.

The assignment reads for DBM 449 Advanced topics in database

"STEP 5: Using the Materialized View

1. First, CALL REFRESH_MV_SALESBYMONTH. This will make sure that the view is created, and currently up-to-date.

2. Run the SQL statement: SELECT * FROM MV_SALESBYMONTH. The output columns from your view should look similar to the following (use aliases to format the column headings) and you should have 18 rows in the result set.

YEAR     MONTH PRODUCT CO UNITS SOLD SALES TOTAL

Now we are going to add some data and update the view. Remember, we must manually run the stored procedure to update the view, as it will not (yet!) automatically itself.

1. To begin with, insert the following data into the SALES table—(207, 110016, 'SM-18277',1,8.95).

2. CALL the stored procedure to refresh the view. 

3. Query the view once again.

You should now see that the row for units sold in month 10 for SM-18277 has increased from 3 to 4 and total sales amount has gone from 26.85 to 35.80.

4. Delete the row you just added to the SALES table, and call the stored procedure to refresh the view, proving that things are up-to-date.

5. Create a Trigger on the SALES table so that any insert automatically fires off the stored procedure to update the view.

6. Test your trigger, by again inserting the following data into the SALES table—(207, 110016, 'SM-18277',1,8.95).

7. Query the view once again.

You should now see that the row for units sold in month 10 for SM-18277 has increased from 3 to 4 and total sales amount has gone from 26.85 to 35.80. "

REFRESH_MV_SALESBYMONTH is a stored procedure written as follows

DELIMITER $$

CREATE PROCEDURE REFRESH_MV_SALESBYMONTH()

BEGIN

DROP TABLE IF EXISTS `MV_SALESBYMONTH`;

CREATE TABLE `MV_SALESBYMONTH`(

SELECT TIME_YEAR AS 'YEAR', TIME_MONTH AS 'MONTH', PROD_CODE AS 'PRODUCT',

SUM(SALE_UNITS) AS 'UNITS SOLD', SUM(SALE_UNITS*SALE_PRICE) AS 'SALES TOTAL'

FROM TIME T, SALES S

WHERE S.TIME_ID = T.TIME_ID

GROUP BY TIME_YEAR, TIME_MONTH, PROD_CODE);

END $$

DELIMITER ;

I am struggling with step 5 second part numbers 5 and 6

I created the trigger with this code:

delimiter //

CREATE TRIGGER TRIG_SALES AFTER INSERT ON SALES

FOR EACH ROW

BEGIN

CALL dbm449lab6.REFRESH_MV_SALESBYMONTH();

END//

delimiter ;

but when i run the insert statement for part 6 i get an error that reads: SQL Error (1422): Explicit or implicit commit is not allowed in stored function or trigger.

I don't know what i am doing wrong or how to fix it. Can you help?