create materialized view MV1 build immediate refresh fast on commit as create materialized view log on <table> with sequence, rowId (...) including new values create table <table_name> ( DateMonth Date not null, DateYear Date not null, Name varchar(256) not null, Total integer not null); insert into <table_name> () (select ...); create trigger trigger after insert or insert of on <table> for each row declare begin end create materialized view GROUPBYMonthYearMuseum build immediate refresh FAST ON COMMIT --enable query rewrite as SELECT Month, Year, ticket_type , SUM(num_tickets) as NumTickets, SUM(revenue) as TotRevenue FROM museums_tickets mt, timedim t WHERE mt.id_time = t.id_time GROUP BY Month, Year, ticket_type; CREATE MATERIALIZED VIEW LOG ON museums_tickets WITH SEQUENCE, ROWID (id_time, ticket_type, num_tickets, Revenue) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW LOG ON TIMEDIM WITH SEQUENCE, ROWID (id_time, Month, Year) INCLUDING NEW VALUES; ---- Trigger Manually CREATE TABLE VM1 ( DateMonth DATE NOT NULL), DateYear INTEGER NOT NULL, Ticket_Type VARCHAR(20) NOT NULL, TOT_NumberOfTickets INTEGER, TOT_Revenue INTEGER); INSERT INTO VM1 (DateMonth, DateYear, Ticket_Type, TOT_NumberOfTickets, TOT_Revenue) (SELECT Month, Year, Ticket_Type, SUM(NumberOfTickets), SUM(Revenue) FROM museums_tickets mt, timedim t WHERE mt.id_time = t.id_time GROUP BY Month, Year, Ticket_Type); create TRIGGER TriggerForViewVM1 AFTER INSERT ON museums_tickets FOR EACH ROW DECLARE N NUMBER; VAR_DateMonth DATE; VAR_DateYear NUMBER; BEGIN
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter