1: procedure events_delete_event (v_event_id IN integer)
2: IS
3: sql_stmt varchar(500);
4: i_group_id integer;
5:
6: -- get all the orders for this event
7: cursor c1 is
8: select distinct r.order_id
9: from events_prices p, events_registrations r
10: where p.event_id = v_event_id
11: and r.price_id = p.price_id;
12:
13: -- get all the organizer roles for this event
14: cursor c2 is
15: select role_id
16: from events_event_organizer_roles
17: where event_id = v_event_id;
18:
19: BEGIN
20: -- delete all the registrations/orders for this event
21: FOR e in c1 LOOP
22: delete from events_registrations
23: where order_id = e.order_id;
24:
25: delete from events_orders
26: where order_id = e.order_id;
27: END LOOP;
28:
29: -- delete the event prices for this event
30: delete from events_prices
31: where event_id = v_event_id;
32:
33: -- get the event's group_id
34: select group_id into i_group_id
35: from events_events
36: where event_id = v_event_id;
37:
38: -- delete the event's event fields
39: delete from events_event_fields
40: where event_id = v_event_id;
41:
42: -- delete the event's organizers and roles
43: FOR f in c2 LOOP
44: -- delete the organizers with this role
45: delete from events_organizers_map
46: where role_id = f.role_id;
47:
48: -- delete this role
49: delete from events_event_organizer_roles
50: where role_id = f.role_id;
51: END LOOP;
52:
53: -- drop the event_n_info table
54: sql_stmt := 'drop table event_' || v_event_id || '_info';
55: EXECUTE IMMEDIATE sql_stmt;
56:
57: -- delete the event
58: delete from events_events
59: where event_id = v_event_id;
60:
61: -- delete the event's user group
62: delete_user_group(i_group_id);
63:
64: END events_delete_event;
|