Keep summary table in sync

Keep summary table in sync

Medium SQL Triggers 14 views
Explanation Complexity

Problem Statement

Maintain DailySales(day, amount). After insert into Orders, update DailySales for that date.

Input Format

SQL DDL

Output Format

Trigger created

Example

Orders, DailySales
Trigger created

Constraints

Use upsert style update+insert

Input / Output Format

Input Format
SQL DDL
Output Format
Trigger created
Constraints
Use upsert style update+insert

Examples

Input:
Orders, DailySales
Output:
Trigger created

Example Solution (Public)

SQL 
CREATE TRIGGER trg_daily_sales AFTER INSERT ON Orders FOR EACH ROW BEGIN UPDATE DailySales SET amount = amount + NEW.total_amount WHERE day = NEW.order_date; IF ROW_COUNT() = 0 THEN INSERT INTO DailySales(day, amount) VALUES (NEW.order_date, NEW.total_amount); END IF; END;

Official Solution Code

CREATE TRIGGER trg_daily_sales AFTER INSERT ON Orders FOR EACH ROW BEGIN UPDATE DailySales SET amount = amount + NEW.total_amount WHERE day = NEW.order_date; IF ROW_COUNT() = 0 THEN INSERT INTO DailySales(day, amount) VALUES (NEW.order_date, NEW.total_amount); END IF; END;
Please login to submit solutions.
Editor
Output

                                        

                                    

                                    

                                        

                                                                                    
Please login to submit solutions.