/**********************************************
.: ID :.
*/
create exception e_style_level 'Livello di stile non valido.';
create exception e_stile_figlio 'Specificare lo stile del primo post.';
create exception E_FEED_CANTMOVE 'Il feed non puo'' essere spostato';
create exception E_FORUM_CANTMOVE 'Il forum non puo'' essere spostato';
create exception e_forum_style 'Impossibile modificare lo stile del forum: stili per topic non trovati.';
create generator g_emoticon;
create generator g_file;
create generator g_section;
create generator g_group;
create generator g_user;
create generator g_user_group;
create generator g_group_section;
create generator g_user_section;
create generator g_group_forum_access;
create generator g_user_forum_access;
create generator g_forum;
create generator g_topic;
create generator g_post;
create generator g_post_attach;
create generator g_user_forum_ignore;
create generator g_user_forum_join;
create generator g_user_topic;
create generator g_user_topic_access;
create generator g_match_cup;
create generator g_player;
create generator g_team;
create generator g_team_player;
create generator g_match;
create generator g_match_player;
create generator g_server;
create generator g_forum_topic_style;
create generator g_user_feed;
create generator g_user_forum_bmk;
create generator g_topic_hit;
create generator g_user_login;
create generator g_banned_ip;
create generator g_machine;
create generator g_ts_server;
create trigger t_id_ts_server for t_a_ts_server active before
insert as
begin
if (new.i_ts_server_id is null) then
new.i_ts_server_id =
gen_id(g_ts_server, 1);
end ^
create trigger t_id_machine for t_a_machine active before insert as
begin
if (new.i_machine_id is null) then
new.i_machine_id =
gen_id(g_machine, 1);
end ^
create trigger t_id_banned_ip for t_a_banned_ip active before insert as
begin
if (new.i_banned_ip_id is null) then
new.i_banned_ip_id = gen_id(g_banned_ip, 1);
end ^
create trigger t_id_user_forum_bmk for t_s_user_forum_bmk active before insert as
begin
if (new.i_user_forum_bmk_id is null) then
new.i_user_forum_bmk_id = gen_id(g_user_forum_bmk, 1);
end ^
create trigger t_id_user_feed for t_s_user_feed active before insert as
begin
if (new.i_user_feed_id is null) then
new.i_user_feed_id = gen_id(g_user_feed, 1);
end ^
create trigger t_id_user_login for t_s_user_login active before insert as
begin
if (new.i_user_login_id is null) then
new.i_user_login_id = gen_id(g_user_login, 1);
end ^
create trigger t_id_emoticon for t_r_emoticon active before insert as
begin
if (new.i_emoticon_id is null) then
new.i_emoticon_id = gen_id(g_emoticon, 1);
end ^
CREATE TRIGGER t_emo_order_insert FOR t_r_emoticon active BEFORE INSERT
as
DECLARE i_order INTEGER;
begin
SELECT MAX(i_order)
FROM t_r_emoticon
INTO :i_order;
if (i_order is null) then
new.i_order = 1;
else
new.i_order = i_order + 1;
END ^
create trigger t_id_topic_hit for t_s_topic_hit active before insert as
begin
if (new.i_topic_hit_id is null) then
new.i_topic_hit_id = gen_id(g_topic_hit, 1);
end ^
create trigger t_id_file for t_a_file active before insert as
begin
if (new.i_file_id is null) then
new.i_file_id = gen_id(g_file, 1);
end ^
create trigger t_id_server for t_a_server active before insert as
begin
if (new.i_server_id is null) then
new.i_server_id = gen_id(g_server, 1);
end ^
create trigger t_id_section for t_a_section active before insert as
begin
if (new.i_section_id is null) then
new.i_section_id = gen_id(g_section, 1);
end ^
create trigger t_id_group for t_a_group active before insert as
begin
if (new.i_group_id is null) then
new.i_group_id = gen_id(g_group, 1);
end ^
create trigger t_id_user for t_a_user active before insert as
begin
if (new.i_user_id is null) then
new.i_user_id = gen_id(g_user, 1);
end ^
create trigger t_id_user_group for t_p_user_group active before insert
as
begin
if (new.i_user_group_id is null) then
new.i_user_group_id = gen_id(g_user_group, 1);
end ^
create trigger t_id_forum_topic_style for t_p_forum_topic_style active before
insert as
begin
if (new.i_forum_topic_style_id is null) then
new.i_forum_topic_style_id = gen_id(g_forum_topic_style, 1);
end ^
create trigger t_id_group_section for t_p_group_section active before insert
as
begin
if (new.i_group_section_id is null) then
new.i_group_section_id = gen_id(g_group_section, 1);
end ^
create trigger t_id_user_section for t_p_user_section active before insert
as
begin
if (new.i_user_section_id is null) then
new.i_user_section_id = gen_id(g_user_section, 1);
end ^
create trigger t_id_group_forum_access for t_p_group_forum_access active
before insert as
begin
if (new.i_group_forum_access_id is null) then
new.i_group_forum_access_id = gen_id(g_group_forum_access, 1);
end ^
create trigger t_id_user_forum_access for t_p_user_forum_access active before
insert as
begin
if (new.i_user_forum_access_id is null) then
new.i_user_forum_access_id = gen_id(g_user_forum_access, 1);
end ^
create trigger t_id_forum for t_a_forum active before insert as
begin
if (new.i_forum_id is null) then
new.i_forum_id = gen_id(g_forum, 1);
end ^
create trigger t_id_post for t_s_post active before insert as
begin
if (new.i_post_id is null) then
new.i_post_id = gen_id(g_post, 1);
end ^
create trigger t_id_topic for t_s_topic active before insert as
begin
if (new.i_topic_id is null) then
new.i_topic_id = gen_id(g_topic, 1);
end ^
create trigger t_id_post_attach for t_s_post_attach active before insert as
begin
if (new.i_post_attach_id is null) then
new.i_post_attach_id = gen_id(g_post_attach, 1);
end ^
create trigger t_id_user_forum_ignore for t_p_user_forum_ignore active before
insert as
begin
if (new.i_user_forum_ignore_id is null) then
new.i_user_forum_ignore_id = gen_id(g_user_forum_ignore, 1);
end ^
create trigger t_id_user_forum_join for t_p_user_forum_join active before
insert as
begin
if (new.i_user_forum_join_id is null) then
new.i_user_forum_join_id = gen_id(g_user_forum_join, 1);
end ^
create trigger t_id_user_topic for t_p_user_topic active before insert
as
begin
if (new.i_user_topic_id is null) then
new.i_user_topic_id = gen_id(g_user_topic, 1);
end ^
create trigger t_id_user_topic_access for t_p_user_topic_access active before
insert as
begin
if (new.i_user_topic_access_id is null) then
new.i_user_topic_access_id = gen_id(g_user_topic_access, 1);
end ^
create trigger t_id_match_cup for t_r_match_cup active before insert as
begin
if (new.i_match_cup_id is null) then
new.i_match_cup_id = gen_id(g_match_cup, 1);
end ^
create trigger t_id_player for t_p_player active before insert as
begin
if (new.i_player_id is null) then
new.i_player_id = gen_id(g_player, 1);
end ^
create trigger t_id_team for t_a_team active before insert as
begin
if (new.i_team_id is null) then
new.i_team_id = gen_id(g_team, 1);
end ^
create trigger t_id_team_player for t_p_team_player active before insert as
begin
if (new.i_team_player_id is null) then
new.i_team_player_id = gen_id(g_team_player, 1);
end ^
create trigger t_id_match for t_a_match active before insert as
begin
if (new.i_match_id is null) then
new.i_match_id = gen_id(g_match, 1);
end ^
create trigger t_id_match_player for t_p_match_player active before insert
as
begin
if (new.i_match_player_id is null) then
new.i_match_player_id = gen_id(g_match_player, 1);
end ^
/*
.: TRIGGER :.
*/
-- FORUM
create procedure user_pm(i_user_id integer)
returns(i_forum_id integer)
as
begin
end ^
create procedure forum_delete_rec(i_forum_id integer)
as begin
end ^
create procedure user_gb(i_user_id integer)
returns(i_topic_id integer)
as begin
end ^
create procedure post_count_inc(i_post_id integer)
as begin
end ^
CREATE TRIGGER t_forum_order_insert FOR t_a_forum active BEFORE INSERT
as
DECLARE i_order INTEGER;
begin
SELECT MAX(i_order)
FROM t_a_forum
WHERE i_parent_forum_id = new.i_parent_forum_id
or (new.i_parent_forum_id is null and i_parent_forum_id is null)
INTO :i_order;
if (i_order is null) then
new.i_order = 1;
else
new.i_order = i_order + 1;
END ^
CREATE TRIGGER t_crea_diritti_forum for t_a_forum active AFTER INSERT
as
declare ugod integer;
declare ggods integer;
declare b_pm char(1);
begin
select t_value from t_e_param where t_param = 'G_GODS' into :ggods;
select t_value from t_e_param where t_param = 'U_GOD' into :ugod;
select b_pm from forum_is_pm(new.i_forum_id)
into :b_pm;
-- copio i diritti del padre
if (new.i_parent_forum_id is null) then
insert into t_p_group_forum_access(i_group_id, i_forum_id)
select i_group_id, new.i_forum_id
from t_a_group
where i_group_id != :ggods;
else begin
insert into t_p_group_forum_access(i_group_id, i_forum_id, b_r, b_w, b_rw)
select i_group_id, new.i_forum_id, b_r, b_w, b_rw
from t_p_group_forum_access
where i_forum_id = new.i_parent_forum_id
and i_group_id != :ggods;
insert into
t_p_user_forum_access(i_user_id, i_forum_id, b_r, b_w, b_rw, b_mod)
select i_user_id, new.i_forum_id, b_r, b_w, b_rw, b_mod
from t_p_user_forum_access
where i_forum_id = new.i_parent_forum_id
and i_user_id != :ugod;
end
-- se mio padre o nonno e' PM, i diritti sono stati impostati altrove
-- altrimenti i god fanno tutto e god e moderatore
if (:b_pm = '0') then begin
insert into t_p_group_forum_access(i_group_id, i_forum_id, b_r, b_w, b_rw)
values(:ggods, new.i_forum_id, '1', '1', '1');
insert into
t_p_user_forum_access(i_user_id, i_forum_id, b_r, b_w, b_rw, b_mod)
values(:ugod, new.i_forum_id, '1', '1', '1', '1');
end else
insert into t_p_group_forum_access(i_group_id, i_forum_id)
values(:ggods, new.i_forum_id);
END ^
-- IGNORE
CREATE TRIGGER t_ignore_reset FOR t_p_user_forum_ignore active after insert position 10
as begin
update t_p_user_topic set b_new = '0'
where
b_new =
'1'
and i_topic_id IN (select i_topic_id from t_s_topic where i_forum_id = new.i_forum_id)
and i_user_id =
new.i_user_id
and b_notify = '0';
end ^
create trigger t_ignore_def_forum for t_a_forum active after insert position 50
as
begin
if (new.b_show_feed = '0') then
create trigger t_ignore_upd_forum for t_a_forum active after update position 50
as
begin
if (new.b_show_feed = '0' and old.b_show_feed = '1') then begin
delete from t_p_user_forum_ignore where i_forum_id = new.i_forum_id;
insert into t_p_user_forum_ignore(i_user_id, i_forum_id)create trigger t_ignore_def_user for t_a_user active after insert position 100
as
begin
insert into t_p_user_forum_ignore(i_user_id, i_forum_id)
/*
insert into t_p_user_forum_ignore(i_user_id, i_forum_id)
select u.i_user_id, f.i_forum_id
from t_a_user u cross join t_a_forum f
where f.b_show_feed = '0' and not exists(select * from t_p_user_forum_ignore a
where a.i_user_id = u.i_user_id and a.i_forum_id = f.i_forum_id)
update t_p_user_topic t set t.b_new = '0'
where t.i_user_id = t.i_user_id
and t.b_new = '1'
and t.b_notify = '0'
and t.i_topic_id in (select i_topic_id from
user_topic_rights_t(t.i_user_id, t.i_topic_id) where b_r = '0');
create trigger t_unset_new_1 for t_p_user_group active after delete
as begin
execute procedure unset_new_u old.i_user_id;
end ^
-- da interfaccia, sui forum posso fare solo update: se tolgo il diritto di lettura, devo aggiornare
-- i flag degli utenti del gruppo
create trigger t_unset_new_2 for t_p_group_forum_access active after update
as begin
if (new.b_r = '0' and old.b_r = '1') then execute procedure
unset_new_g old.i_group_id;
end ^
create trigger t_unset_new_3 for t_p_user_forum_access active after update
as begin
if (new.b_r = '0' and old.b_r = '1') then execute procedure
unset_new_uf old.i_user_id, old.i_forum_id;
end ^
create trigger t_unset_new_4 for t_p_user_forum_access active after delete
as
declare b_r char(1);
begin
select b_r from user_group_forum_rights_f(old.i_user_id, old.i_forum_id) into :b_r;
if (:b_r = '0' and old.b_r = '1') then execute procedure
unset_new_uf old.i_user_id, old.i_forum_id;
end ^
-- per i diritti topic, vale il discorso dei diritti
utente sui forum
create trigger t_unset_new_5 for t_p_user_topic_access active after update
as begin
if (new.b_r = '0' and old.b_r = '1') then execute procedure
unset_new_ut old.i_user_id, old.i_topic_id;
end ^
create trigger t_unset_new_6 for t_p_user_topic_access active after delete
as
declare b_r char(1);
begin
select b_r from topic_user_rights(old.i_topic_id) where i_user_id = old.i_user_id into :b_r;
if (:b_r = '0' and old.b_r = '1') then execute procedure
unset_new_ut old.i_user_id, old.i_topic_id;
end ^
-- se dico che il topic non è piu' leggibile, devo
resettare i diritti a tutti quelli che non possono più leggerlo
create trigger t_unset_new_7 for t_s_topic active after update
-- TOPIC
CREATE TRIGGER t_topic_count_inc for t_s_topic active AFTER INSERT
as begin
update t_a_forum set i_topic_count = i_topic_count + 1
where i_forum_id = new.i_forum_id;
-- spazio nella user-topic
-- qua metto tutto a 0, ci pensa poi il post a
fare un update sensato del flag
INSERT INTO t_p_user_topic (i_topic_id, i_user_id, b_new)
SELECT new.i_topic_id, i_user_id , '0'
FROM t_a_user;
END ^
CREATE TRIGGER t_topic_count_dec for t_s_topic active AFTER DELETE
as begin
update t_a_forum set i_topic_count = i_topic_count - 1
where i_forum_id = old.i_forum_id;
END ^
-- POST
CREATE TRIGGER t_post_count_inc for t_s_post active AFTER INSERT
as
declare i_forum integer;
begin
select i_forum_id from t_s_topic where i_topic_id = new.i_topic_id into :i_forum;
-- update user count
update t_a_user set i_post_count =
i_post_count + 1
where i_user_id = new.i_user_id;
execute procedure post_count_inc new.i_post_id;
/* aggiorno il bit "new" per il topic e tutti
gli utenti (tranne il poster) che
lo possono
leggere
non hanno il forum in ignore
oppure
hanno il notify sul topic stesso
*/
update t_p_user_topic t
set t.b_new = '1'
where
t.i_topic_id = new.i_topic_id
and
(t.b_notify = '1'
or
(t.i_user_id != new.i_user_id
and ('1' = (select b_r from user_topic_rights_t(t.i_user_id, new.i_topic_id)))
and (not exists(select * from t_p_user_forum_ignore where i_forum_id = :i_forum and i_user_id = t.i_user_id))
));
END ^
CREATE TRIGGER t_post_edit for t_s_post active BEFORE UPDATE
as
declare r_insert timestamp;
declare i_last_post integer;
declare r_last_post timestamp;
begin
new.r_edit_last = current_timestamp;
new.i_edit_count = new.i_edit_count + 1;
if (new.i_edit_user_id is null) then new.i_edit_user_id = new.i_user_id;
if (new.i_topic_id != old.i_topic_id) then begin
-- gli metto lo stile del topic di destinazione
-- update conteggi nell'after update
select t_post_style from t_e_style where t_style = (select t_style from t_s_topic where i_topic_id = new.i_topic_id)
into new.t_style;
end else if (new.t_post_source != old.t_post_source or new.t_title != old.t_title or new.t_description != old.t_description or new.t_style != old.t_style) then begin
-- se e' il primo post, cambio stile e titolo topic di conseguenza
select min(r_insert) from t_s_post where i_topic_id = new.i_topic_id into :r_insert;
if (new.r_insert = :r_insert) then begin
update t_s_topic set
t_title = new.t_title
,t_description = new.t_description
,t_style = (select min(st.t_style) from t_e_style st where st.t_post_style = new.t_style)
where i_topic_id = new.i_topic_id;
update t_s_post set t_style = new.t_style
where i_topic_id = new.i_topic_id and i_post_id != new.i_post_id;
end
end
END ^
CREATE TRIGGER t_post_move for t_s_post active AFTER UPDATE
as begin
if (new.i_topic_id != old.i_topic_id) then begin
-- update conteggi
execute procedure post_count_dec old.i_topic_id, old.i_post_id;
execute procedure post_count_inc new.i_post_id;
end
end ^
create trigger t_post_mod_att for t_s_post active after update position 10
as
declare i_forum_id integer;
declare i_user_id integer;
declare t_url varchar(4096);
begin
if (new.b_mod_att = '1' and old.b_mod_att = '0') then begin
select i_forum_id from t_s_topic where i_topic_id = new.i_topic_id into :i_forum_id;
select t_url from forum_link(:i_forum_id, new.i_topic_id, NULL, new.i_post_id) into :t_url;
t_url = '[url][var]pathl(PROTO.$_SERVER["HTTP_HOST"], VDIR, "go")[/var]/' || t_url || '[/url]';
for select uf.i_user_id
from t_p_user_forum_access uf
inner join t_s_topic t on (uf.i_forum_id = t.i_forum_id)
where
t.i_topic_id = new.i_topic_id
and uf.b_mod = '1'
into :i_user_id do begin
execute
procedure send_pm(new.i_mod_att_user_id, :i_user_id, 'Nuova segnalazione',
'Messaggio generato automaticamente dal sistema', 'Il mittente ha appena
segnalato un post alla tua attenzione: ' || :t_url);
end
end
end^
-- disattivo
create trigger t_post_last_read_upd for t_s_post inactive before delete
as
declare i_last_post integer;
declare r_last_post timestamp;
begin
select max(i_post_id)
from t_s_post
where r_insert = (select max(r_insert) from t_s_post where i_topic_id = old.i_topic_id and i_post_id != old.i_post_id)
into :i_last_post;
select r_insert from t_s_post where i_post_id
= :i_last_post
into :r_last_post;
update t_p_user_topic set i_last_read_post_id = :i_last_post
where i_last_read_post_id
= old.i_post_id;
end ^
create procedure post_count_dec(i_topic_id integer, i_post_id integer)
as
CREATE TRIGGER t_post_count_dec for t_s_post active AFTER DELETE
as
declare i_forum integer;
declare i_forum_gb integer;
declare i_forum_pm integer;
begin
select i_forum_id from t_s_topic where i_topic_id = old.i_topic_id
into :i_forum;
/* abbasso il post count sull'utente */
update t_a_user set i_post_count = i_post_count - 1
where i_user_id = old.i_user_id;
select t_value from t_e_param where t_param = 'F_GB' into
:i_forum_gb;
select t_value from t_e_param where t_param = 'F_PM' into :i_forum_pm;
if (i_forum not in(i_forum_gb, i_forum_pm) and (not
exists(select * from t_s_post where i_topic_id = old.i_topic_id))) then begin
update t_a_forum set i_post_count = i_post_count - 1
where i_forum_id = :i_forum;
delete from t_s_topic
where i_topic_id = old.i_topic_id;
end else begin
execute procedure post_count_dec
old.i_topic_id, old.i_post_id;
if (not exists(select * from t_s_post where i_topic_id = old.i_topic_id and i_post_id != old.i_post_id)) then
delete from t_s_topic where i_topic_id = old.i_topic_id;
end
END ^
-- UTENTE
create trigger t_user_logged FOR t_a_user active BEFORE UPDATE position 20
as begin
if (old.b_logged != new.b_logged and new.b_logged='1') then
new.r_last_login = current_timestamp;
end ^
create TRIGGER t_user_topic FOR t_a_user active AFTER INSERT position 1
as begin
INSERT INTO t_p_user_topic (i_topic_id, i_user_id)
SELECT i_topic_id, new.i_user_id
FROM t_s_topic;
-- subito registrato
INSERT INTO t_p_user_group(i_user_id, i_group_id)
SELECT new.i_user_id, t_value
FROM t_e_param
WHERE t_param = 'G_REGS';
END ^
create trigger t_user_reg for t_a_user active after insert position 150
as
declare i_user_to_id integer;
declare i_ibox integer;
begin
select t_value from t_e_param where t_param = 'U_PM_REG' into :i_user_to_id;
execute procedure send_pm(new.i_user_id,
:i_user_to_id, 'Nuovo utente', 'Messaggio generato automaticamente dal
sistema', 'Il mittente si è appena registrato.');
end ^
create trigger t_user_group_add for t_p_user_group active after
insert position 20
as
declare i_god integer;
declare i_regs integer;
declare t_group varchar(255);
declare t_description varchar(255);
declare text varchar(8192);
begin
select t_value from t_e_param where t_param = 'G_REGS' into :i_regs;
if (new.i_group_id != :i_regs) then begin
select t_value from t_e_param where t_param = 'U_GOD' into :i_god;
select t_name, t_description from t_a_group where i_group_id = new.i_group_id into :t_group, :t_description;
text = 'Sei appena stato inserito nel gruppo [b]' || :t_group || '[/b] - ' || :t_description;
if (new.b_mod = '1') then text = text || ' e ne sei uno dei gestori';
execute procedure
send_pm(:i_god, new.i_user_id, 'Inserimento in un gruppo di accesso',
'Messaggio generato automaticamente dal sistema', :text);
end
end^
create trigger t_user_group_upd for t_p_user_group active after update position 20
as
declare i_god integer;
declare t_group varchar(255);
declare t_description varchar(255);
begin
select t_value from t_e_param where t_param = 'U_GOD' into :i_god;
select t_name, t_description from t_a_group where i_group_id = new.i_group_id into :t_group, :t_description;
if (new.b_mod = '1' and old.b_mod = '0') then begin
execute procedure send_pm(:i_god, new.i_user_id, 'Gestione di un gruppo di accesso', 'Messaggio generato automaticamente dal sistema', 'Sei stato nominato gestore del gruppo [b]' || :t_group || '[/b] - ' || :t_description);end else if (new.b_mod = '0' and old.b_mod = '1') then begin
execute procedure
send_pm(:i_god, new.i_user_id, 'Gestione di un gruppo di accesso', 'Messaggio
generato automaticamente dal sistema', 'Sei stato rimosso
dai gestori del gruppo [b]' || :t_group || '[/b] - ' ||
:t_description);
end
end^
create trigger t_user_group_del for t_p_user_group active before
delete position 20
as
declare i_god integer;
declare i_regs integer;
declare t_group varchar(255);
declare t_description varchar(255);
begin
select t_value from t_e_param where t_param = 'G_REGS' into :i_regs;
if (old.i_group_id != :i_regs) then begin
select t_value from t_e_param where t_param = 'U_GOD' into :i_god;
select t_name, t_description from t_a_group where i_group_id = old.i_group_id into :t_group, :t_description;
execute procedure
send_pm(:i_god, old.i_user_id, 'Rimozione da un gruppo di accesso', 'Messaggio
generato automaticamente dal sistema', 'Sei appena stato rimosso dal gruppo
[b]' || :t_group || '[/b] - ' || :t_description);
end
end^
create trigger t_user_forum_mod_upd for t_p_user_forum_access active after update position 10
as
declare i_god integer;
declare t_url varchar(4096);
begin
select t_url from forum_link(new.i_forum_id, NULL, NULL, NULL) into :t_url;
select t_value from t_e_param where t_param = 'U_GOD' into :i_god;
t_url = '[url][var]pathl(PROTO.$_SERVER["HTTP_HOST"], VDIR, "go")[/var]/' || t_url || '[/url]';
if (new.b_mod = '1' and old.b_mod = '0') then begin
execute
procedure send_pm(:i_god, new.i_user_id, 'Sei diventato moderatore',
'Messaggio generato automaticamente dal sistema', 'Sei stato nominato
moderatore del forum ' || :t_url);
end else if (new.b_mod = '0' and old.b_mod = '1') then begin
execute
procedure send_pm(:i_god, new.i_user_id, 'Non sei più moderatore',
'Messaggio generato automaticamente dal sistema', 'Non sei più
moderatore del forum ' || :t_url);
end
end^
create trigger t_user_forum_mod_del for t_p_user_forum_access active after delete position 10
as
declare i_god integer;
declare t_url varchar(4096);
begin
if (old.b_mod = '0') then beginselect t_url from forum_link(old.i_forum_id, NULL, NULL, NULL) into :t_url;
select t_value from t_e_param where t_param = 'U_GOD' into :i_god;
t_url = '[url][var]pathl(PROTO.$_SERVER["HTTP_HOST"], VDIR, "go")[/var]/' || t_url || '[/url]';
execute
procedure send_pm(:i_god, old.i_user_id, 'Non sei più moderatore',
'Messaggio generato automaticamente dal sistema', 'Non sei più
moderatore del forum ' || :t_url);
end
end^
-- PRIVATE MESSAGES
/* ogni utente ha un forum sotto F_PM
My box
Inbox (io modero, tutti scrivono)
Outbox (io modero)
*/
create trigger t_user_pm for t_a_user active after insert position 3
as
declare i_pm_forum_id integer;
begin
execute procedure user_pm new.i_user_id
returning_values :i_pm_forum_id;
update t_a_user set i_pm_forum_id = :i_pm_forum_id
where i_user_id = new.i_user_id;
end ^
create trigger t_user_pm_del for t_a_user active after delete
as
begin
if (old.i_pm_forum_id is not null) then
execute procedure forum_delete_rec old.i_pm_forum_id;
end ^
-- GUESTBOOK
create trigger t_user_gb for t_a_user active after insert position 2
as
declare i_gb integer;
begin
execute procedure user_gb new.i_user_id
returning_values :i_gb;
update t_a_user set i_guestbook_topic_id = :i_gb
where i_user_id = new.i_user_id;
end ^
create trigger t_user_gb_del for t_a_user active before delete
as
declare i_gb integer;
begin
update t_a_user set i_guestbook_topic_id = NULL where i_user_id = old.i_user_id;
delete from t_s_topic where i_topic_id = old.i_guestbook_topic_id ;
end ^
-- feed e forum bmks
CREATE TRIGGER t_user_feed_order_insert FOR t_s_user_feed active BEFORE INSERT
as
DECLARE i_order INTEGER;
begin
SELECT MAX(i_order)
FROM t_s_user_feed
WHERE i_user_id = new.i_user_id
INTO :i_order;
if (i_order is null) then
new.i_order = 1;
else
new.i_order = i_order + 1;
END ^
CREATE TRIGGER t_user_feed_order_del FOR t_s_user_feed active AFTER DELETE
as
DECLARE i_order INTEGER;
begin
update t_s_user_feed set i_order = i_order - 1
where i_user_id = old.i_user_id
and i_order >
old.i_order;
END ^
CREATE TRIGGER t_user_forum_bmk_order_insert FOR t_s_user_forum_bmk active BEFORE INSERT
as
DECLARE i_order INTEGER;
begin
SELECT MAX(i_order)
FROM t_s_user_forum_bmk
WHERE i_user_id = new.i_user_id
INTO :i_order;
if (i_order is null) then
new.i_order = 1;
else
new.i_order = i_order + 1;
END ^
CREATE TRIGGER t_user_forum_bmk_order_del FOR t_s_user_forum_bmk active AFTER DELETE
as
DECLARE i_order INTEGER;
begin
update t_s_user_forum_bmk
set i_order = i_order - 1
where i_user_id = old.i_user_id
and i_order >
old.i_order;
END ^
-- FILE
create trigger t_user_avt_del for t_a_file active before delete position
0
as
begin
update t_a_user set i_avatar_file_id = null
where i_avatar_file_id = old.i_file_id;
end ^
-- SEZIONI
CREATE TRIGGER t_crea_diritti_sezione FOR t_a_section active AFTER
INSERT
as begin
INSERT INTO t_p_group_section(i_section_id, i_group_id)
SELECT new.i_section_id, i_group_id
FROM t_a_group;
END ^
-- GRUPPI
CREATE TRIGGER t_crea_diritti_gruppo FOR t_a_group active AFTER INSERT
as begin
INSERT INTO t_p_group_section(i_section_id, i_group_id)
SELECT i_section_id, new.i_group_id
FROM t_a_section;
INSERT INTO t_p_group_forum_access(i_forum_id, i_group_id)
SELECT i_forum_id, new.i_group_id
FROM t_a_forum;
END ^
-- TEAM
CREATE TRIGGER t_crea_team_match FOR t_a_match active AFTER INSERT
as begin
insert into t_p_match_player(i_match_id, i_player_id, t_match_player_status)
select new.i_match_id, p.i_player_id, '?'
from t_p_team_player mn
inner join t_p_player p on (mn.i_player_id = p.i_player_id)
where
new.i_team_id = mn.i_team_id
and p.b_active = '1';
END ^
CREATE TRIGGER t_elimina_wa FOR t_p_player active BEFORE DELETE
as begin
update t_a_team
set i_war_arranger_id = NULL
where i_war_arranger_id = old.i_user_id;
END ^
CREATE TRIGGER t_elimina_wa_t FOR t_p_team_player active BEFORE DELETE
as begin
update t_a_team
set i_war_arranger_id = NULL
where i_team_id = old.i_team_id
and i_war_arranger_id = (select i_user_id from t_p_player where i_player_id = old.i_player_id)
;
END ^
-- STILE
create trigger t_stile_dati for t_e_style active before insert or
update
as begin
if (new.t_style_level = 'F') then
new.t_style_type = NULL;
if (new.t_style_level != 'T') then
new.t_post_style = NULL;
else if (new.t_post_style IS NULL) then
exception e_stile_figlio;
if (new.t_post_style is not null and not exists(select * from t_e_style where t_style = new.t_post_style and t_style_level = 'P')) then
exception e_style_level;
end ^
create trigger t_stile_topic_check for t_p_forum_topic_style active before
insert or update
as begin
if (not exists(select * from t_e_style where t_style = new.t_forum_style and t_style_level = 'F')) then
exception e_style_level;
if (not exists(select * from t_e_style where t_style = new.t_topic_style and t_style_level = 'T')) then
exception e_style_level;
end ^
CREATE TRIGGER t_aggiorna_stile FOR t_e_style active BEFORE UPDATE
as begin
new.t_style_level = old.t_style_level;
END ^
CREATE TRIGGER t_forum_style_check FOR t_a_forum active BEFORE INSERT or UPDATE position 5
as
declare t_style_level char(1);
begin
select t_style_level from t_e_style where t_style = new.t_style
into :t_style_level;
if ('F' != :t_style_level) then
exception e_style_level;
END ^
CREATE TRIGGER t_topic_style_check FOR t_s_topic active BEFORE INSERT position 5
as
declare t_style_level char(1);
begin
select t_style_level from t_e_style where t_style = new.t_style
into :t_style_level;
if ('T' != :t_style_level) then
exception e_style_level;
END ^
CREATE TRIGGER t_post_style_check FOR t_s_post active BEFORE INSERT position 5
as
declare t_style_level char(1);
begin
select t_style_level from t_e_style where t_style = new.t_style
into :t_style_level;
if ('P' != :t_style_level) then
exception e_style_level;
END ^
create trigger t_aggiorna_stile_topic_posts for t_a_forum active
after update position 10
as
declare old_t_topic_style char(17);
declare new_t_topic_style char(17);
declare old_t_topic_type char(5);
declare i_topic_id integer;
declare i_first_post_id integer;
begin
if (old.t_style != new.t_style) then begin
for select
mn.t_topic_style, s.t_style_type
from t_p_forum_topic_style mn
inner join t_e_style s on (mn.t_topic_style = s.t_style)
where mn.t_forum_style = old.t_style
and exists (select * from t_s_topic where i_forum_id =
new.i_forum_id and t_style = mn.t_topic_style)
into :old_t_topic_style, :old_t_topic_type
do begin
new_t_topic_style = null;
select mn.t_topic_style
from t_p_forum_topic_style mn
inner join t_e_style s on (mn.t_topic_style = s.t_style)
where mn.t_forum_style = new.t_style and s.t_style_type = :old_t_topic_type
into :new_t_topic_style;
if (new_t_topic_style is null) then exception E_FORUM_STYLE;
for select i_topic_id
from t_s_topic
where i_forum_id = new.i_forum_id and t_style = :old_t_topic_style
into :i_topic_id
do begin
select i_post_id
from t_s_post
where i_topic_id = :i_topic_id
and r_insert = (select min(r_insert) from t_s_post where i_topic_id = :i_topic_id)
into :i_first_post_id;
update t_s_post set t_style = (select t_post_style from t_e_style where t_style = :new_t_topic_style)
where i_post_id = :i_first_post_id;
end
end
end
end
-- SERVER
(dropped)
create trigger t_server_change_state for t_a_server inactive
after update position 10
as
declare i_god integer;
declare t_svrname varchar(1024);
begin
select t_value from t_e_param where t_param = 'U_GOD' into :i_god;
select t_name from v_server where i_server_id = new.i_server_id into :t_svrname;
if ((old.b_up is null or old.b_up = '1') and new.b_up = '0') then begin
execute procedure
send_pm(:i_god, new.i_user_admin_id, 'Server down', 'Messaggio generato
automaticamente dal sistema', 'Il server ' || :t_svrname || ' `
caduto.');
end^
create trigger t_server_qstat for t_a_server active before insert or update position 20
as
begin
if (new.t_qstat is null) then begin
new.i_ping = null;
new.i_players = null;
new.i_max_players = null;
new.b_up = null;
new.r_update = null;
end
end ^