SF Enterprise / MySQL trigger issue |
Post Reply 
|
| Author | |
Dan B 
Senior Member 
Joined: 09 February 2005 Location: United States Status: Offline Points: 105 |
 Post Options
 Thanks(0)
 Quote Reply
 Topic: SF Enterprise / MySQL trigger issuePosted: 04 July 2010 at 4:11pm |
|
Roberto, I just recently changed my SF enterprise to allow custom filter settings per domain and now if I try to do anything with the spamfilter table records example adding new email in the authorized table I get the following MySQL Error: #1242 Subquery returns more than 1 row. I narrowed this down to the triggers on each table querying the following:
select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.wl_authorizedtoemailsusedefaults = 1)
I get back my list of 22 domains in the server. Here is the trigger on this table.
CREATE TRIGGER `spamfilter`.`trwl_authorizedtoemails_ai` AFTER INSERT ON `spamfilter`.`tblwl_authorizedtoemails`
FOR EACH ROW begin if not exists (select * from tblreloadtableinfo r where (r.domainid = new.domainid) and (r.tablename = 'tblwl_authorizedtoemails' )) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values (new.domainid, 1, 'tblwl_authorizedtoemails'); end if; begin declare tmp_id int; set tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.wl_authorizedtoemailsusedefaults = 1)); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblwl_authorizedtoemails')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblwl_authorizedtoemails'); end if; end; end; Thanks,
Dan B |
|
|
Dan B
|
|
 |
|
|
LogSat
Admin Group 
Joined: 25 January 2005 Location: United States Status: Offline Points: 4104 |
Post Options
Thanks(0)
Quote Reply
Posted: 04 July 2010 at 5:43pm |
|
Dan,
It’s possible that, if you migrated/restored/re-created the SpamFilter database in the past, and/or migrated the SpamFilter installation somewhere else, this created a mixup with the actual patches applied to the database and with what SpamFilter thinks was applied. Before perfoming additional troubleshooting/logfile debugging, I’d recommend having SpamFilter re-apply all the database patches that are needed. This may cause errors to be logged when the scripts are trying to add database fields that exist already, but these errors can be ignored. To proceed, please: • stop SpamFilter • then find and remove the line starting with “DBPatchesApplied=” in the SpamFilter.ini file • and then restart SpamFilter. Please let me know if this does not solve the issue. If it doesn't, can you please email and zip me SpamFilter's activity logfile for a day this happened? If the zip file is larger than 8MB, please contact us via email so I can provide you with the FTP credential you can use to upload us the file (unless you have it already, or you wish for us to download it from your website) |
|
|
|
|
Dan B
Senior Member
Joined: 09 February 2005 Location: United States Status: Offline Points: 105 |
Post Options
Thanks(0)
Quote Reply
Posted: 04 July 2010 at 6:01pm |
|
Roberto,
I have already tried the DBPatchesApplied option before I posted the above issue. It doesn't help. I have the latest .832 release. I will send todays log that has the DBPatch info in it.
Thanks,
Dan
|
|
|
Dan B
|
|
|
|
|
Dan B
Senior Member
Joined: 09 February 2005 Location: United States Status: Offline Points: 105 |
Post Options
Thanks(0)
Quote Reply
Posted: 04 July 2010 at 7:47pm |
|
Roberto,
I just tried a fresh install of SF and mysql on a test box and I'm getting the same results. I tried to add an email address to the auth to emails within SF and here is the error i get from the active log.
07/04/10 19:45:05:765 -- (3144) Exception occurred during SaveToDBMemoControl (2): [MySQL][ODBC 5.1 Driver][mysqld-5.0.67-community-nt]Subquery returns more than 1 row
Thanks,
Dan |
|
|
Dan B
|
|
|
|
|
Dan B
Senior Member
Joined: 09 February 2005 Location: United States Status: Offline Points: 105 |
Post Options
Thanks(0)
Quote Reply
Posted: 04 July 2010 at 8:11pm |
|
R,
I've been doing more testing and this is what I'm seeing. If I have the domains in the filtersettings table set to "use default settings from ALL Domain" and I try to add users to the auth to email with the domainid of 1 I get the "Subquery returns more than 1 row" error. If I have "use default settings from ALL Domain" not set to use it I can add email address and don't get the error message.
So I would say that the trigger needs to be tweaked a little to allow records to be added to the tables when the filtersettings records are set to use "All Domain" settings.
I hope this helps.
Thanks,
Dan |
|
|
Dan B
|
|
|
|
|
LogSat
Admin Group
Joined: 25 January 2005 Location: United States Status: Offline Points: 4104 |
Post Options
Thanks(0)
Quote Reply
Posted: 04 July 2010 at 9:21pm |
|
Dan,
I can reproduce the issue, please allow me a few hours to see what it going on. I'll update you both via email and forum as soon as I have a solution.
|
|
|
|
|
Dan B
Senior Member
Joined: 09 February 2005 Location: United States Status: Offline Points: 105 |
Post Options
Thanks(0)
Quote Reply
Posted: 04 July 2010 at 9:28pm |
|
Thanks!! You rock on support! This can wait until Tuesday as today is the 4th and most is of tomorrow as well.
Thanks,
Dan
|
|
|
Dan B
|
|
|
|
|
LogSat
Admin Group
Joined: 25 January 2005 Location: United States Status: Offline Points: 4104 |
Post Options
Thanks(0)
Quote Reply
Posted: 04 July 2010 at 10:06pm |
|
Dan... a bit embarrassing for me, but the reason I was able to duplicate the issue that fast is that I myself did not follow our own documentation when setting up a test box just now to duplicate your issue :-) If it wasn't for that mistake I probably would have spent quite some time to find the problem!
And, to add to the coincidences, going back thru the forums to see if I had any screenshots I could provide you to help locating the "fix", I found a posting between you and I back in 2007 talking about this very same issue, which caused it to modify our documentation to begin with.. :-) that was funny as well! Here's our thread with the root cause: and here's one with a screenshot of where the setting is: You will need to restart SpamFilter after making the change in the ODBC settings. Glad this was an easy one, happy 4th!
Edited by LogSat - 04 July 2010 at 10:07pm |
|
|
|
|
Dan B
Senior Member
Joined: 09 February 2005 Location: United States Status: Offline Points: 105 |
Post Options
Thanks(0)
Quote Reply
Posted: 05 July 2010 at 12:00am |
|
Roberto,
I have a little issue with just setting the odbc setting. I just ColdFusion 8 and it has the option "useAffectedRows" but it doesn't support jdbc. So my web apps are failing due to the subquery error. I have been playing around with the triggers and I was able to get it working by adding
"and (s.domainid = old.domainid)" to the delete and update triggers and "and(s.domainid = new.domainid)" to the insert trigger in the following statement:
set tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.wl_authorizedtoemailsusedefaults = 1) **insert the above examples**);
Do you see anything wrong by doing this for each trigger in the spamfilter database?
Let me know,
Thanks,
Dan |
|
|
Dan B
|
|
|
|
|
LogSat
Admin Group
Joined: 25 January 2005 Location: United States Status: Offline Points: 4104 |
Post Options
Thanks(0)
Quote Reply
Posted: 06 July 2010 at 12:11am |
|
Dan,
Please allow me another day to look into this, while checking into this I see some other possible issues with the triggers, as they should be updating multiple rows in certain scenarios, and while your addition (i.e. and s.domainid = old.domainid) would prevent that from occurring (that would force the trigger to act upon a single row), I do see cases where this (updating multiple rows) is not happening anyways even without your modification.
|
|
|
|
|
LogSat
Admin Group
Joined: 25 January 2005 Location: United States Status: Offline Points: 4104 |
Post Options
Thanks(0)
Quote Reply
Posted: 07 July 2010 at 10:51pm |
|
Dan,
I believe we found the issue (affecting the new feature that allows individual blacklist/whitelist for a domain be inherited from the default ALL DOMAINS, even if the domain itself is not configured to inherit the default settings. We also modified the triggers to avoid the issue you are having with the error "Subquery returns more than 1 row". The next (patched) version of SpamFilter will be executing this script on MySQL to address this (we're using LIMIT 1 in the SQL statement you identified as being an issue to limit the results to 1 row only). DROP TRIGGER trWL_AuthorizedTOEmails_AU; -- GO -- delimiter // CREATE TRIGGER trWL_AuthorizedTOEmails_AU AFTER UPDATE ON tblWL_AuthorizedTOEmails FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = OLD.DomainID) AND (r.TableName = 'tblwl_authorizedtoemails' )) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (OLD.DomainID, 1, 'tblwl_authorizedtoemails'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblwl_authorizedtoemails' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.wl_authorizedtoemailsusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.wl_authorizedtoemailsusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblwl_authorizedtoemails')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblwl_authorizedtoemails'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trWL_AuthorizedTOEmails_AI; -- GO -- delimiter // CREATE TRIGGER trWL_AuthorizedTOEmails_AI AFTER INSERT ON tblWL_AuthorizedTOEmails FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = NEW.DomainID) AND (r.TableName = 'tblwl_authorizedtoemails' )) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (NEW.DomainID, 1, 'tblwl_authorizedtoemails'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblwl_authorizedtoemails' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.wl_authorizedtoemailsusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.wl_authorizedtoemailsusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblwl_authorizedtoemails')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblwl_authorizedtoemails'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trWL_AuthorizedTOEmails_AD; -- GO -- delimiter // CREATE TRIGGER trWL_AuthorizedTOEmails_AD AFTER DELETE ON tblWL_AuthorizedTOEmails FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = OLD.DomainID) AND (r.TableName = 'tblwl_authorizedtoemails' )) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (OLD.DomainID, 1, 'tblwl_authorizedtoemails'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblwl_authorizedtoemails' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.wl_authorizedtoemailsusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.wl_authorizedtoemailsusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblwl_authorizedtoemails')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblwl_authorizedtoemails'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trWL_Keywords_AU; -- GO -- delimiter // CREATE TRIGGER trWL_Keywords_AU AFTER UPDATE ON tblWL_Keywords FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = OLD.DomainID) AND (r.TableName = 'tblwl_keywords')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (OLD.DomainID, 1, 'tblwl_keywords'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblwl_keywords' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.wl_keywordsusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.wl_keywordsusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblwl_keywords')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblwl_keywords'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trWL_Keywords_AI; -- GO -- delimiter // CREATE TRIGGER trWL_Keywords_AI AFTER INSERT ON tblWL_Keywords FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = NEW.DomainID) AND (r.TableName = 'tblwl_keywords')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (NEW.DomainID, 1, 'tblwl_keywords'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblwl_keywords' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.wl_keywordsusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.wl_keywordsusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblwl_keywords')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblwl_keywords'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trWL_Keywords_AD; -- GO -- delimiter // CREATE TRIGGER trWL_Keywords_AD AFTER DELETE ON tblWL_Keywords FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = OLD.DomainID) AND (r.TableName = 'tblwl_keywords')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (OLD.DomainID, 1, 'tblwl_keywords'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblwl_keywords' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.wl_keywordsusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.wl_keywordsusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblwl_keywords')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblwl_keywords'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trWL_EmailsFrom_AU; -- GO -- delimiter // CREATE TRIGGER trWL_EmailsFrom_AU AFTER UPDATE ON tblWL_EmailsFrom FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = OLD.DomainID) AND (r.TableName = 'tblwl_emailsfrom')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (OLD.DomainID, 1, 'tblwl_emailsfrom'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblwl_emailsfrom' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.wl_emailsfromusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.wl_emailsfromusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblwl_emailsfrom')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblwl_emailsfrom'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trWL_EmailsFrom_AI; -- GO -- delimiter // CREATE TRIGGER trWL_EmailsFrom_AI AFTER INSERT ON tblWL_EmailsFrom FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = NEW.DomainID) AND (r.TableName = 'tblwl_emailsfrom')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (NEW.DomainID, 1, 'tblwl_emailsfrom'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblwl_emailsfrom' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.wl_emailsfromusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.wl_emailsfromusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblwl_emailsfrom')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblwl_emailsfrom'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trWL_EmailsFrom_AD; -- GO -- delimiter // CREATE TRIGGER trWL_EmailsFrom_AD AFTER DELETE ON tblWL_EmailsFrom FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = OLD.DomainID) AND (r.TableName = 'tblwl_emailsfrom')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (OLD.DomainID, 1, 'tblwl_emailsfrom'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblwl_emailsfrom' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.wl_emailsfromusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.wl_emailsfromusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblwl_emailsfrom')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblwl_emailsfrom'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trWL_EmailsTO_AU; -- GO -- delimiter // CREATE TRIGGER trWL_EmailsTO_AU AFTER UPDATE ON tblWL_EmailsTO FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = OLD.DomainID) AND (r.TableName = 'tblwl_emailsto')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (OLD.DomainID, 1, 'tblwl_emailsto'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblwl_emailsto' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.wl_emailstousedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.wl_emailstousedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblwl_emailsto')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblwl_emailsto'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trWL_EmailsTO_AI; -- GO -- delimiter // CREATE TRIGGER trWL_EmailsTO_AI AFTER INSERT ON tblWL_EmailsTO FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = NEW.DomainID) AND (r.TableName = 'tblwl_emailsto')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (NEW.DomainID, 1, 'tblwl_emailsto'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblwl_emailsto' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.wl_emailstousedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.wl_emailstousedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblwl_emailsto')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblwl_emailsto'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trWL_EmailsTO_AD; -- GO -- delimiter // CREATE TRIGGER trWL_EmailsTO_AD AFTER DELETE ON tblWL_EmailsTO FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = OLD.DomainID) AND (r.TableName = 'tblwl_emailsto')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (OLD.DomainID, 1, 'tblwl_emailsto'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblwl_emailsto' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.wl_emailstousedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.wl_emailstousedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblwl_emailsto')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblwl_emailsto'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trWL_DomainsIPs_AU; -- GO -- delimiter // CREATE TRIGGER trWL_DomainsIPs_AU AFTER UPDATE ON tblWL_DomainsIPs FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = OLD.DomainID) AND (r.TableName = 'tblwl_domainsips')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (OLD.DomainID, 1, 'tblwl_domainsips'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblwl_domainsips' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.wl_domainsipsusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.wl_domainsipsusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblwl_domainsips')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblwl_domainsips'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trWL_DomainsIPs_AI; -- GO -- delimiter // CREATE TRIGGER trWL_DomainsIPs_AI AFTER INSERT ON tblWL_DomainsIPs FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = NEW.DomainID) AND (r.TableName = 'tblwl_domainsips')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (NEW.DomainID, 1, 'tblwl_domainsips'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblwl_domainsips' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.wl_domainsipsusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.wl_domainsipsusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblwl_domainsips')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblwl_domainsips'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trWL_DomainsIPs_AD; -- GO -- delimiter // CREATE TRIGGER trWL_DomainsIPs_AD AFTER DELETE ON tblWL_DomainsIPs FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = OLD.DomainID) AND (r.TableName = 'tblwl_domainsips')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (OLD.DomainID, 1, 'tblwl_domainsips'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblwl_domainsips' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.wl_domainsipsusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.wl_domainsipsusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblwl_domainsips')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblwl_domainsips'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trBL_Domains_AU; -- GO -- delimiter // CREATE TRIGGER trBL_Domains_AU AFTER UPDATE ON tblBL_Domains FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = OLD.DomainID) AND (r.TableName = 'tblbl_domains')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (OLD.DomainID, 1, 'tblbl_domains'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblbl_domains' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.bl_domainsusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.bl_domainsusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblbl_domains')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblbl_domains'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trBL_Domains_AI; -- GO -- delimiter // CREATE TRIGGER trBL_Domains_AI AFTER INSERT ON tblBL_Domains FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = NEW.DomainID) AND (r.TableName = 'tblbl_domains')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (NEW.DomainID, 1, 'tblbl_domains'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblbl_domains' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.bl_domainsusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.bl_domainsusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblbl_domains')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblbl_domains'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trBL_Domains_AD; -- GO -- delimiter // CREATE TRIGGER trBL_Domains_AD AFTER DELETE ON tblBL_Domains FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = OLD.DomainID) AND (r.TableName = 'tblbl_domains')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (OLD.DomainID, 1, 'tblbl_domains'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblbl_domains' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.bl_domainsusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.bl_domainsusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblbl_domains')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblbl_domains'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trBL_EmailsFrom_AU; -- GO -- delimiter // CREATE TRIGGER trBL_EmailsFrom_AU AFTER UPDATE ON tblBL_EmailsFrom FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = OLD.DomainID) AND (r.TableName = 'tblbl_emailsfrom')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (OLD.DomainID, 1, 'tblbl_emailsfrom'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblbl_emailsfrom' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.bl_emailsfromusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.bl_emailsfromusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblbl_emailsfrom')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblbl_emailsfrom'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trBL_EmailsFrom_AI; -- GO -- delimiter // CREATE TRIGGER trBL_EmailsFrom_AI AFTER INSERT ON tblBL_EmailsFrom FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = NEW.DomainID) AND (r.TableName = 'tblbl_emailsfrom')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (NEW.DomainID, 1, 'tblbl_emailsfrom'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblbl_emailsfrom' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.bl_emailsfromusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.bl_emailsfromusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblbl_emailsfrom')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblbl_emailsfrom'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trBL_EmailsFrom_AD; -- GO -- delimiter // CREATE TRIGGER trBL_EmailsFrom_AD AFTER DELETE ON tblBL_EmailsFrom FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = OLD.DomainID) AND (r.TableName = 'tblbl_emailsfrom')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (OLD.DomainID, 1, 'tblbl_emailsfrom'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblbl_emailsfrom' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.bl_emailsfromusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.bl_emailsfromusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblbl_emailsfrom')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblbl_emailsfrom'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trBL_EmailsTO_AU; -- GO -- delimiter // CREATE TRIGGER trBL_EmailsTO_AU AFTER UPDATE ON tblBL_EmailsTO FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = OLD.DomainID) AND (r.TableName = 'tblbl_emailsto')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (OLD.DomainID, 1, 'tblbl_emailsto'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblbl_emailsto' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.bl_emailstousedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.bl_emailstousedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblbl_emailsto')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblbl_emailsto'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trBL_EmailsTO_AI; -- GO -- delimiter // CREATE TRIGGER trBL_EmailsTO_AI AFTER INSERT ON tblBL_EmailsTO FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = NEW.DomainID) AND (r.TableName = 'tblbl_emailsto')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (NEW.DomainID, 1, 'tblbl_emailsto'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblbl_emailsto' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.bl_emailstousedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.bl_emailstousedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblbl_emailsto')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblbl_emailsto'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trBL_EmailsTO_AD; -- GO -- delimiter // CREATE TRIGGER trBL_EmailsTO_AD AFTER DELETE ON tblBL_EmailsTO FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = OLD.DomainID) AND (r.TableName = 'tblbl_emailsto')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (OLD.DomainID, 1, 'tblbl_emailsto'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblbl_emailsto' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.bl_emailstousedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.bl_emailstousedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblbl_emailsto')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblbl_emailsto'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trBL_MAPS_AU; -- GO -- delimiter // CREATE TRIGGER trBL_MAPS_AU AFTER UPDATE ON tblBL_MAPS FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = OLD.DomainID) AND (r.TableName = 'tblbl_maps')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (OLD.DomainID, 1, 'tblbl_maps'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblbl_maps' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.bl_mapsusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.bl_mapsusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblbl_maps')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblbl_maps'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trBL_MAPS_AI; -- GO -- delimiter // CREATE TRIGGER trBL_MAPS_AI AFTER INSERT ON tblBL_MAPS FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = NEW.DomainID) AND (r.TableName = 'tblbl_maps')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (NEW.DomainID, 1, 'tblbl_maps'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblbl_maps' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.bl_mapsusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.bl_mapsusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblbl_maps')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblbl_maps'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trBL_MAPS_AD; -- GO -- delimiter // CREATE TRIGGER trBL_MAPS_AD AFTER DELETE ON tblBL_MAPS FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = OLD.DomainID) AND (r.TableName = 'tblbl_maps')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (OLD.DomainID, 1, 'tblbl_maps'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblbl_maps' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.bl_mapsusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.bl_mapsusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblbl_maps')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblbl_maps'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trBL_Keywords_AU; -- GO -- delimiter // CREATE TRIGGER trBL_Keywords_AU AFTER UPDATE ON tblBL_Keywords FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = OLD.DomainID) AND (r.TableName = 'tblbl_keywords')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (OLD.DomainID, 1, 'tblbl_keywords'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblbl_keywords' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.bl_keywordsusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.bl_keywordsusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblbl_keywords')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblbl_keywords'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trBL_Keywords_AI; -- GO -- delimiter // CREATE TRIGGER trBL_Keywords_AI AFTER INSERT ON tblBL_Keywords FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = NEW.DomainID) AND (r.TableName = 'tblbl_keywords')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (NEW.DomainID, 1, 'tblbl_keywords'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblbl_keywords' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.bl_keywordsusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.bl_keywordsusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblbl_keywords')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblbl_keywords'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trBL_Keywords_AD; -- GO -- delimiter // CREATE TRIGGER trBL_Keywords_AD AFTER DELETE ON tblBL_Keywords FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = OLD.DomainID) AND (r.TableName = 'tblbl_keywords')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (OLD.DomainID, 1, 'tblbl_keywords'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblbl_keywords' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.bl_keywordsusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.bl_keywordsusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblbl_keywords')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblbl_keywords'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trBL_Attachments_AU; -- GO -- delimiter // CREATE TRIGGER trBL_Attachments_AU AFTER UPDATE ON tblBL_Attachments FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = OLD.DomainID) AND (r.TableName = 'tblbl_attachments')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (OLD.DomainID, 1, 'tblbl_attachments'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblbl_attachments' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.bl_attachmentsusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.bl_attachmentsusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblbl_attachments')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblbl_attachments'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trBL_Attachments_AI; -- GO -- delimiter // CREATE TRIGGER trBL_Attachments_AI AFTER INSERT ON tblBL_Attachments FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = NEW.DomainID) AND (r.TableName = 'tblbl_attachments')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (NEW.DomainID, 1, 'tblbl_attachments'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblbl_attachments' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.bl_attachmentsusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.bl_attachmentsusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblbl_attachments')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblbl_attachments'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trBL_Attachments_AD; -- GO -- delimiter // CREATE TRIGGER trBL_Attachments_AD AFTER DELETE ON tblBL_Attachments FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = OLD.DomainID) AND (r.TableName = 'tblbl_attachments')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (OLD.DomainID, 1, 'tblbl_attachments'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblbl_attachments' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.bl_attachmentsusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.bl_attachmentsusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblbl_attachments')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblbl_attachments'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trBL_SURBL_AU; -- GO -- delimiter // CREATE TRIGGER trBL_SURBL_AU AFTER UPDATE ON tblBL_SURBL FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = OLD.DomainID) AND (r.TableName = 'tblbl_surbl')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (OLD.DomainID, 1, 'tblbl_surbl'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblbl_surbl' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.bl_surblusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.bl_surblusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblbl_surbl')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblbl_surbl'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trBL_SURBL_AI; -- GO -- delimiter // CREATE TRIGGER trBL_SURBL_AI AFTER INSERT ON tblBL_SURBL FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = NEW.DomainID) AND (r.TableName = 'tblbl_surbl')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (NEW.DomainID, 1, 'tblbl_surbl'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblbl_surbl' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.bl_surblusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.bl_surblusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblbl_surbl')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblbl_surbl'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trBL_SURBL_AD; -- GO -- delimiter // CREATE TRIGGER trBL_SURBL_AD AFTER DELETE ON tblBL_SURBL FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = OLD.DomainID) AND (r.TableName = 'tblbl_surbl')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (OLD.DomainID, 1, 'tblbl_surbl'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblbl_surbl' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.bl_surblusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.bl_surblusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblbl_surbl')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblbl_surbl'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trBL_IPs_AU; -- GO -- delimiter // CREATE TRIGGER trBL_IPs_AU AFTER UPDATE ON tblBL_IPs FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = OLD.DomainID) AND (r.TableName = 'tblbl_ips')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (OLD.DomainID, 1, 'tblbl_ips'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblbl_ips' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.bl_ipsusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.bl_ipsusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblbl_ips')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblbl_ips'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trBL_IPs_AI; -- GO -- delimiter // CREATE TRIGGER trBL_IPs_AI AFTER INSERT ON tblBL_IPs FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = NEW.DomainID) AND (r.TableName = 'tblbl_ips')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (NEW.DomainID, 1, 'tblbl_ips'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblbl_ips' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.bl_ipsusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.bl_ipsusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblbl_ips')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblbl_ips'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trBL_IPs_AD; -- GO -- delimiter // CREATE TRIGGER trBL_IPs_AD AFTER DELETE ON tblBL_IPs FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = OLD.DomainID) AND (r.TableName = 'tblbl_ips')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (OLD.DomainID, 1, 'tblbl_ips'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblbl_ips' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.bl_ipsusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.bl_ipsusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblbl_ips')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblbl_ips'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trBL_HoneypotEmails_AU; -- GO -- delimiter // CREATE TRIGGER trBL_HoneypotEmails_AU AFTER UPDATE ON tblBL_HoneypotEmails FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = OLD.DomainID) AND (r.TableName = 'tblbl_honeypotemails')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (OLD.DomainID, 1, 'tblbl_honeypotemails'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblbl_honeypotemails' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.bl_honeypotemailsusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.bl_honeypotemailsusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblbl_honeypotemails')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblbl_honeypotemails'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trBL_HoneypotEmails_AI; -- GO -- delimiter // CREATE TRIGGER trBL_HoneypotEmails_AI AFTER INSERT ON tblBL_HoneypotEmails FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = NEW.DomainID) AND (r.TableName = 'tblbl_honeypotemails')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (NEW.DomainID, 1, 'tblbl_honeypotemails'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblbl_honeypotemails' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.bl_honeypotemailsusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.bl_honeypotemailsusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblbl_honeypotemails')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblbl_honeypotemails'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER trBL_HoneypotEmails_AD; -- GO -- delimiter // CREATE TRIGGER trBL_HoneypotEmails_AD AFTER DELETE ON tblBL_HoneypotEmails FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = OLD.DomainID) AND (r.TableName = 'tblbl_honeypotemails')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (OLD.DomainID, 1, 'tblbl_honeypotemails'); END IF; BEGIN DECLARE tmp_id INT; INSERT INTO tblreloadtableinfo (domainid, updatetable, tablename) SELECT s.domainid, 1, 'tblbl_honeypotemails' FROM tbl_localdomains d INNER JOIN tbl_filtersettings s ON d.id = s.domainid WHERE (d.usedefaults=0) AND (s.bl_honeypotemailsusedefaults = 1); SET tmp_id = (select s.domainid from tbl_localdomains d inner join tbl_filtersettings s on d.id = s.domainid where (d.usedefaults=0) and (s.bl_honeypotemailsusedefaults = 1) LIMIT 1); if not exists (select * from tblreloadtableinfo r where (r.domainid = tmp_id) and (r.tablename = 'tblbl_honeypotemails')) then insert into tblreloadtableinfo (domainid, updatetable, tablename) values(tmp_id, 1, 'tblbl_honeypotemails'); end if; END; END; // delimiter ; -- GO -- DROP TRIGGER tr_FilterSettings_AU; -- GO -- delimiter // CREATE TRIGGER tr_FilterSettings_AU AFTER UPDATE ON tbl_FilterSettings FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM tblReloadTableInfo r WHERE (r.DomainID = OLD.DomainID) AND (r.TableName = 'tbl_FilterSettings')) THEN INSERT INTO tblReloadTableInfo (DomainID, UpdateTable, TableName) VALUES (OLD.DomainID, 1, 'tbl_FilterSettings'); END IF; BEGIN if (OLD.bl_domainsusedefaults <> NEW.bl_domainsusedefaults) then insert into tblreloadtableinfo (domainid, updatetable, tablename) VALUES (NEW.domainid, 1, 'tblbl_domains'); end if; if (OLD.bl_emailsfromusedefaults <> NEW.bl_emailsfromusedefaults) then insert into tblreloadtableinfo (domainid, updatetable, tablename) VALUES (NEW.domainid, 1, 'tblbl_emailsfrom'); end if; if (OLD.bl_emailstousedefaults <> NEW.bl_emailstousedefaults) then insert into tblreloadtableinfo (domainid, updatetable, tablename) VALUES (NEW.domainid, 1, 'tblbl_emailsto'); end if; if (OLD.bl_countriesusedefaults <> NEW.bl_countriesusedefaults) then insert into tblreloadtableinfo (domainid, updatetable, tablename) VALUES (NEW.domainid, 1, 'tblbl_countries'); end if; if (OLD.bl_mapsusedefaults <> NEW.bl_mapsusedefaults) then insert into tblreloadtableinfo (domainid, updatetable, tablename) VALUES (NEW.domainid, 1, 'tblbl_maps'); end if; if (OLD.bl_keywordsusedefaults <> NEW.bl_keywordsusedefaults) then insert into tblreloadtableinfo (domainid, updatetable, tablename) VALUES (NEW.domainid, 1, 'tblbl_keywords'); end if; if (OLD.bl_attachmentsusedefaults <> NEW.bl_attachmentsusedefaults) then insert into tblreloadtableinfo (domainid, updatetable, tablename) VALUES (NEW.domainid, 1, 'tblbl_attachments'); end if; if (OLD.bl_surblusedefaults <> NEW.bl_surblusedefaults) then insert into tblreloadtableinfo (domainid, updatetable, tablename) VALUES (NEW.domainid, 1, 'tblbl_surbl'); end if; if (OLD.bl_ipsusedefaults <> NEW.bl_ipsusedefaults) then insert into tblreloadtableinfo (domainid, updatetable, tablename) VALUES (NEW.domainid, 1, 'tblbl_ips'); end if; if (OLD.bl_honeypotemailsusedefaults <> NEW.bl_honeypotemailsusedefaults) then insert into tblreloadtableinfo (domainid, updatetable, tablename) VALUES (NEW.domainid, 1, 'tblbl_honeypotemails'); end if; if (OLD.wl_domainsipsusedefaults <> NEW.wl_domainsipsusedefaults) then insert into tblreloadtableinfo (domainid, updatetable, tablename) VALUES (NEW.domainid, 1, 'tblwl_domainsips'); end if; if (OLD.wl_emailstousedefaults <> NEW.wl_emailstousedefaults) then insert into tblreloadtableinfo (domainid, updatetable, tablename) VALUES (NEW.domainid, 1, 'tblwl_emailsto'); end if; if (OLD.wl_emailsfromusedefaults <> NEW.wl_emailsfromusedefaults) then insert into tblreloadtableinfo (domainid, updatetable, tablename) VALUES (NEW.domainid, 1, 'tblwl_emailsfrom'); end if; if (OLD.wl_authorizedtoemailsusedefaults <> NEW.wl_authorizedtoemailsusedefaults) then insert into tblreloadtableinfo (domainid, updatetable, tablename) VALUES (NEW.domainid, 1, 'tblwl_authorizedtoemails'); end if; if (OLD.wl_keywordsusedefaults <> NEW.wl_keywordsusedefaults) then insert into tblreloadtableinfo (domainid, updatetable, tablename) VALUES (NEW.domainid, 1, 'tblwl_keywords'); end if; END; END; // delimiter ; -- GO -- |
|
|
|
Topic Options|
Post Reply
|
|
Tweet
|
| Forum Jump | Forum Permissions You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum |
This page was generated in 0.125 seconds.