/*
*   Notify convention: OPNAME$RELNAME
*   OPNAME: INSERT, UPDATE, DELETE
*   Notify List:
*   Lan Setting                 UPDATE$NETWORK                  uunetwork restart
*   iStar Setting               UPDATE$ISTAR                    restart gated, reload agent
*   iStar Status                UP(DOWN)$ISTAR                  registered/ready or unregistered/unready (call led function)
*   License ConcurrentUser      UPDATE$CONCURRENTUSER           reload app
*   License Bandwidth           UPDATE$BANDWIDTH                traffic control
*   Local Time                  UPDATE$TIME                     set local system time
*   TimeZone Changed            UPDATE$TIMEZONE                 reset timezone
*   NTP Setting                 UPDATE$NTP                      check ntp setting
*   System Option               UPDATE$LOGDEBUG                 reload all
*   Encrypt/Hash Algorithms     UPDATE$ENCRYPT                  reload gated agent
*   App Option                  UPDATE$APPOPTION                reload app
*   Admin Option                UPDATE$ADMINOPTION              reload admin
*   Remote Option               UPDATE$REMOTEOPTION             reload Remote
*   Soft Option                 UPDATE$SOFTOPTION               reload Soft
*   DHCP Setting                UPDATE$DHCP                     service dhcpd restart
*   STUN Server Update          UPDATE$STUN                     service agent restart
*/

/*
* license triggers
*/
DROP TRIGGER before_insert_license_basic;
CREATE TRIGGER before_insert_license_basic BEFORE INSERT ON license_basic FOR EACH ROW
    WHEN (SELECT COUNT(*) FROM license_basic) <> 0
BEGIN
    SELECT RAISE(ROLLBACK, 'Forbid to insert or delete orphan record');
END;

DROP TRIGGER before_delete_license_basic;
CREATE TRIGGER before_delete_license_basic BEFORE DELETE ON license_basic FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'Forbid to insert or delete orphan record');
END;

DROP TRIGGER before_update_license_basic;
CREATE TRIGGER before_update_license_basic BEFORE UPDATE ON license_basic FOR EACH ROW
BEGIN
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.if_default NOT IN('TRUE', 'FALSE'))
    OR (NEW.imported_date NOT LIKE '____-__-__ __:__:__')
    OR (NEW.valid_from IS NOT NULL AND NEW.valid_from NOT LIKE '____-__-__')
    OR (NEW.valid_to IS NOT NULL AND NEW.valid_to NOT LIKE '____-__-__');
    -- consistency check
    SELECT RAISE(ROLLBACK, 'Violate consistency check constraint')
    WHERE (NEW.valid_from > NEW.valid_to);
END;

DROP TRIGGER after_update_license_basic;
CREATE TRIGGER after_update_license_basic AFTER UPDATE ON license_basic FOR EACH ROW
BEGIN
    SELECT NOTIFY("UPDATE$LICENSE", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17 AND NEW.if_default <> OLD.if_default OR NEW.if_default = 'FALSE' AND NEW.lbf_file <> OLD.lbf_file;
END;

DROP TRIGGER before_update_license_features;
CREATE TRIGGER before_update_license_features BEFORE UPDATE ON license_features FOR EACH ROW
BEGIN
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (LENGTH(NEW.name) NOT BETWEEN 1 AND 64)
    -- OR (NEW.name NOT IN('Bandwidth', 'ConcurrentUser', 'UURemoteAccess', 'UURemoteStation', 'UUMobile', 'UUCert'))
    OR (NEW.if_visible NOT IN('TRUE', 'FALSE'))
    OR (NEW.if_enable NOT IN('TRUE', 'FALSE'))
    OR (NEW.valid_from IS NOT NULL AND NEW.valid_from NOT LIKE '____-__-__')
    OR (NEW.valid_to IS NOT NULL AND NEW.valid_to NOT LIKE '____-__-__');
    -- consistency check
    SELECT RAISE(ROLLBACK, 'Violate consistency check constraint')
    WHERE (NEW.valid_from > NEW.valid_to);
END;

-- change license by UUCentral
DROP TRIGGER before_update_license_dynamics;
CREATE TRIGGER before_update_license_dynamics BEFORE UPDATE ON license_dynamics FOR EACH ROW
BEGIN
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (LENGTH(NEW.name) NOT BETWEEN 1 AND 64)
    -- OR (NEW.name NOT IN('Bandwidth', 'ConcurrentUser', 'UURemoteAccess'))
    OR (NEW.if_visible NOT IN('TRUE', 'FALSE'))
    OR (NEW.if_enable NOT IN('TRUE', 'FALSE'))
    OR (NEW.valid_from IS NOT NULL AND NEW.valid_from NOT LIKE '____-__-__')
    OR (NEW.valid_to IS NOT NULL AND NEW.valid_to NOT LIKE '____-__-__');
    -- consistency check
    SELECT RAISE(ROLLBACK, 'Violate consistency check constraint')
    WHERE (NEW.valid_from > NEW.valid_to);
END;

DROP TRIGGER after_update_license_dynamics;
CREATE TRIGGER after_update_license_dynamics AFTER UPDATE ON license_dynamics FOR EACH ROW
BEGIN
    SELECT NOTIFY("UPDATE$CONCURRENTUSER", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17 AND NEW.number <> OLD.number AND NEW.name='ConcurrentUser';
    SELECT NOTIFY("UPDATE$BANDWIDTH", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17 AND NEW.number <> OLD.number AND NEW.name='Bandwidth';
END;

/*
* network triggers
*/
DROP TRIGGER before_insert_network_system;
CREATE TRIGGER before_insert_network_system BEFORE INSERT ON network_system FOR EACH ROW
    WHEN (SELECT COUNT(*) FROM network_system) <> 0
BEGIN
    SELECT RAISE(ROLLBACK, 'Forbid to insert or delete orphan record');
END;

DROP TRIGGER before_delete_network_system;
CREATE TRIGGER before_delete_network_system BEFORE DELETE ON network_system FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'Forbid to insert or delete orphan record');
END;

DROP TRIGGER before_update_network_system;
CREATE TRIGGER before_update_network_system BEFORE UPDATE ON network_system FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM static_host_types WHERE id = NEW.host_typeid) IS NULL
    OR (SELECT id FROM static_system_modes WHERE id = NEW.system_modeid) IS NULL
    OR (SELECT id FROM static_proxy_types WHERE id = NEW.proxy_typeid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.host_name IS NOT NULL AND LENGTH(NEW.host_name) NOT BETWEEN 1 AND 64)
    OR (NEW.cluster_key IS NOT NULL AND LENGTH(NEW.cluster_key) NOT BETWEEN 1 AND 64)
    OR (NEW.dns_domain IS NOT NULL AND LENGTH(NEW.dns_domain) NOT BETWEEN 1 AND 64)
    OR (NEW.proxy_scripturl IS NOT NULL AND LENGTH(NEW.proxy_scripturl) NOT BETWEEN 1 AND 128)
    OR (NEW.if_recovery NOT IN('TRUE', 'FALSE'))
    OR (NEW.proxy_server IS NOT NULL AND LENGTH(NEW.proxy_server) NOT BETWEEN 1 AND 64)
    OR (NEW.proxy_port IS NOT NULL AND NEW.proxy_port NOT BETWEEN 1 AND 65535)
    OR (NEW.recovery_interval IS NOT NULL AND NEW.recovery_interval NOT BETWEEN 1 AND 10);
    -- consistency check
    SELECT RAISE(ROLLBACK, 'Violate consistency check constraint')
    WHERE (NEW.proxy_typeid BETWEEN 1 AND 3 AND (NEW.proxy_server IS NULL OR NEW.proxy_port IS NULL))
    OR (NEW.if_recovery = 'TRUE' AND (SELECT connector_typeid FROM istar_connector WHERE connector_typeid <> 1) IS NULL);
END;

/*
DROP TRIGGER after_update_network_system;
CREATE TRIGGER after_update_network_system AFTER UPDATE ON network_system FOR EACH ROW
BEGIN
    SELECT NOTIFY("UPDATE$NETWORK", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17;
END;
*/

-- network proxy users
DROP TRIGGER before_insert_network_proxy_users;
CREATE TRIGGER before_insert_network_proxy_users BEFORE INSERT ON network_proxy_users FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM static_proxy_types WHERE id = NEW.proxy_typeid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (LENGTH(NEW.username) NOT BETWEEN 1 AND 64)
    OR (LENGTH(NEW.password) NOT BETWEEN 1 AND 64)
    OR (NEW.domain IS NOT NULL AND LENGTH(NEW.domain) NOT BETWEEN 1 AND 64);
END;

DROP TRIGGER before_update_network_proxy_users;
CREATE TRIGGER before_update_network_proxy_users BEFORE UPDATE ON network_proxy_users FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM static_proxy_types WHERE id = NEW.proxy_typeid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (LENGTH(NEW.username) NOT BETWEEN 1 AND 64)
    OR (LENGTH(NEW.password) NOT BETWEEN 1 AND 64)
    OR (NEW.domain IS NOT NULL AND LENGTH(NEW.domain) NOT BETWEEN 1 AND 64);
END;

-- network_interfaces
DROP TRIGGER before_delete_network_interfaces;
CREATE TRIGGER before_delete_network_interfaces BEFORE DELETE ON network_interfaces FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'Forbid to delete immortal record');
END;

DROP TRIGGER before_update_network_interfaces;
CREATE TRIGGER before_update_network_interfaces BEFORE UPDATE ON network_interfaces FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM static_access_types WHERE id = NEW.access_typeid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.name NOT IN('CONFIG', 'CLUSTER', 'INTERNAL', 'EXTERNAL'))
    OR (NEW.device NOT IN('PORT_CONFIG', 'PORT_CLUSTER', 'PORT_INTERNAL', 'PORT_EXTERNAL'))
    OR (NEW.if_enable NOT IN('TRUE', 'FALSE'))
    OR (NEW.bootproto NOT IN('static', 'dhcp', 'pppoe'))
    OR (NEW.if_gateway NOT IN('TRUE', 'FALSE'))
    OR (NEW.if_dynamic_dns NOT IN('TRUE', 'FALSE'))
    OR (NEW.onboot NOT IN('yes', 'no'))
    OR (NEW.ip IS NOT NULL AND (NEW.ip NOT LIKE '%.%.%.%/%' OR LENGTH(NEW.ip) NOT BETWEEN 9 AND 18))
    OR (NEW.gateway IS NOT NULL AND (NEW.gateway NOT LIKE '%.%.%.%' OR LENGTH(NEW.gateway) NOT BETWEEN 7 AND 15))
    OR (NEW.adsl_username IS NOT NULL AND LENGTH(NEW.adsl_username) NOT BETWEEN 1 AND 64)
    OR (NEW.adsl_password IS NOT NULL AND LENGTH(NEW.adsl_password) NOT BETWEEN 1 AND 64);
    -- consistency check
    SELECT RAISE(ROLLBACK, 'Violate consistency check constraint')
    WHERE (NEW.access_typeid = 3 AND (NEW.adsl_username IS NULL OR NEW.adsl_password IS NULL))
    OR (NEW.name = 'CLUSTER' AND NEW.gateway IS NOT NULL)
    OR (NEW.access_typeid = 1 AND NEW.if_dynamic_dns <> 'FALSE');
END;

DROP TRIGGER after_update_network_interfaces;
CREATE TRIGGER after_update_network_interfaces AFTER UPDATE ON network_interfaces FOR EACH ROW
BEGIN
    UPDATE cluster_routers SET if_enable = 'FALSE' WHERE NEW.name = 'CLUSTER' AND NEW.if_enable = 'FALSE';
    UPDATE cluster_forwards SET if_enable = 'FALSE' WHERE NEW.name = 'CLUSTER' AND NEW.if_enable = 'FALSE';
    DELETE FROM network_clusters WHERE id <> 1 AND NEW.name = 'CLUSTER' AND NEW.if_enable = 'FALSE';
    UPDATE network_clusters SET ip_from = '127.0.0.1', ip_to = '127.0.0.1' WHERE NEW.name = 'CLUSTER' AND NEW.if_enable = 'FALSE';
END;

-- network dns server
DROP TRIGGER before_insert_network_dns_servers;
CREATE TRIGGER before_insert_network_dns_servers BEFORE INSERT ON network_dns_servers FOR EACH ROW
BEGIN
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.ip NOT LIKE '%.%.%.%' OR LENGTH(NEW.ip) NOT BETWEEN 7 AND 15);
END;

DROP TRIGGER before_update_network_dns_servers;
CREATE TRIGGER before_update_network_dns_servers BEFORE UPDATE ON network_dns_servers FOR EACH ROW
BEGIN
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.ip NOT LIKE '%.%.%.%' OR LENGTH(NEW.ip) NOT BETWEEN 7 AND 15);
END;

-- network_clusters
DROP TRIGGER before_insert_network_clusters;
CREATE TRIGGER before_insert_network_clusters BEFORE INSERT ON network_clusters FOR EACH ROW
BEGIN
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.ip_from NOT LIKE '%.%.%.%' OR LENGTH(NEW.ip_from) NOT BETWEEN 7 AND 15)
    OR (NEW.ip_to NOT LIKE '%.%.%.%' OR LENGTH(NEW.ip_to) NOT BETWEEN 7 AND 15);
END;

DROP TRIGGER before_update_network_clusters;
CREATE TRIGGER before_update_network_clusters BEFORE UPDATE ON network_clusters FOR EACH ROW
BEGIN
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.ip_from NOT LIKE '%.%.%.%' OR LENGTH(NEW.ip_from) NOT BETWEEN 7 AND 15)
    OR (NEW.ip_to NOT LIKE '%.%.%.%' OR LENGTH(NEW.ip_to) NOT BETWEEN 7 AND 15);
END;

DROP TRIGGER before_delete_network_clusters;
CREATE TRIGGER before_delete_network_clusters BEFORE DELETE ON network_clusters FOR EACH ROW
    WHEN OLD.id = 1
BEGIN
    SELECT RAISE(ROLLBACK, 'Forbid to delete immortal record');
END;

/*
* status triggers
*/
-- status sequences
DROP TRIGGER after_update_status_sequences;
CREATE TRIGGER after_update_status_sequences AFTER UPDATE ON status_sequences FOR EACH ROW
    WHEN NEW.next_value > NEW.max_value
BEGIN
    UPDATE status_sequences SET next_value = min_value WHERE sequence_name = NEW.sequence_name AND if_cycled = 'TRUE';
    UPDATE status_sequences SET next_value = 0 WHERE sequence_name = NEW.sequence_name AND if_cycled = 'FALSE';
END;

-- status interface
DROP TRIGGER before_insert_status_interface;
CREATE TRIGGER before_insert_status_interface BEFORE INSERT ON status_interface FOR EACH ROW
    WHEN (SELECT COUNT(*) FROM status_interface) <> 0
BEGIN
    SELECT RAISE(ROLLBACK, 'Forbid to insert or delete orphan record');
END;

DROP TRIGGER before_delete_status_interface;
CREATE TRIGGER before_delete_status_interface BEFORE DELETE ON status_interface FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'Forbid to insert or delete orphan record');
END;

DROP TRIGGER before_update_status_interface;
CREATE TRIGGER before_update_status_interface BEFORE UPDATE ON status_interface FOR EACH ROW
BEGIN
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.if_internalon NOT IN('TRUE', 'FALSE'))
    OR (NEW.if_externalon NOT IN('TRUE', 'FALSE'))
    OR (NEW.if_externalup NOT IN('TRUE', 'FALSE'))
    OR (NEW.external_ip NOT LIKE '%.%.%.%' OR LENGTH(NEW.external_ip) NOT BETWEEN 7 AND 15)
    OR (NEW.time NOT LIKE '____-__-__ __:__:__');
END;

DROP TRIGGER after_update_status_interface;
CREATE TRIGGER after_update_status_interface AFTER UPDATE ON status_interface FOR EACH ROW
BEGIN
    SELECT NOTIFY("UPDATE$INTERFACE", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17;
END;

-- status istar
DROP TRIGGER before_insert_status_istar;
CREATE TRIGGER before_insert_status_istar BEFORE INSERT ON status_istar FOR EACH ROW
    WHEN (SELECT COUNT(*) FROM status_istar) <> 0
BEGIN
    SELECT RAISE(ROLLBACK, 'Forbid to insert or delete orphan record');
END;

DROP TRIGGER before_delete_status_istar;
CREATE TRIGGER before_delete_status_istar BEFORE DELETE ON status_istar FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'Forbid to insert or delete orphan record');
END;

DROP TRIGGER before_update_status_istar;
CREATE TRIGGER before_update_status_istar BEFORE UPDATE ON status_istar FOR EACH ROW
BEGIN
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.if_connected NOT IN('TRUE', 'FALSE'))
    OR (NEW.time NOT LIKE '____-__-__ __:__:__');
END;

DROP TRIGGER after_update_status_istar;
CREATE TRIGGER after_update_status_istar AFTER UPDATE ON status_istar FOR EACH ROW
BEGIN
    SELECT NOTIFY("UP$ISTAR", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17 AND NEW.if_connected='TRUE';
    SELECT NOTIFY("DOWN$ISTAR", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17 AND NEW.if_connected='FALSE';
    SELECT NOTIFY("UPDATE$STUN", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17 AND NEW.stun_server <> OLD.stun_server;
END;

-- status_database
DROP TRIGGER after_update_status_database;
CREATE TRIGGER after_update_status_database AFTER UPDATE ON status_database FOR EACH ROW
BEGIN
    SELECT NOTIFY("UPDATE$DATABASE", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17 AND NEW.recovery <> OLD.recovery;
END;

-- status statistics
DROP TRIGGER before_insert_status_statistics;
CREATE TRIGGER before_insert_status_statistics BEFORE INSERT ON status_statistics FOR EACH ROW
BEGIN
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.cpu_percent NOT BETWEEN 0 AND 100)
    OR (NEW.mem_percent NOT BETWEEN 0 AND 100)
    OR (NEW.time NOT LIKE '____-__-__ __:__:__');
END;

DROP TRIGGER after_insert_status_statistics;
CREATE TRIGGER after_insert_status_statistics AFTER INSERT ON status_statistics FOR EACH ROW
BEGIN
    UPDATE status_sequences SET next_value = NEW.id + increment_by WHERE sequence_name='status_statistics' AND next_value <> 0;
END;

-- status summary
DROP TRIGGER before_insert_status_summary;
CREATE TRIGGER before_insert_status_summary BEFORE INSERT ON status_summary FOR EACH ROW
BEGIN
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.cpu_max NOT BETWEEN 0 AND 100)
    OR (NEW.cpu_min NOT BETWEEN 0 AND 100)
    OR (NEW.cpu_avg NOT BETWEEN 0 AND 100)
    OR (NEW.mem_max NOT BETWEEN 0 AND 100)
    OR (NEW.mem_min NOT BETWEEN 0 AND 100)
    OR (NEW.mem_avg NOT BETWEEN 0 AND 100)
    OR (NEW.date NOT LIKE '____-__-__');
END;

DROP TRIGGER after_insert_status_summary;
CREATE TRIGGER after_insert_status_summary AFTER INSERT ON status_summary FOR EACH ROW
BEGIN
    UPDATE status_sequences SET next_value = NEW.id + increment_by WHERE sequence_name='status_summary' AND next_value <> 0;
END;

/*
* system triggers
*/
-- system time check
DROP TRIGGER before_insert_system_time;
CREATE TRIGGER before_insert_system_time BEFORE INSERT ON system_time FOR EACH ROW
    WHEN (SELECT COUNT(*) FROM system_time) <> 0
BEGIN
    SELECT RAISE(ROLLBACK, 'Forbid to insert or delete orphan record');
END;

DROP TRIGGER before_delete_system_time;
CREATE TRIGGER before_delete_system_time BEFORE DELETE ON system_time FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'Forbid to insert or delete orphan record');
END;

DROP TRIGGER before_update_system_time;
CREATE TRIGGER before_update_system_time BEFORE UPDATE ON system_time FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM static_time_zones WHERE id = NEW.time_zoneid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.date NOT LIKE '____-__-__')
    OR (NEW.time NOT LIKE '__:__:__')
    OR (NEW.server IS NOT NULL AND LENGTH(NEW.server) NOT BETWEEN 1 AND 64)
    OR (NEW.update_interval IS NOT NULL AND NEW.update_interval NOT BETWEEN 1 AND 60);
    -- consistency check
    SELECT RAISE(ROLLBACK, 'Violate consistency check constraint')
    WHERE (NEW.if_ntp = 'TRUE' AND NEW.server IS NULL);
END;

DROP TRIGGER after_update_system_time;
CREATE TRIGGER after_update_system_time AFTER UPDATE ON system_time FOR EACH ROW
BEGIN
    SELECT NOTIFY("UPDATE$TIMEZONE", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17 AND NEW.time_zoneid <> OLD.time_zoneid AND NEW.date = OLD.date AND NEW.time = OLD.time;
    SELECT NOTIFY("UPDATE$TZTIME", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17 AND NEW.time_zoneid <> OLD.time_zoneid AND (NEW.date <> OLD.date OR NEW.time <> OLD.time);
    SELECT NOTIFY("UPDATE$NTP", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17 AND NEW.if_ntp <> OLD.if_ntp OR NEW.server <> OLD.server OR NEW.update_interval <> OLD.update_interval;
    SELECT NOTIFY("UPDATE$TIME", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17 AND NEW.if_ntp = 'FALSE' AND NEW.time_zoneid = OLD.time_zoneid AND (NEW.date <> OLD.date OR NEW.time <> OLD.time);
END;

-- system option
DROP TRIGGER before_insert_system_option;
CREATE TRIGGER before_insert_system_option BEFORE INSERT ON system_option FOR EACH ROW
    WHEN (SELECT COUNT(*) FROM system_option) <> 0
BEGIN
    SELECT RAISE(ROLLBACK, 'Forbid to insert or delete orphan record');
END;

DROP TRIGGER before_delete_system_option;
CREATE TRIGGER before_delete_system_option BEFORE DELETE ON system_option FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'Forbid to insert or delete orphan record');
END;

DROP TRIGGER before_update_system_option;
CREATE TRIGGER before_update_system_option BEFORE UPDATE ON system_option FOR EACH ROW
BEGIN
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.if_debug NOT IN('TRUE', 'FALSE'))
    OR (NEW.sample_interval IS NOT NULL AND NEW.sample_interval NOT BETWEEN 1 AND 3600);
END;

DROP TRIGGER after_update_system_option;
CREATE TRIGGER after_update_system_option AFTER UPDATE ON system_option FOR EACH ROW
BEGIN
    SELECT NOTIFY("UPDATE$LOGDEBUG", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17 AND NEW.if_debug <> OLD.if_debug;
END;

-- system sequences
DROP TRIGGER after_update_system_sequences;
CREATE TRIGGER after_update_system_sequences AFTER UPDATE ON system_sequences FOR EACH ROW
    WHEN NEW.next_value > NEW.max_value
BEGIN
    UPDATE system_sequences SET next_value = min_value WHERE sequence_name = NEW.sequence_name AND if_cycled = 'TRUE';
    UPDATE system_sequences SET next_value = 0 WHERE sequence_name = NEW.sequence_name AND if_cycled = 'FALSE';
END;

-- system statistics
DROP TRIGGER before_insert_system_statistics;
CREATE TRIGGER before_insert_system_statistics BEFORE INSERT ON system_statistics FOR EACH ROW
BEGIN
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.cluster_ip NOT LIKE '%.%.%.%' OR LENGTH(NEW.cluster_ip) NOT BETWEEN 7 AND 15)
    OR (SELECT id FROM static_service_types WHERE id = NEW.service_typeid) IS NULL
    OR (LENGTH(NEW.name) NOT BETWEEN 1 AND 64)
    OR (LENGTH(NEW.realm) NOT BETWEEN 1 AND 64)
    OR (NEW.ip NOT LIKE '%.%.%.%' OR LENGTH(NEW.ip) NOT BETWEEN 7 AND 15)
    OR (NEW.if_online NOT IN('TRUE', 'FALSE'))
    OR (NEW.login_time NOT LIKE '____-__-__ __:__:__')
    OR (NEW.logout_time NOT LIKE '____-__-__ __:__:__');
END;

DROP TRIGGER before_update_system_statistics;
CREATE TRIGGER before_update_system_statistics BEFORE UPDATE ON system_statistics FOR EACH ROW
BEGIN
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.cluster_ip NOT LIKE '%.%.%.%' OR LENGTH(NEW.cluster_ip) NOT BETWEEN 7 AND 15)
    OR (SELECT id FROM static_service_types WHERE id = NEW.service_typeid) IS NULL
    OR (LENGTH(NEW.name) NOT BETWEEN 1 AND 64)
    OR (LENGTH(NEW.realm) NOT BETWEEN 1 AND 64)
    OR (NEW.ip NOT LIKE '%.%.%.%' OR LENGTH(NEW.ip) NOT BETWEEN 7 AND 15)
    OR (NEW.if_online NOT IN('TRUE', 'FALSE'))
    OR (NEW.login_time NOT LIKE '____-__-__ __:__:__')
    OR (NEW.logout_time NOT LIKE '____-__-__ __:__:__');
END;

DROP TRIGGER after_insert_system_statistics;
CREATE TRIGGER after_insert_system_statistics AFTER INSERT ON system_statistics FOR EACH ROW
BEGIN
    UPDATE system_sequences SET next_value = NEW.id + increment_by WHERE sequence_name='system_statistics' AND next_value <> 0;
END;

DROP TRIGGER after_delete_system_statistics;
CREATE TRIGGER after_delete_system_statistics AFTER DELETE ON system_statistics FOR EACH ROW
BEGIN
    DELETE FROM system_sessions WHERE system_statisticid = OLD.id;
END;

DROP TRIGGER before_insert_system_sessions;
CREATE TRIGGER before_insert_system_sessions BEFORE INSERT ON system_sessions FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM system_statistics WHERE id = NEW.system_statisticid) IS NULL;
END;

DROP TRIGGER after_delete_system_sessions;
CREATE TRIGGER after_delete_system_sessions AFTER DELETE ON system_sessions FOR EACH ROW
BEGIN
    UPDATE system_statistics SET if_online='FALSE', logout_time=CURRENT_TIMESTAMP WHERE id = OLD.system_statisticid AND if_online='TRUE';
END;

/*
* istar setting triggers
*/
DROP TRIGGER after_insert_istar_connector;
CREATE TRIGGER after_insert_istar_connector AFTER INSERT ON istar_connector FOR EACH ROW
BEGIN
    SELECT NOTIFY("UPDATE$ISTAR", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17;
    UPDATE cert_roots SET if_enable = CASE WHEN NEW.connector_typeid = 1 THEN 'FALSE' ELSE 'TRUE' END WHERE type = 3;
END;

DROP TRIGGER before_update_istar_connector;
CREATE TRIGGER before_update_istar_connector BEFORE UPDATE ON istar_connector FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM static_connector_types WHERE id = NEW.connector_typeid) IS NULL;
END;

DROP TRIGGER before_update_istar_certs;
CREATE TRIGGER before_update_istar_certs BEFORE UPDATE ON istar_certs FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM static_connector_types WHERE id=NEW.connector_typeid) IS NULL
    OR (SELECT id FROM static_uuid_types WHERE id = NEW.uuid_typeid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.if_csr NOT IN('TRUE', 'FALSE'))
    OR (NEW.valid_from NOT LIKE '____-__-__ __:__:__')
    OR (NEW.valid_to NOT LIKE '____-__-__ __:__:__');
END;

DROP TRIGGER after_update_istar_connector;
CREATE TRIGGER after_update_istar_connector AFTER UPDATE ON istar_connector FOR EACH ROW
BEGIN
    SELECT NOTIFY("UPDATE$ISTAR", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17;
    UPDATE cert_roots SET if_enable = CASE WHEN NEW.connector_typeid = 1 THEN 'FALSE' ELSE 'TRUE' END WHERE type = 3;
END;

DROP TRIGGER after_delete_istar_certs;
CREATE TRIGGER after_delete_istar_certs AFTER DELETE ON istar_certs FOR EACH ROW
BEGIN
    DELETE FROM istar_cert_servers WHERE istar_certid = OLD.id;
    DELETE FROM istar_license_certs WHERE istar_certid = OLD.id;
END;

/*
* static routes
*/
DROP TRIGGER before_insert_static_routes;
CREATE TRIGGER before_insert_static_routes BEFORE INSERT ON static_routes FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'Reach the system limitation')
    WHERE (SELECT COUNT(destination) FROM static_routes) >= 1000;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.destination NOT LIKE '%.%.%.%' OR LENGTH(NEW.destination) NOT BETWEEN 7 AND 18)
    OR (NEW.gateway NOT LIKE '%.%.%.%' OR LENGTH(NEW.gateway) NOT BETWEEN 7 AND 15)
    OR (NEW.description IS NOT NULL AND LENGTH(NEW.description) > 128);
END;

DROP TRIGGER before_update_static_routes;
CREATE TRIGGER before_update_static_routes BEFORE UPDATE ON static_routes FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'Reach the system limitation')
    WHERE (SELECT COUNT(destination) FROM static_routes) >= 1000;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.destination NOT LIKE '%.%.%.%' OR LENGTH(NEW.destination) NOT BETWEEN 7 AND 18)
    OR (NEW.gateway NOT LIKE '%.%.%.%' OR LENGTH(NEW.gateway) NOT BETWEEN 7 AND 15)
    OR (NEW.description IS NOT NULL AND LENGTH(NEW.description) > 128);
END;

DROP TRIGGER after_insert_static_route_track;
CREATE TRIGGER after_insert_static_route_track AFTER INSERT ON static_route_track FOR EACH ROW
BEGIN
    SELECT NOTIFY("UPDATE$STATICROUTE", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17;
END;

DROP TRIGGER after_update_static_route_track;
CREATE TRIGGER after_update_static_route_track AFTER UPDATE ON static_route_track FOR EACH ROW
BEGIN
    SELECT NOTIFY("UPDATE$STATICROUTE", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17;
END;


/*
* cluster forwards
*/
DROP TRIGGER after_insert_cluster_forwards;
CREATE TRIGGER after_insert_cluster_forwards AFTER INSERT ON cluster_forwards FOR EACH ROW
BEGIN
    SELECT NOTIFY("UPDATE$FORWARD", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17;
END;

DROP TRIGGER after_update_cluster_forwards;
CREATE TRIGGER after_update_cluster_forwards AFTER UPDATE ON cluster_forwards FOR EACH ROW
BEGIN
    SELECT NOTIFY("UPDATE$FORWARD", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17;
END;

/*
* local dns triggers
*/
DROP TRIGGER before_insert_dns_names;
CREATE TRIGGER before_insert_dns_names BEFORE INSERT ON dns_names FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'Reach the system limitation')
    WHERE (SELECT COUNT(id) FROM dns_names) >= 100;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (LENGTH(NEW.name) NOT BETWEEN 1 AND 64)
    OR (NEW.description IS NOT NULL AND LENGTH(NEW.description) > 128);
END;

DROP TRIGGER before_update_dns_names;
CREATE TRIGGER before_update_dns_names BEFORE UPDATE ON dns_names FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'Reach the system limitation')
    WHERE (SELECT COUNT(id) FROM dns_names) >= 100;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (LENGTH(NEW.name) NOT BETWEEN 1 AND 64)
    OR (NEW.description IS NOT NULL AND LENGTH(NEW.description) > 128);
END;

DROP TRIGGER after_delete_dns_names;
CREATE TRIGGER after_delete_dns_names AFTER DELETE ON dns_names FOR EACH ROW
BEGIN
    DELETE FROM dns_ips WHERE dns_nameid = OLD.id;
END;

DROP TRIGGER before_insert_dns_ips;
CREATE TRIGGER before_insert_dns_ips BEFORE INSERT ON dns_ips FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'Reach the system limitation')
    WHERE (SELECT COUNT(ip) FROM dns_ips WHERE dns_nameid = NEW.dns_nameid) >= 10;
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM dns_names WHERE id = NEW.dns_nameid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.ip NOT LIKE '%.%.%.%' OR LENGTH(NEW.ip) NOT BETWEEN 7 AND 15);
END;

DROP TRIGGER before_update_dns_ips;
CREATE TRIGGER before_update_dns_ips BEFORE UPDATE ON dns_ips FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'Reach the system limitation')
    WHERE (SELECT COUNT(ip) FROM dns_ips WHERE dns_nameid = NEW.dns_nameid) >= 10;
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM dns_names WHERE id = NEW.dns_nameid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.ip NOT LIKE '%.%.%.%' OR LENGTH(NEW.ip) NOT BETWEEN 7 AND 15);
END;

DROP TRIGGER before_insert_dns_hosts;
CREATE TRIGGER before_insert_dns_hosts BEFORE INSERT ON dns_hosts FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'Reach the system limitation')
    WHERE (SELECT COUNT(ip) FROM dns_hosts) >= 100;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.ip NOT LIKE '%.%.%.%' OR LENGTH(NEW.ip) NOT BETWEEN 7 AND 15)
    OR (LENGTH(NEW.name) NOT BETWEEN 1 AND 64)
    OR (NEW.description IS NOT NULL AND LENGTH(NEW.description) > 128);
END;

DROP TRIGGER before_update_dns_hosts;
CREATE TRIGGER before_update_dns_hosts BEFORE UPDATE ON dns_hosts FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'Reach the system limitation')
    WHERE (SELECT COUNT(ip) FROM dns_hosts) >= 100;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.ip NOT LIKE '%.%.%.%' OR LENGTH(NEW.ip) NOT BETWEEN 7 AND 15)
    OR (LENGTH(NEW.name) NOT BETWEEN 1 AND 64)
    OR (NEW.description IS NOT NULL AND LENGTH(NEW.description) > 128);
END;

DROP TRIGGER after_insert_dns_track;
CREATE TRIGGER after_insert_dns_track AFTER INSERT ON dns_track FOR EACH ROW
BEGIN
    SELECT NOTIFY("UPDATE$DNS", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17;
END;

DROP TRIGGER after_update_dns_track;
CREATE TRIGGER after_update_dns_track AFTER UPDATE ON dns_track FOR EACH ROW
BEGIN
    SELECT NOTIFY("UPDATE$DNS", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17;
END;

/*
*   dhcp
*/
DROP TRIGGER before_insert_cluster_dhcps;
CREATE TRIGGER before_insert_cluster_dhcps BEFORE INSERT ON cluster_dhcps FOR EACH ROW
    WHEN (SELECT COUNT(*) FROM cluster_dhcp) <> 0
BEGIN
    SELECT RAISE(ROLLBACK, 'Forbid to insert or delete orphan record');
END;

DROP TRIGGER before_delete_cluster_dhcps;
CREATE TRIGGER before_delete_cluster_dhcps BEFORE DELETE ON cluster_dhcps FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'Forbid to delete immortal record');
END;

DROP TRIGGER after_delete_cluster_dhcps;
CREATE TRIGGER after_delete_cluster_dhcps AFTER DELETE on cluster_dhcps FOR EACH ROW
BEGIN
    DELETE FROM cluster_dhcp_ranges WHERE cluster_chcpid = OLD.id;
END;

DROP TRIGGER before_update_cluster_dhcps;
CREATE TRIGGER before_update_cluster_dhcps BEFORE UPDATE ON cluster_dhcps FOR EACH ROW
BEGIN
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.if_enable NOT IN('TRUE', 'FALSE'))
    OR (NEW.if_limited NOT IN('TRUE', 'FALSE'));
END;

DROP TRIGGER after_update_cluster_dhcps;
CREATE TRIGGER after_update_cluster_dhcps AFTER UPDATE ON cluster_dhcps FOR EACH ROW
BEGIN
    UPDATE pubsubnet_permissions SET if_fixed_ip='FALSE', fixed_ip=NULL;
    SELECT NOTIFY("UPDATE$DHCP", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17;
END;

DROP TRIGGER before_insert_cluster_dhcp_ranges;
CREATE TRIGGER before_insert_cluster_dhcp_ranges BEFORE INSERT ON cluster_dhcp_ranges FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM cluster_dhcps WHERE id = NEW.cluster_dhcpid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.ip_from IS NOT NULL AND (NEW.ip_from NOT LIKE '%.%.%.%' OR LENGTH(NEW.ip_from) NOT BETWEEN 7 AND 15))
    OR (NEW.ip_to IS NOT NULL AND (NEW.ip_to NOT LIKE '%.%.%.%' OR LENGTH(NEW.ip_to) NOT BETWEEN 7 AND 15));
    -- consistency check
    -- SELECT RAISE(ROLLBACK, 'Violate consistency check constraint')
    -- WHERE (NEW.ip_from > NEW.ip_to);
END;

DROP TRIGGER before_update_cluster_dhcp_ranges;
CREATE TRIGGER before_update_cluster_dhcp_ranges BEFORE UPDATE ON cluster_dhcp_ranges FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM cluster_dhcps WHERE id = NEW.cluster_dhcpid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.ip_from IS NOT NULL AND (NEW.ip_from NOT LIKE '%.%.%.%' OR LENGTH(NEW.ip_from) NOT BETWEEN 7 AND 15))
    OR (NEW.ip_to IS NOT NULL AND (NEW.ip_to NOT LIKE '%.%.%.%' OR LENGTH(NEW.ip_to) NOT BETWEEN 7 AND 15));
    -- consistency check
    -- SELECT RAISE(ROLLBACK, 'Violate consistency check constraint')
    -- WHERE (NEW.ip_from > NEW.ip_to);
END;

/*
* port forwarding
*/
DROP TRIGGER after_insert_cluster_forwards;
CREATE TRIGGER after_insert_cluster_forwards AFTER INSERT ON cluster_forwards FOR EACH ROW
BEGIN
    SELECT NOTIFY("UPDATE$FORWARD", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17;
END;

DROP TRIGGER after_update_cluster_forwards;
CREATE TRIGGER after_update_cluster_forwards AFTER UPDATE ON cluster_forwards FOR EACH ROW
BEGIN
    SELECT NOTIFY("UPDATE$FORWARD", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17;
END;

DROP TRIGGER after_delete_cluster_forwards;
CREATE TRIGGER after_delete_cluster_forwards AFTER DELETE on cluster_forwards FOR EACH ROW
BEGIN
    DELETE FROM cluster_forwardips WHERE cluster_forwardid = OLD.id;
    SELECT NOTIFY("UPDATE$FORWARD", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17;
END;

/*
* system rootca
*/
DROP TRIGGER after_insert_system_rootca;
CREATE TRIGGER after_insert_system_rootca AFTER INSERT ON system_rootca FOR EACH ROW
BEGIN
    UPDATE cert_roots SET if_enable='TRUE' WHERE type=2;
END;

DROP TRIGGER after_delete_system_rootca;
CREATE TRIGGER after_delete_system_rootca AFTER DELETE ON system_rootca FOR EACH ROW
BEGIN
    UPDATE cert_roots SET if_enable='FALSE' WHERE type=2;
END;

/*
*   snmp
*/
DROP TRIGGER before_update_snmp_system;
CREATE TRIGGER before_update_snmp_system BEFORE UPDATE ON snmp_system FOR EACH ROW
BEGIN
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (LENGTH(NEW.syscontact) NOT BETWEEN 1 AND 128)
    OR (LENGTH(NEW.syslocation) NOT BETWEEN 1 AND 128)
    OR (LENGTH(NEW.communityname) NOT BETWEEN 1 AND 64);
END;

DROP TRIGGER after_update_snmp_system;
CREATE TRIGGER after_update_snmp_system AFTER UPDATE ON snmp_system FOR EACH ROW
BEGIN
    SELECT NOTIFY("UPDATE$SNMP", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17;
END;

DROP TRIGGER before_insert_snmp_filters;
CREATE TRIGGER before_insert_snmp_filters BEFORE INSERT ON snmp_filters FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM static_filter_types WHERE id = NEW.filter_typeid) IS NULL
    OR (SELECT id FROM static_inet_protocols WHERE id = NEW.inet_protocolid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.subnet NOT LIKE '%.%.%.%/%' OR LENGTH(NEW.subnet) NOT BETWEEN 9 AND 18)
    OR (NEW.if_accept NOT IN('TRUE', 'FALSE'));
END;

DROP TRIGGER before_update_snmp_filters;
CREATE TRIGGER before_update_snmp_filters BEFORE UPDATE ON snmp_filters FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM static_filter_types WHERE id = NEW.filter_typeid) IS NULL
    OR (SELECT id FROM static_inet_protocols WHERE id = NEW.inet_protocolid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.subnet NOT LIKE '%.%.%.%/%' OR LENGTH(NEW.subnet) NOT BETWEEN 9 AND 18)
    OR (NEW.if_accept NOT IN('TRUE', 'FALSE'));
END;

/*
* ddmz
*/
DROP TRIGGER after_update_ddmz_tables;
CREATE TRIGGER after_update_ddmz_tables AFTER UPDATE ON ddmz_tables FOR EACH ROW
BEGIN
    SELECT NOTIFY("UPDATE$DDMZ", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17;
END;

/*
*   uuid
*/
DROP TRIGGER before_insert_uuid_indexes;
CREATE TRIGGER before_insert_uuid_indexes BEFORE INSERT ON uuid_indexes FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'Reach the system limitation')
    WHERE (SELECT COUNT(id) FROM uuid_indexes) >= 1000;
END;

DROP TRIGGER after_delete_uuid_providers;
CREATE TRIGGER after_delete_uuid_providers AFTER UPDATE ON uuid_providers FOR EACH ROW
BEGIN
    DELETE FROM uuid_indexes WHERE id IN ( SELECT uuid_indexid FROM uuid_uuids WHERE uuid_uuids.uuid_providerid = OLD.id);
    DELETE FROM uuid_indexes WHERE id IN ( SELECT uuid_indexid FROM uuid_accounts WHERE uuid_accounts.uuid_providerid = OLD.id);
END;

DROP TRIGGER after_insert_uuid_indexes;
CREATE TRIGGER after_insert_uuid_indexes AFTER INSERT ON uuid_indexes FOR EACH ROW
BEGIN
    UPDATE system_sequences SET next_value = NEW.id + increment_by WHERE sequence_name='uuid_indexes' AND next_value <> 0;
END;

DROP TRIGGER after_update_uuid_indexes;
CREATE TRIGGER after_update_uuid_indexes AFTER UPDATE ON uuid_indexes FOR EACH ROW
    WHEN OLD.serial <> NEW.serial
BEGIN
    UPDATE system_sequences SET next_value = NEW.id + increment_by WHERE sequence_name='uuid_indexes' AND next_value <> 0;
END;

DROP TRIGGER after_delete_uuid_indexes;
CREATE TRIGGER after_delete_uuid_indexes AFTER DELETE ON uuid_indexes FOR EACH ROW
BEGIN
    DELETE FROM uuid_contacts WHERE uuid_indexid = OLD.id;
    DELETE FROM uuid_tracks WHERE uuid_indexid = OLD.id;
    DELETE FROM uuid_attributes WHERE uuid_indexid = OLD.id;
    DELETE FROM uuid_certs WHERE uuid_indexid = OLD.id;
    DELETE FROM uuid_uuids WHERE uuid_indexid = OLD.id;
    DELETE FROM uuid_accounts WHERE uuid_indexid = OLD.id;
END;

DROP TRIGGER after_delete_uuid_certs;
CREATE TRIGGER after_delete_uuid_certs AFTER DELETE ON uuid_certs FOR EACH ROW
BEGIN
    DELETE FROM uuid_account_certs WHERE uucert_indexid = OLD.uuid_indexid;
END;

DROP TRIGGER after_delete_uuid_accounts;
CREATE TRIGGER after_delete_uuid_accounts AFTER DELETE ON uuid_accounts FOR EACH ROW
BEGIN
    DELETE FROM uuid_account_cookies WHERE uuaccount_indexid = OLD.uuid_indexid;
    DELETE FROM uuid_account_certs WHERE uuaccount_indexid = OLD.uuid_indexid;
END;

DROP TRIGGER after_delete_uuid_account_certs;
CREATE TRIGGER after_delete_uuid_account_certs AFTER DELETE ON uuid_account_certs FOR EACH ROW
BEGIN
    DELETE FROM uuid_indexes WHERE id = OLD.uucert_indexid;
END;

/*
*   log settings
*/
DROP TRIGGER after_update_log_alert;
CREATE TRIGGER after_update_log_alert AFTER UPDATE ON log_alert FOR EACH ROW
BEGIN
    SELECT NOTIFY("UPDATE$LOGSET", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17;
END;

/*
* authn/authz/attr server
*/
DROP TRIGGER before_insert_server_servers;
CREATE TRIGGER before_insert_server_servers BEFORE INSERT ON server_servers FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'Reach the system limitation')
    WHERE (SELECT COUNT(id) FROM server_servers) >= 100;
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM static_auth_types WHERE id = NEW.auth_typeid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (LENGTH(NEW.name) NOT BETWEEN 1 AND 64)
    OR (NEW.if_visible NOT IN('TRUE', 'FALSE'))
    OR (NEW.if_identity NOT IN('TRUE', 'FALSE'))
    OR (NEW.description IS NOT NULL AND LENGTH(NEW.description) > 128);
END;

DROP TRIGGER before_update_server_servers;
CREATE TRIGGER before_update_server_servers BEFORE UPDATE ON server_servers FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'Reach the system limitation')
    WHERE (SELECT COUNT(id) FROM server_servers) >= 100;
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM static_auth_types WHERE id = NEW.auth_typeid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (LENGTH(NEW.name) NOT BETWEEN 1 AND 64)
    OR (NEW.if_visible NOT IN('TRUE', 'FALSE'))
    OR (NEW.if_identity NOT IN('TRUE', 'FALSE'))
    OR (NEW.description IS NOT NULL AND LENGTH(NEW.description) > 128);
END;

DROP TRIGGER before_delete_server_servers;
CREATE TRIGGER before_delete_server_servers BEFORE DELETE ON server_servers FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'Forbid to delete immortal record') WHERE OLD.id <= 1;
END;

DROP TRIGGER after_delete_server_servers;
CREATE TRIGGER after_delete_server_servers AFTER DELETE ON server_servers FOR EACH ROW
BEGIN
    DELETE FROM server_tracks WHERE server_serverid = OLD.id;
    DELETE FROM server_winnts WHERE server_serverid = OLD.id;
    DELETE FROM server_winads WHERE server_serverid = OLD.id;
    DELETE FROM server_ldaps WHERE server_serverid = OLD.id;
    DELETE FROM server_radiuses WHERE server_serverid = OLD.id;
    DELETE FROM server_aces WHERE server_serverid = OLD.id;
    DELETE FROM server_customs WHERE server_serverid = OLD.id;
    DELETE FROM server_ldap_bases WHERE server_serverid = OLD.id;
    DELETE FROM server_ldap_certs WHERE server_serverid = OLD.id;
    -- local users
    DELETE FROM local_users WHERE server_serverid = OLD.id;
    DELETE FROM local_groups WHERE server_serverid = OLD.id;
    -- realms
    DELETE FROM realm_realms WHERE auth_serverid = OLD.id OR attr_serverid = OLD.id;
END;

DROP TRIGGER after_update_server_winads;
CREATE TRIGGER after_update_server_winads AFTER UPDATE ON server_winads FOR EACH ROW
BEGIN
    SELECT NOTIFY("UPDATE$REALM", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17 AND NEW.if_sso <> OLD.if_sso;
END;

DROP TRIGGER after_update_server_ldaps;
CREATE TRIGGER after_update_server_ldaps AFTER UPDATE ON server_ldaps FOR EACH ROW
BEGIN
    SELECT NOTIFY("UPDATE$REALM", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17 AND NEW.if_sso <> OLD.if_sso;
END;

/*
* local_users and local_groups update, delete trigger
*/
DROP TRIGGER before_insert_local_users;
CREATE TRIGGER before_insert_local_users BEFORE INSERT ON local_users FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'Reach the system limitation')
    WHERE (SELECT COUNT(id) FROM local_users WHERE server_serverid=NEW.server_serverid) >= 1000;
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM server_servers WHERE id = NEW.server_serverid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (LENGTH(NEW.name) NOT BETWEEN 1 AND 64)
    OR (LENGTH(NEW.password) NOT BETWEEN 1 AND 128)
    OR (NEW.description IS NOT NULL AND LENGTH(NEW.description) > 128);
END;

DROP TRIGGER before_update_local_users;
CREATE TRIGGER before_update_local_users BEFORE UPDATE ON local_users FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM server_servers WHERE id = NEW.server_serverid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (LENGTH(NEW.name) NOT BETWEEN 1 AND 64)
    OR (LENGTH(NEW.password) NOT BETWEEN 1 AND 128)
    OR (NEW.description IS NOT NULL AND LENGTH(NEW.description) > 128);
    -- admin user
    SELECT RAISE(ROLLBACK, 'Forbid to change admin user')
    WHERE OLD.server_serverid = 1 AND OLD.name = 'admin' AND NEW.name <> 'admin';
END;

/*
DROP TRIGGER before_delete_local_users;
CREATE TRIGGER before_delete_local_users BEFORE DELETE ON local_users FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'Forbid to delete immortal record')
    WHERE OLD.server_serverid = 1 AND OLD.name = 'admin';
END;
*/

DROP TRIGGER after_update_local_users;
CREATE TRIGGER after_update_local_users AFTER UPDATE ON local_users FOR EACH ROW
BEGIN
    UPDATE pubapp_tracks SET created_by = NEW.name WHERE OLD.server_serverid = 1 AND created_by = OLD.name;
    UPDATE pubapp_permissions SET name = NEW.name WHERE auth_levelid = 1 AND name = OLD.name AND
        realm_realmid IN (SELECT id FROM realm_realms WHERE attr_serverid = OLD.server_serverid);
    UPDATE pubweb_permissions SET name = NEW.name WHERE auth_levelid = 1 AND name = OLD.name AND
        realm_realmid IN (SELECT id FROM realm_realms WHERE attr_serverid = OLD.server_serverid);
    UPDATE pubsubnet_permissions SET name = NEW.name WHERE auth_levelid = 1 AND name = OLD.name AND
        realm_realmid IN (SELECT id FROM realm_realms WHERE attr_serverid = OLD.server_serverid);
    UPDATE pubsite_permissions SET name = NEW.name WHERE auth_levelid = 1 AND name = OLD.name AND
        realm_realmid IN (SELECT id FROM realm_realms WHERE attr_serverid = OLD.server_serverid);
    SELECT NOTIFY("UPDATE$PASSWORD", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17 AND NEW.server_serverid = 1 AND NEW.name = 'admin' AND NEW.password <> OLD.password;
END;

DROP TRIGGER after_delete_local_users;
CREATE TRIGGER after_delete_local_users AFTER DELETE ON local_users FOR EACH ROW
BEGIN
    DELETE FROM local_user_attributes WHERE local_userid = OLD.id;
    DELETE FROM local_user_groups WHERE local_userid = OLD.id;
    DELETE FROM acl_users WHERE local_userid = OLD.id;
    DELETE FROM pubapp_tracks WHERE OLD.server_serverid = 1 AND created_by = OLD.name;
    DELETE FROM pubapp_permissions WHERE auth_levelid = 1 AND name = OLD.name AND
        realm_realmid IN (SELECT id FROM realm_realms WHERE attr_serverid = OLD.server_serverid);
    DELETE FROM pubweb_permissions WHERE auth_levelid = 1 AND name = OLD.name AND
        realm_realmid IN (SELECT id FROM realm_realms WHERE attr_serverid = OLD.server_serverid);
    DELETE FROM pubsubnet_permissions WHERE auth_levelid = 1 AND name = OLD.name AND
        realm_realmid IN (SELECT id FROM realm_realms WHERE attr_serverid = OLD.server_serverid);
    DELETE FROM pubsite_permissions WHERE auth_levelid = 1 AND name = OLD.name AND
        realm_realmid IN (SELECT id FROM realm_realms WHERE attr_serverid = OLD.server_serverid);
END;

-- local user attribute
DROP TRIGGER before_insert_local_user_attributes;
CREATE TRIGGER before_insert_local_user_attributes BEFORE INSERT ON local_user_attributes FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM local_users WHERE id = NEW.local_userid) IS NULL
    OR (SELECT id FROM static_user_attributes WHERE id = NEW.user_attributeid) IS NULL;
END;

DROP TRIGGER before_update_local_user_attributes;
CREATE TRIGGER before_update_local_user_attributes BEFORE UPDATE ON local_user_attributes FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM local_users WHERE id = NEW.local_userid) IS NULL
    OR (SELECT id FROM static_user_attributes WHERE id = NEW.user_attributeid) IS NULL;
END;

-- local group
DROP TRIGGER before_insert_local_groups;
CREATE TRIGGER before_insert_local_groups BEFORE INSERT ON local_groups FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'Reach the system limitation')
    WHERE (SELECT COUNT(id) FROM local_groups WHERE server_serverid=NEW.server_serverid) >= 1000;
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM server_servers WHERE id = NEW.server_serverid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (LENGTH(NEW.name) NOT BETWEEN 1 AND 64)
    OR (NEW.description IS NOT NULL AND LENGTH(NEW.description) > 128);
END;

DROP TRIGGER before_update_local_groups;
CREATE TRIGGER before_update_local_groups BEFORE UPDATE ON local_groups FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM server_servers WHERE id = NEW.server_serverid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (LENGTH(NEW.name) NOT BETWEEN 1 AND 64)
    OR (NEW.description IS NOT NULL AND LENGTH(NEW.description) > 128);
END;

DROP TRIGGER after_update_local_groups;
CREATE TRIGGER after_update_local_groups AFTER UPDATE ON local_groups FOR EACH ROW
BEGIN
    UPDATE pubapp_permissions SET name = NEW.name WHERE auth_levelid = 2 AND name = OLD.name AND
        realm_realmid IN (SELECT id FROM realm_realms WHERE attr_serverid = OLD.server_serverid);
    UPDATE pubweb_permissions SET name = NEW.name WHERE auth_levelid = 2 AND name = OLD.name AND
        realm_realmid IN (SELECT id FROM realm_realms WHERE attr_serverid = OLD.server_serverid);
    UPDATE pubsubnet_permissions SET name = NEW.name WHERE auth_levelid = 2 AND name = OLD.name AND
        realm_realmid IN (SELECT id FROM realm_realms WHERE attr_serverid = OLD.server_serverid);
    UPDATE pubsite_permissions SET name = NEW.name WHERE auth_levelid = 2 AND name = OLD.name AND
        realm_realmid IN (SELECT id FROM realm_realms WHERE attr_serverid = OLD.server_serverid);
END;

DROP TRIGGER after_delete_local_groups;
CREATE TRIGGER after_delete_local_groups AFTER DELETE ON local_groups FOR EACH ROW
BEGIN
    DELETE FROM local_user_groups WHERE local_groupid = OLD.id;
    DELETE FROM local_group_groups WHERE local_groupid = OLD.id OR parent_groupid = OLD.id;
    DELETE FROM acl_groups WHERE local_groupid = OLD.id;
    DELETE FROM pubapp_permissions WHERE auth_levelid = 2 AND name = OLD.name AND
        realm_realmid IN (SELECT id FROM realm_realms WHERE attr_serverid = OLD.server_serverid);
    DELETE FROM pubweb_permissions WHERE auth_levelid = 2 AND name = OLD.name AND
        realm_realmid IN (SELECT id FROM realm_realms WHERE attr_serverid = OLD.server_serverid);
    DELETE FROM pubsubnet_permissions WHERE auth_levelid = 2 AND name = OLD.name AND
        realm_realmid IN (SELECT id FROM realm_realms WHERE attr_serverid = OLD.server_serverid);
    DELETE FROM pubsite_permissions WHERE auth_levelid = 2 AND name = OLD.name AND
        realm_realmid IN (SELECT id FROM realm_realms WHERE attr_serverid = OLD.server_serverid);
END;

DROP TRIGGER before_insert_local_user_groups;
CREATE TRIGGER before_insert_local_user_groups BEFORE INSERT ON local_user_groups FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM local_users WHERE id = NEW.local_userid) IS NULL
    OR (SELECT id FROM local_groups WHERE id = NEW.local_groupid) IS NULL;
END;

DROP TRIGGER before_update_local_user_groups;
CREATE TRIGGER before_update_local_user_groups BEFORE UPDATE ON local_user_groups FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM local_users WHERE id = NEW.local_userid) IS NULL
    OR (SELECT id FROM local_groups WHERE id = NEW.local_groupid) IS NULL;
END;

DROP TRIGGER before_insert_local_group_groups;
CREATE TRIGGER before_insert_local_group_groups BEFORE INSERT ON local_group_groups FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM local_groups WHERE id = NEW.local_groupid) IS NULL
    OR (SELECT id FROM local_groups WHERE id = NEW.parent_groupid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.local_groupid = NEW.parent_groupid)
    OR (SELECT local_groupid FROM local_group_groups WHERE local_groupid = NEW.parent_groupid AND parent_groupid = NEW.local_groupid) IS NOT NULL;
END;

DROP TRIGGER before_update_local_group_groups;
CREATE TRIGGER before_update_local_group_groups BEFORE UPDATE ON local_group_groups FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM local_groups WHERE id = NEW.local_groupid) IS NULL
    OR (SELECT id FROM local_groups WHERE id = NEW.parent_groupid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.local_groupid = NEW.parent_groupid)
    OR (SELECT local_groupid FROM local_group_groups WHERE local_groupid = NEW.parent_groupid AND parent_groupid = NEW.local_groupid) IS NOT NULL;
END;

/*
* certificate
*/
DROP TRIGGER before_insert_cert_roots;
CREATE TRIGGER before_insert_cert_roots BEFORE INSERT ON cert_roots FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'Reach the system limitation')
    WHERE (SELECT COUNT(id) FROM cert_roots) >= 10;
END;

DROP TRIGGER after_delete_cert_roots;
CREATE TRIGGER after_delete_cert_roots AFTER DELETE ON cert_roots FOR EACH ROW
BEGIN
    DELETE FROM cert_crls WHERE cert_rootid = OLD.id;
    DELETE FROM realm_policy_certs WHERE cert_rootid = OLD.id;
    DELETE FROM pubapp_policy_certs WHERE cert_rootid = OLD.id;
END;

/*
* role
*/
DROP TRIGGER before_insert_role_roles;
CREATE TRIGGER before_insert_role_roles BEFORE INSERT ON role_roles FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'Reach the system limitation')
    WHERE (SELECT COUNT(id) FROM role_roles) >= 1000;
END;

DROP TRIGGER after_update_role_roles;
CREATE TRIGGER after_update_role_roles AFTER UPDATE ON role_roles FOR EACH ROW
BEGIN
    UPDATE pubapp_permissions SET name = NEW.name WHERE auth_levelid = 3 AND name = OLD.name;
    UPDATE pubweb_permissions SET name = NEW.name WHERE auth_levelid = 3 AND name = OLD.name;
    UPDATE pubsubnet_permissions SET name = NEW.name WHERE auth_levelid = 3 AND name = OLD.name;
    UPDATE pubsite_permissions SET name = NEW.name WHERE auth_levelid = 3 AND name = OLD.name;
END;

DROP TRIGGER after_delete_role_roles;
CREATE TRIGGER after_delete_role_roles AFTER DELETE ON role_roles FOR EACH ROW
BEGIN
    DELETE FROM realm_policy_rolemaps WHERE role_roleid = OLD.id;
    -- permissions
    DELETE FROM pubapp_permissions WHERE auth_levelid = 3 AND name = OLD.name;
    DELETE FROM pubweb_permissions WHERE auth_levelid = 1 AND name = OLD.name;
    DELETE FROM pubsubnet_permissions WHERE auth_levelid = 1 AND name = OLD.name;
    DELETE FROM pubsite_permissions WHERE auth_levelid = 1 AND name = OLD.name;
END;

/*
* host checker
*/
DROP TRIGGER before_insert_check_checkers;
CREATE TRIGGER before_insert_check_checkers BEFORE INSERT ON check_checkers FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'Reach the system limitation')
    WHERE (SELECT COUNT(id) FROM check_checkers) >= 100;
END;

DROP TRIGGER after_delete_check_checkers;
CREATE TRIGGER after_delete_check_checkers AFTER DELETE ON check_checkers FOR EACH ROW
BEGIN
    DELETE FROM check_rules WHERE check_checkerid = OLD.id;
    DELETE FROM realm_policy_checks WHERE check_checkerid = OLD.id;
    DELETE FROM pubapp_policy_checks WHERE check_checkerid = OLD.id;
END;

DROP TRIGGER after_delete_check_rules;
CREATE TRIGGER after_delete_check_rules AFTER DELETE ON check_rules FOR EACH ROW
BEGIN
    DELETE FROM check_sets WHERE check_ruleid = OLD.id;
END;

DROP TRIGGER after_delete_check_sets;
CREATE TRIGGER after_delete_check_sets AFTER DELETE ON check_sets FOR EACH ROW
BEGIN
    DELETE FROM check_ports WHERE check_setid = OLD.id;
    DELETE FROM check_csums WHERE check_setid = OLD.id;
END;

/*
 * dynamics password
 */
DROP TRIGGER after_delete_server_dynamics_passwords;
CREATE TRIGGER after_delete_server_dynamics_passwords AFTER DELETE ON server_dynamics_passwords FOR EACH ROW
BEGIN
    DELETE FROM realm_policy_dynamics_passwords WHERE dynamics_serverid = OLD.id;
END;

/*
* realms
*/
DROP TRIGGER before_insert_realm_realms;
CREATE TRIGGER before_insert_realm_realms BEFORE INSERT ON realm_realms FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'Reach the system limitation')
    WHERE (SELECT COUNT(id) FROM realm_realms) >= 100;
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM server_servers WHERE id = NEW.auth_serverid) IS NULL
    OR (SELECT id FROM server_servers WHERE id = NEW.attr_serverid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (LENGTH(NEW.name) NOT BETWEEN 1 AND 64)
    OR (NEW.if_limited NOT IN('TRUE', 'FALSE'))
    OR (NEW.description IS NOT NULL AND LENGTH(NEW.description) > 128);
END;

DROP TRIGGER after_insert_realm_realms;
CREATE TRIGGER after_insert_realm_realms AFTER INSERT ON realm_realms FOR EACH ROW
BEGIN
    SELECT NOTIFY("UPDATE$REALM", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17;
    INSERT INTO pubapp_permissions(pubapp_appid, realm_realmid, auth_levelid, if_grant) SELECT 3, NEW.id, 0, 'TRUE' FROM server_servers WHERE auth_typeid = 2 AND id = NEW.auth_serverid;
END;

DROP TRIGGER before_update_realm_realms;
CREATE TRIGGER before_update_realm_realms BEFORE UPDATE ON realm_realms FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'Reach the system limitation')
    WHERE (SELECT COUNT(id) FROM realm_realms) >= 100;
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM server_servers WHERE id = NEW.auth_serverid) IS NULL
    OR (SELECT id FROM server_servers WHERE id = NEW.attr_serverid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (LENGTH(NEW.name) NOT BETWEEN 1 AND 64)
    OR (NEW.if_limited NOT IN('TRUE', 'FALSE'))
    OR (NEW.if_sso NOT IN('TRUE', 'FALSE'))
    OR (NEW.description IS NOT NULL AND LENGTH(NEW.description) > 128);
END;

DROP TRIGGER after_update_realm_realms;
CREATE TRIGGER after_update_realm_realms AFTER UPDATE ON realm_realms FOR EACH ROW
BEGIN
    SELECT NOTIFY("UPDATE$REALM", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17;
    DELETE FROM pubapp_permissions WHERE pubapp_appid = 3 AND realm_realmid = NEW.id;
    INSERT INTO pubapp_permissions(pubapp_appid, realm_realmid, auth_levelid, if_grant) SELECT 3, NEW.id, 0, 'TRUE' FROM server_servers WHERE auth_typeid = 2 AND id = NEW.auth_serverid;
END;

DROP TRIGGER after_delete_realm_realms;
CREATE TRIGGER after_delete_realm_realms AFTER DELETE ON realm_realms FOR EACH ROW
BEGIN
    DELETE FROM realm_tracks WHERE realm_realmid = OLD.id;
    DELETE FROM realm_policies WHERE realm_realmid = OLD.id;
    DELETE FROM realm_policy_rolerules WHERE realm_realmid = OLD.id;
    DELETE FROM realm_policy_userrules WHERE realm_realmid = OLD.id;
    -- permissions
    DELETE FROM pubapp_permissions WHERE realm_realmid = OLD.id;
    DELETE FROM pubweb_permissions WHERE realm_realmid = OLD.id;
    DELETE FROM pubsubnet_permissions WHERE realm_realmid = OLD.id;
    DELETE FROM pubsite_permissions WHERE realm_realmid = OLD.id;
    -- notify
    SELECT NOTIFY("UPDATE$REALM", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17;
END;

DROP TRIGGER after_delete_realm_policies;
CREATE TRIGGER after_delete_realm_policies AFTER DELETE ON realm_policies FOR EACH ROW
BEGIN
    DELETE FROM realm_policy_certs WHERE realm_policyid = OLD.id;
    DELETE FROM realm_policy_checks WHERE realm_policyid = OLD.id;
    DELETE FROM realm_policy_filters WHERE realm_policyid = OLD.id;
    DELETE FROM realm_policy_macfilters WHERE realm_policyid = OLD.id;
    DELETE FROM realm_policy_dynamics_passwords WHERE realm_policyid = OLD.id;
END;

DROP TRIGGER after_delete_realm_policy_rolerules;
CREATE TRIGGER after_delete_realm_policy_rolerules AFTER DELETE ON realm_policy_rolerules FOR EACH ROW
BEGIN
    DELETE FROM realm_policy_rolemaps WHERE realm_policy_roleruleid = OLD.id;
END;

/*
* ACL
*/
DROP TRIGGER after_delete_acl_acos;
CREATE TRIGGER after_delete_acl_acos AFTER DELETE ON acl_acos FOR EACH ROW
BEGIN
    DELETE FROM acl_users WHERE acl_acoid = OLD.id;
    DELETE FROM acl_groups WHERE acl_acoid = OLD.id;
END;

DROP TRIGGER before_insert_acl_users;
CREATE TRIGGER before_insert_acl_users BEFORE INSERT ON acl_users FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM acl_acos WHERE id = NEW.acl_acoid) IS NULL
    OR (SELECT id FROM local_users WHERE id = NEW.local_userid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.permission NOT BETWEEN 0 AND 15);
END;

DROP TRIGGER before_update_acl_users;
CREATE TRIGGER before_update_acl_users BEFORE UPDATE ON acl_users FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM acl_acos WHERE id = NEW.acl_acoid) IS NULL
    OR (SELECT id FROM local_users WHERE id = NEW.local_userid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.permission NOT BETWEEN 0 AND 15);
END;

DROP TRIGGER before_insert_acl_groups;
CREATE TRIGGER before_insert_acl_groups BEFORE INSERT ON acl_groups FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM acl_acos WHERE id = NEW.acl_acoid) IS NULL
    OR (SELECT id FROM local_groups WHERE id = NEW.local_groupid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.permission NOT BETWEEN 0 AND 15);
END;

DROP TRIGGER before_update_acl_groups;
CREATE TRIGGER before_update_acl_groups BEFORE UPDATE ON acl_groups FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM acl_acos WHERE id = NEW.acl_acoid) IS NULL
    OR (SELECT id FROM local_groups WHERE id = NEW.local_groupid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.permission NOT BETWEEN 0 AND 15);
END;

/*
* publish service configuration
*/
DROP TRIGGER before_update_pub_options;
CREATE TRIGGER before_update_pub_options BEFORE UPDATE ON pub_options FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM static_service_types WHERE id = NEW.service_typeid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.if_multiple NOT IN('TRUE', 'FALSE'))
    OR (NEW.idle_time NOT BETWEEN 0 AND 2147483647)
    OR (NEW.max_time <> 0 AND NEW.max_time NOT BETWEEN 5 AND 2147483647);
END;

DROP TRIGGER after_update_pub_options;
CREATE TRIGGER after_update_pub_options AFTER UPDATE ON pub_options FOR EACH ROW
BEGIN
    -- UUApp
    SELECT NOTIFY("UPDATE$APPOPTION", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17 AND NEW.service_typeid=2;
    -- UUAdmin
    SELECT NOTIFY("UPDATE$ADMINOPTION", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17 AND NEW.service_typeid=3;
    -- UURemote
    SELECT NOTIFY("UPDATE$REMOTEOPTION", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17 AND NEW.service_typeid=4;
    -- UUSoft
    SELECT NOTIFY("UPDATE$SOFTOPTION", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17 AND NEW.service_typeid=5;
END;

DROP TRIGGER before_insert_pub_encrypt_algorithms;
CREATE TRIGGER before_insert_pub_encrypt_algorithms BEFORE INSERT ON pub_encrypt_algorithms FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM static_service_types WHERE id = NEW.service_typeid) IS NULL
    OR (SELECT id FROM static_encrypt_algorithms WHERE id = NEW.encrypt_algorithmid) IS NULL;
END;

DROP TRIGGER before_update_pub_encrypt_algorithms;
CREATE TRIGGER before_update_pub_encrypt_algorithms BEFORE UPDATE ON pub_encrypt_algorithms FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM static_service_types WHERE id = NEW.service_typeid) IS NULL
    OR (SELECT id FROM static_encrypt_algorithms WHERE id = NEW.encrypt_algorithmid) IS NULL;
END;

DROP TRIGGER before_insert_pub_hash_algorithms;
CREATE TRIGGER before_insert_pub_hash_algorithms BEFORE INSERT ON pub_hash_algorithms FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM static_service_types WHERE id = NEW.service_typeid) IS NULL
    OR (SELECT id FROM static_hash_algorithms WHERE id = NEW.hash_algorithmid) IS NULL;
END;

DROP TRIGGER before_update_pub_hash_algorithms;
CREATE TRIGGER before_update_pub_hash_algorithms BEFORE UPDATE ON pub_hash_algorithms FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM static_service_types WHERE id = NEW.service_typeid) IS NULL
    OR (SELECT id FROM static_hash_algorithms WHERE id = NEW.hash_algorithmid) IS NULL;
END;

-- pub_algorithm_tracks
DROP TRIGGER before_insert_pub_algorithm_tracks;
CREATE TRIGGER before_insert_pub_algorithm_tracks BEFORE INSERT ON pub_algorithm_tracks FOR EACH ROW
    WHEN (SELECT COUNT(*) FROM pub_algorithm_tracks) <> 0
BEGIN
    SELECT RAISE(ROLLBACK, 'Forbid to insert or delete orphan record');
END;

DROP TRIGGER before_delete_pub_algorithm_tracks;
CREATE TRIGGER before_delete_pub_algorithm_tracks BEFORE DELETE ON pub_algorithm_tracks FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'Forbid to insert or delete orphan record');
END;

DROP TRIGGER after_update_pub_algorithm_tracks;
CREATE TRIGGER after_update_pub_algorithm_tracks AFTER UPDATE ON pub_algorithm_tracks FOR EACH ROW
BEGIN
    SELECT NOTIFY("UPDATE$ENCRYPT", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17;
END;

/*
* publish application
*/
-- pubapp_apps
DROP TRIGGER before_insert_pubapp_apps;
CREATE TRIGGER before_insert_pubapp_apps BEFORE INSERT ON pubapp_apps FOR EACH ROW
BEGIN
    -- max 100 application
    SELECT RAISE(ROLLBACK, 'Reach the system limitation')
    WHERE (NEW.service_typeid = 3)
    OR (SELECT COUNT(id) FROM pubapp_apps WHERE service_typeid = NEW.service_typeid) >= 1000;
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM static_service_types WHERE id = NEW.service_typeid) IS NULL
    OR (SELECT id FROM static_server_types WHERE id = NEW.server_typeid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.service_typeid NOT IN(2, 3))
    OR (LENGTH(NEW.name) NOT BETWEEN 1 AND 64)
    OR (NEW.if_enable NOT IN('TRUE', 'FALSE'))
    OR (NEW.if_grant NOT IN('TRUE', 'FALSE'))
    OR (NEW.if_accept NOT IN('TRUE', 'FALSE'))
    OR (NEW.if_encrypt NOT IN('TRUE', 'FALSE'))
    OR (NEW.if_hash NOT IN('TRUE', 'FALSE'))
    OR (NEW.if_compress NOT IN('TRUE', 'FALSE'))
    OR (NEW.description IS NOT NULL AND LENGTH(NEW.description) > 128);
END;

DROP TRIGGER before_update_pubapp_apps;
CREATE TRIGGER before_update_pubapp_apps BEFORE UPDATE ON pubapp_apps FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM static_service_types WHERE id = NEW.service_typeid) IS NULL
    OR (SELECT id FROM static_server_types WHERE id = NEW.server_typeid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.service_typeid NOT IN(2, 3))
    OR (LENGTH(NEW.name) NOT BETWEEN 1 AND 64)
    OR (NEW.if_enable NOT IN('TRUE', 'FALSE'))
    OR (NEW.if_grant NOT IN('TRUE', 'FALSE'))
    OR (NEW.if_accept NOT IN('TRUE', 'FALSE'))
    OR (NEW.if_encrypt NOT IN('TRUE', 'FALSE'))
    OR (NEW.if_hash NOT IN('TRUE', 'FALSE'))
    OR (NEW.if_compress NOT IN('TRUE', 'FALSE'))
    OR (NEW.description IS NOT NULL AND LENGTH(NEW.description) > 128);
END;

DROP TRIGGER after_insert_pubapp_apps;
CREATE TRIGGER after_insert_pubapp_apps AFTER INSERT ON pubapp_apps FOR EACH ROW
BEGIN
    INSERT INTO pubapp_group_apps(pubapp_appid, priority) SELECT NEW.id, COALESCE(MAX(priority), 0)+ 1
        FROM pubapp_group_apps WHERE service_typeid = NEW.service_typeid AND pubapp_groupid = 0;
    UPDATE pubapp_apps SET server_bits = NEW.server_bits | 1 WHERE id=NEW.id AND NEW.server_typeid = 105 AND NEW.server_bits & 1 <> 1;
    UPDATE pubapp_apps SET server_bits = NEW.server_bits | 1 WHERE id=NEW.id AND NEW.server_typeid = 203 AND NEW.server_bits & 1 <> 1;
    UPDATE pubapp_apps SET server_bits = NEW.server_bits | 28 WHERE id=NEW.id AND NEW.server_typeid = 109 AND NEW.server_bits & 28 <> 28;
    UPDATE system_sequences SET next_value = NEW.id + increment_by WHERE NEW.id > 10000 AND sequence_name='pubapp_apps' AND next_value <> 0;
END;

DROP TRIGGER after_update_pubapp_apps;
CREATE TRIGGER after_update_pubapp_apps AFTER UPDATE ON pubapp_apps FOR EACH ROW
BEGIN
    SELECT NOTIFY("UPDATE$APPOPTION", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17 AND NEW.server_typeid = 405;
    UPDATE pubapp_apps SET server_bits = NEW.server_bits | 1 WHERE NEW.server_typeid = 105 AND NEW.server_bits & 1 <> 1 AND id=NEW.id;
END;

DROP TRIGGER after_delete_pubapp_apps;
CREATE TRIGGER after_delete_pubapp_apps AFTER DELETE ON pubapp_apps FOR EACH ROW
BEGIN
    SELECT NOTIFY("UPDATE$APPOPTION", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17 AND OLD.server_typeid = 405;
    DELETE FROM pubapp_parameters WHERE pubapp_appid = OLD.id;
    DELETE FROM pubapp_fbapps WHERE pubapp_appid = OLD.id;
    DELETE FROM pubapp_tracks WHERE pubapp_appid = OLD.id;
    DELETE FROM pubapp_permissions WHERE pubapp_appid = OLD.id;
    DELETE FROM pubapp_group_apps WHERE pubapp_appid = OLD.id;
    DELETE FROM pubapp_policies WHERE pubapp_appid = OLD.id;
    DELETE FROM pubapp_ports WHERE pubapp_appid = OLD.id;
    DELETE FROM pubapp_filters WHERE pubapp_appid = OLD.id;
END;

-- pubapp_parameters
DROP TRIGGER before_insert_pubapp_parameters;
CREATE TRIGGER before_insert_pubapp_parameters BEFORE INSERT ON pubapp_parameters FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM pubapp_apps WHERE id = NEW.pubapp_appid) IS NULL
    OR (SELECT id FROM static_platform_types WHERE id = NEW.platform_typeid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.if_enable NOT IN('TRUE', 'FALSE'))
    OR (NEW.path IS NOT NULL AND LENGTH(NEW.path) > 512)
    OR (NEW.parameter IS NOT NULL AND LENGTH(NEW.parameter) > 512);
END;

DROP TRIGGER before_update_pubapp_parameters;
CREATE TRIGGER before_update_pubapp_parameters BEFORE UPDATE ON pubapp_parameters FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM pubapp_apps WHERE id = NEW.pubapp_appid) IS NULL
    OR (SELECT id FROM static_platform_types WHERE id = NEW.platform_typeid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.if_enable NOT IN('TRUE', 'FALSE'))
    OR (NEW.path IS NOT NULL AND LENGTH(NEW.path) > 512)
    OR (NEW.parameter IS NOT NULL AND LENGTH(NEW.parameter) > 512);
END;

-- pubapp_fbapps
DROP TRIGGER before_insert_pubapp_fbapps;
CREATE TRIGGER before_insert_pubapp_fbapps BEFORE INSERT ON pubapp_fbapps FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM pubapp_apps WHERE id = NEW.pubapp_appid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.fs_type NOT IN('SMB', 'NFS', 'LCL', 'FTP'))
    OR (LENGTH(NEW.folder_name) NOT BETWEEN 1 AND 512);
END;

DROP TRIGGER before_update_pubapp_fbapps;
CREATE TRIGGER before_update_pubapp_fbapps BEFORE UPDATE ON pubapp_fbapps FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM pubapp_apps WHERE id = NEW.pubapp_appid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.fs_type NOT IN('SMB', 'NFS', 'LCL', 'FTP'))
    OR (LENGTH(NEW.folder_name) NOT BETWEEN 1 AND 512);
END;

-- pubapp_permissions
DROP TRIGGER before_insert_pubapp_permissions;
CREATE TRIGGER before_insert_pubapp_permissions BEFORE INSERT ON pubapp_permissions FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM pubapp_apps WHERE id = NEW.pubapp_appid) IS NULL
    OR (SELECT id FROM realm_realms WHERE id = NEW.realm_realmid) IS NULL
    OR (SELECT id FROM static_auth_levels WHERE id = NEW.auth_levelid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.if_grant NOT IN('TRUE', 'FALSE'))
    OR (NEW.auth_levelid <> 0 AND NEW.name IS NULL);
END;

DROP TRIGGER before_update_pubapp_permissions;
CREATE TRIGGER before_update_pubapp_permissions BEFORE UPDATE ON pubapp_permissions FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM pubapp_apps WHERE id = NEW.pubapp_appid) IS NULL
    OR (SELECT id FROM realm_realms WHERE id = NEW.realm_realmid) IS NULL
    OR (SELECT id FROM static_auth_levels WHERE id = NEW.auth_levelid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.if_grant NOT IN('TRUE', 'FALSE'))
    OR (NEW.auth_levelid <> 0 AND NEW.name IS NULL);
END;

DROP TRIGGER after_update_pubapp_permissions;
CREATE TRIGGER after_update_pubapp_permissions AFTER UPDATE ON pubapp_permissions FOR EACH ROW
    WHEN OLD.auth_levelid = 0 AND NEW.if_grant <> OLD.if_grant
BEGIN
    UPDATE pubapp_permissions SET if_grant=OLD.if_grant WHERE auth_levelid <> 0 AND pubapp_appid = OLD.pubapp_appid AND realm_realmid = OLD.realm_realmid;
END;

-- pubapp_groups
DROP TRIGGER before_insert_pubapp_groups;
CREATE TRIGGER before_insert_pubapp_groups BEFORE INSERT ON pubapp_groups FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'Reach the system limitation')
    WHERE (SELECT COUNT(id) FROM pubapp_groups) >= 100;
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM pubapp_groups WHERE id = NEW.parent_groupid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (LENGTH(NEW.name) NOT BETWEEN 1 AND 64)
    OR (NEW.description IS NOT NULL AND LENGTH(NEW.description) > 128);
END;

DROP TRIGGER before_update_pubapp_groups;
CREATE TRIGGER before_update_pubapp_groups BEFORE UPDATE ON pubapp_groups FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM pubapp_groups WHERE id = NEW.parent_groupid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (LENGTH(NEW.name) NOT BETWEEN 1 AND 64)
    OR (NEW.description IS NOT NULL AND LENGTH(NEW.description) > 128);
END;

DROP TRIGGER after_insert_pubapp_groups;
CREATE TRIGGER after_insert_pubapp_groups AFTER INSERT ON pubapp_groups FOR EACH ROW
BEGIN
    UPDATE pubapp_groups SET right_id = right_id + 2
        WHERE left_id < (SELECT left_id FROM pubapp_groups WHERE id = NEW.parent_groupid)
        AND right_id > (SELECT right_id FROM pubapp_groups WHERE id = NEW.parent_groupid);
    UPDATE pubapp_groups SET left_id = left_id + 2, right_id = right_id + 2
        WHERE left_id > (SELECT left_id FROM pubapp_groups WHERE id = NEW.parent_groupid)
        AND right_id > (SELECT right_id FROM pubapp_groups WHERE id = NEW.parent_groupid);
    UPDATE pubapp_groups SET left_id = (SELECT right_id FROM pubapp_groups WHERE id = NEW.parent_groupid),
        right_id = (SELECT right_id + 1 FROM pubapp_groups WHERE id = NEW.parent_groupid) WHERE id = NEW.id;
    UPDATE pubapp_groups SET right_id = right_id + 2 WHERE id = NEW.parent_groupid;
END;

DROP TRIGGER after_delete_pubapp_groups;
CREATE TRIGGER after_delete_pubapp_groups AFTER DELETE ON pubapp_groups FOR EACH ROW
BEGIN
    UPDATE pubapp_groups SET parent_groupid = OLD.id WHERE left_id > OLD.left_id AND right_id < OLD.right_id;
    DELETE FROM pubapp_group_apps WHERE pubapp_groupid IN (SELECT id FROM pubapp_groups WHERE parent_groupid = OLD.id);
    UPDATE pubapp_groups SET right_id = right_id - (SELECT (COUNT(id) + 1)*2 FROM pubapp_groups WHERE parent_groupid = OLD.id)
        WHERE left_id < OLD.left_id AND right_id > OLD.right_id;
    UPDATE pubapp_groups SET left_id = left_id - (SELECT (COUNT(id) + 1)*2 FROM pubapp_groups WHERE parent_groupid = OLD.id),
        right_id = right_id - (SELECT (COUNT(id) + 1)*2 FROM pubapp_groups WHERE parent_groupid = OLD.id)
        WHERE left_id > OLD.left_id AND right_id > OLD.right_id;
    DELETE FROM pubapp_groups WHERE parent_groupid = OLD.id;
    DELETE FROM pubapp_group_apps WHERE pubapp_groupid = OLD.id;
END;

DROP TRIGGER before_insert_pubapp_group_apps;
CREATE TRIGGER before_insert_pubapp_group_apps BEFORE INSERT ON pubapp_group_apps FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM static_service_types WHERE id = NEW.service_typeid) IS NULL
    OR (SELECT id FROM pubapp_apps WHERE id = NEW.pubapp_appid) IS NULL
    OR (SELECT id FROM pubapp_groups WHERE id = NEW.pubapp_groupid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.service_typeid NOT IN(2, 3));
END;

DROP TRIGGER before_update_pubapp_group_apps;
CREATE TRIGGER before_update_pubapp_group_apps BEFORE UPDATE ON pubapp_group_apps FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM static_service_types WHERE id = NEW.service_typeid) IS NULL
    OR (SELECT id FROM pubapp_apps WHERE id = NEW.pubapp_appid) IS NULL
    OR (SELECT id FROM pubapp_groups WHERE id = NEW.pubapp_groupid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.service_typeid NOT IN(2, 3));
END;

DROP TRIGGER after_delete_pubapp_group_apps;
CREATE TRIGGER after_delete_pubapp_group_apps AFTER DELETE ON pubapp_group_apps FOR EACH ROW
    WHEN (SELECT COUNT(*) FROM pubapp_group_apps WHERE pubapp_appid = OLD.pubapp_appid) = 0
    AND (SELECT id FROM pubapp_apps WHERE id = OLD.pubapp_appid) IS NOT NULL
BEGIN
    INSERT INTO pubapp_group_apps(pubapp_appid, priority) SELECT OLD.pubapp_appid, COALESCE(MAX(priority), 0)+ 1
        FROM pubapp_group_apps WHERE service_typeid = OLD.service_typeid AND pubapp_groupid = 0;
END;

-- pubapp_filters
DROP TRIGGER before_insert_pubapp_ports;
CREATE TRIGGER before_insert_pubapp_ports BEFORE INSERT ON pubapp_ports FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM pubapp_apps WHERE id = NEW.pubapp_appid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.port_start NOT BETWEEN 1 AND 65535)
    OR (NEW.port_end NOT BETWEEN 1 AND 65535);
    -- consistency check
    SELECT RAISE(ROLLBACK, 'Violate consistency check constraint')
    WHERE (NEW.port_start > NEW.port_end);
END;

DROP TRIGGER before_update_pubapp_ports;
CREATE TRIGGER before_update_pubapp_ports BEFORE UPDATE ON pubapp_ports FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM pubapp_apps WHERE id = NEW.pubapp_appid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.port_start NOT BETWEEN 1 AND 65535)
    OR (NEW.port_end NOT BETWEEN 1 AND 65535);
    -- consistency check
    SELECT RAISE(ROLLBACK, 'Violate consistency check constraint')
    WHERE (NEW.port_start > NEW.port_end);
END;

DROP TRIGGER before_insert_pubapp_filters;
CREATE TRIGGER before_insert_pubapp_filters BEFORE INSERT ON pubapp_filters FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM pubapp_apps WHERE id = NEW.pubapp_appid) IS NULL
    OR (SELECT id FROM static_filter_types WHERE id = NEW.filter_typeid) IS NULL
    OR (SELECT id FROM static_inet_protocols WHERE id = NEW.inet_protocolid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.name IS NOT NULL AND LENGTH(NEW.name) NOT BETWEEN 1 AND 64)
    OR (NEW.ip_from IS NOT NULL AND (NEW.ip_from NOT LIKE '%.%.%.%' OR LENGTH(NEW.ip_from) NOT BETWEEN 7 AND 15))
    OR (NEW.ip_to IS NOT NULL AND (NEW.ip_to NOT LIKE '%.%.%.%' OR LENGTH(NEW.ip_to) NOT BETWEEN 7 AND 15))
    OR (NEW.if_accept NOT IN('TRUE', 'FALSE'))
    OR (NEW.if_custom_port NOT IN('TRUE', 'FALSE'))
    OR (NEW.description IS NOT NULL AND LENGTH(NEW.description) > 128);
    -- consistency check
    SELECT RAISE(ROLLBACK, 'Violate consistency check constraint')
    WHERE (NEW.filter_typeid IN(0, 3) AND NEW.name IS NULL)
    OR (NEW.filter_typeid NOT IN(0, 3) AND (NEW.ip_from IS NULL OR NEW.ip_to IS NULL));
END;

DROP TRIGGER before_update_pubapp_filters;
CREATE TRIGGER before_update_pubapp_filters BEFORE UPDATE ON pubapp_filters FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM pubapp_apps WHERE id = NEW.pubapp_appid) IS NULL
    OR (SELECT id FROM static_filter_types WHERE id = NEW.filter_typeid) IS NULL
    OR (SELECT id FROM static_inet_protocols WHERE id = NEW.inet_protocolid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.name IS NOT NULL AND LENGTH(NEW.name) NOT BETWEEN 1 AND 64)
    OR (NEW.ip_from IS NOT NULL AND (NEW.ip_from NOT LIKE '%.%.%.%' OR LENGTH(NEW.ip_from) NOT BETWEEN 7 AND 15))
    OR (NEW.ip_to IS NOT NULL AND (NEW.ip_to NOT LIKE '%.%.%.%' OR LENGTH(NEW.ip_to) NOT BETWEEN 7 AND 15))
    OR (NEW.if_accept NOT IN('TRUE', 'FALSE'))
    OR (NEW.if_custom_port NOT IN('TRUE', 'FALSE'))
    OR (NEW.description IS NOT NULL AND LENGTH(NEW.description) > 128);
    -- consistency check
    SELECT RAISE(ROLLBACK, 'Violate consistency check constraint')
    WHERE (NEW.filter_typeid IN(0, 3) AND NEW.name IS NULL)
    OR (NEW.filter_typeid NOT IN(0, 3) AND (NEW.ip_from IS NULL OR NEW.ip_to IS NULL));
END;

DROP TRIGGER after_delete_pubapp_filters;
CREATE TRIGGER after_delete_pubapp_filters AFTER DELETE ON pubapp_filters FOR EACH ROW
BEGIN
    DELETE FROM pubapp_filter_ports WHERE pubapp_filterid = OLD.id;
END;

-- pubapp_policies
DROP TRIGGER before_insert_pubapp_policies;
CREATE TRIGGER before_insert_pubapp_policies BEFORE INSERT ON pubapp_policies FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM pubapp_apps WHERE id = NEW.pubapp_appid) IS NULL
    OR (SELECT id FROM static_policy_types WHERE id = NEW.policy_typeid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.if_enable NOT IN('TRUE', 'FALSE'))
    OR (NEW.if_accept NOT IN('TRUE', 'FALSE'))
    OR (NEW.rule IS NOT NULL AND LENGTH(NEW.rule) > 2048);
END;

DROP TRIGGER before_update_pubapp_policies;
CREATE TRIGGER before_update_pubapp_policies BEFORE UPDATE ON pubapp_policies FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM pubapp_apps WHERE id = NEW.pubapp_appid) IS NULL
    OR (SELECT id FROM static_policy_types WHERE id = NEW.policy_typeid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (NEW.if_enable NOT IN('TRUE', 'FALSE'))
    OR (NEW.if_accept NOT IN('TRUE', 'FALSE'))
    OR (NEW.rule IS NOT NULL AND LENGTH(NEW.rule) > 2048);
END;

DROP TRIGGER after_delete_pubapp_policies;
CREATE TRIGGER after_delete_pubapp_policies AFTER DELETE ON pubapp_policies FOR EACH ROW
BEGIN
    DELETE FROM pubapp_policy_certs WHERE pubapp_policyid = OLD.id;
    DELETE FROM pubapp_policy_csums WHERE pubapp_policyid = OLD.id;
    DELETE FROM pubapp_policy_filters WHERE pubapp_policyid = OLD.id;
    DELETE FROM pubapp_policy_macfilters WHERE pubapp_policyid = OLD.id;
    DELETE FROM pubapp_policy_checks WHERE pubapp_policyid = OLD.id;
    DELETE FROM pubapp_policy_routes WHERE pubapp_policyid = OLD.id;
    DELETE FROM pubapp_policy_splits WHERE pubapp_policyid = OLD.id;
END;

DROP TRIGGER after_delete_pubapp_policy_splits;
CREATE TRIGGER after_delete_pubapp_policy_splits AFTER DELETE ON pubapp_policy_splits FOR EACH ROW
BEGIN
    DELETE FROM pubapp_policy_split_ports WHERE pubapp_policy_splitid = OLD.id;
END;

/*
* web app
*/
DROP TRIGGER after_insert_pubweb_apps;
CREATE TRIGGER after_insert_pubweb_apps AFTER INSERT ON pubweb_apps FOR EACH ROW
BEGIN
    INSERT INTO pubweb_group_apps(pubweb_appid, priority) SELECT NEW.id, COALESCE(MAX(priority), 0) + 1
        FROM pubweb_group_apps WHERE pubweb_groupid = 0;
    UPDATE system_sequences SET next_value = NEW.id + increment_by WHERE sequence_name='pubweb_apps' AND next_value <> 0;
END;

DROP TRIGGER after_delete_pubweb_apps;
CREATE TRIGGER after_delete_pubweb_apps AFTER DELETE ON pubweb_apps FOR EACH ROW
BEGIN
    DELETE FROM pubweb_parameters WHERE pubweb_appid = OLD.id;
    DELETE FROM pubweb_fbapps WHERE pubweb_appid = OLD.id;
    DELETE FROM pubweb_tracks WHERE pubweb_appid = OLD.id;
    DELETE FROM pubweb_group_apps WHERE pubweb_appid = OLD.id;
    DELETE FROM pubweb_permissions WHERE pubweb_appid = OLD.id;
    DELETE FROM pubweb_ports WHERE pubweb_appid = OLD.id;
    DELETE FROM pubweb_filters WHERE pubweb_appid = OLD.id;
    DELETE FROM pubweb_policies WHERE pubweb_appid = OLD.id;
END;

-- pubweb_groups
DROP TRIGGER before_insert_pubweb_groups;
CREATE TRIGGER before_insert_pubweb_groups BEFORE INSERT ON pubweb_groups FOR EACH ROW
BEGIN
    SELECT RAISE(ROLLBACK, 'Reach the system limitation')
    WHERE (SELECT COUNT(id) FROM pubweb_groups) >= 100;
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM pubweb_groups WHERE id = NEW.parent_groupid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (LENGTH(NEW.name) NOT BETWEEN 1 AND 64)
    OR (NEW.description IS NOT NULL AND LENGTH(NEW.description) > 128);
END;

DROP TRIGGER before_update_pubweb_groups;
CREATE TRIGGER before_update_pubweb_groups BEFORE UPDATE ON pubweb_groups FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM pubweb_groups WHERE id = NEW.parent_groupid) IS NULL;
    -- sanity check
    SELECT RAISE(ROLLBACK, 'Violate sanity check constraint')
    WHERE (LENGTH(NEW.name) NOT BETWEEN 1 AND 64)
    OR (NEW.description IS NOT NULL AND LENGTH(NEW.description) > 128);
END;

DROP TRIGGER after_insert_pubweb_groups;
CREATE TRIGGER after_insert_pubweb_groups AFTER INSERT ON pubweb_groups FOR EACH ROW
BEGIN
    UPDATE pubweb_groups SET right_id = right_id + 2
        WHERE left_id < (SELECT left_id FROM pubweb_groups WHERE id = NEW.parent_groupid)
        AND right_id > (SELECT right_id FROM pubweb_groups WHERE id = NEW.parent_groupid);
    UPDATE pubweb_groups SET left_id = left_id + 2, right_id = right_id + 2
        WHERE left_id > (SELECT left_id FROM pubweb_groups WHERE id = NEW.parent_groupid)
        AND right_id > (SELECT right_id FROM pubweb_groups WHERE id = NEW.parent_groupid);
    UPDATE pubweb_groups SET left_id = (SELECT right_id FROM pubweb_groups WHERE id = NEW.parent_groupid),
        right_id = (SELECT right_id + 1 FROM pubweb_groups WHERE id = NEW.parent_groupid) WHERE id = NEW.id;
    UPDATE pubweb_groups SET right_id = right_id + 2 WHERE id = NEW.parent_groupid;
END;

DROP TRIGGER after_delete_pubweb_groups;
CREATE TRIGGER after_delete_pubweb_groups AFTER DELETE ON pubweb_groups FOR EACH ROW
BEGIN
    UPDATE pubweb_groups SET parent_groupid = OLD.id WHERE left_id > OLD.left_id AND right_id < OLD.right_id;
    DELETE FROM pubweb_group_apps WHERE pubweb_groupid IN (SELECT id FROM pubweb_groups WHERE parent_groupid = OLD.id);
    UPDATE pubweb_groups SET right_id = right_id - (SELECT (COUNT(id) + 1)*2 FROM pubweb_groups WHERE parent_groupid = OLD.id)
        WHERE left_id < OLD.left_id AND right_id > OLD.right_id;
    UPDATE pubweb_groups SET left_id = left_id - (SELECT (COUNT(id) + 1)*2 FROM pubweb_groups WHERE parent_groupid = OLD.id),
        right_id = right_id - (SELECT (COUNT(id) + 1)*2 FROM pubweb_groups WHERE parent_groupid = OLD.id)
        WHERE left_id > OLD.left_id AND right_id > OLD.right_id;
    DELETE FROM pubweb_groups WHERE parent_groupid = OLD.id;
    DELETE FROM pubweb_group_apps WHERE pubweb_groupid = OLD.id;
END;

DROP TRIGGER before_insert_pubweb_group_apps;
CREATE TRIGGER before_insert_pubweb_group_apps BEFORE INSERT ON pubweb_group_apps FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM pubweb_apps WHERE id = NEW.pubweb_appid) IS NULL
    OR (SELECT id FROM pubweb_groups WHERE id = NEW.pubweb_groupid) IS NULL;
END;

DROP TRIGGER before_update_pubweb_group_apps;
CREATE TRIGGER before_update_pubweb_group_apps BEFORE UPDATE ON pubweb_group_apps FOR EACH ROW
BEGIN
    -- foreign key check
    SELECT RAISE(ROLLBACK, 'Violate foreign key constraint')
    WHERE (SELECT id FROM pubweb_apps WHERE id = NEW.pubweb_appid) IS NULL
    OR (SELECT id FROM pubweb_groups WHERE id = NEW.pubweb_groupid) IS NULL;
END;

DROP TRIGGER after_delete_pubweb_group_apps;
CREATE TRIGGER after_delete_pubweb_group_apps AFTER DELETE ON pubweb_group_apps FOR EACH ROW
    WHEN (SELECT COUNT(*) FROM pubweb_group_apps WHERE pubweb_appid = OLD.pubweb_appid) = 0
    AND (SELECT id FROM pubweb_apps WHERE id = OLD.pubweb_appid) IS NOT NULL
BEGIN
    INSERT INTO pubweb_group_apps(pubweb_appid, priority) SELECT OLD.pubweb_appid, COALESCE(MAX(priority), 0)+ 1
        FROM pubweb_group_apps WHERE pubweb_groupid = 0;
END;

DROP TRIGGER after_update_pubweb_permissions;
CREATE TRIGGER after_update_pubweb_permissions AFTER UPDATE ON pubweb_permissions FOR EACH ROW
    WHEN OLD.auth_levelid = 0 AND NEW.if_grant <> OLD.if_grant
BEGIN
    UPDATE pubweb_permissions SET if_grant=OLD.if_grant WHERE auth_levelid <> 0 AND pubweb_appid = OLD.pubweb_appid AND realm_realmid = OLD.realm_realmid;
END;

DROP TRIGGER after_delete_pubweb_policies;
CREATE TRIGGER after_delete_pubweb_policies AFTER DELETE ON pubweb_policies FOR EACH ROW
BEGIN
    DELETE FROM pubweb_policy_filters WHERE pubweb_policyid = OLD.id;
END;

/*
* publish subnet
*/
DROP TRIGGER after_delete_pubsubnet_filters;
CREATE TRIGGER after_delete_pubsubnet_filters AFTER DELETE ON pubsubnet_filters FOR EACH ROW
BEGIN
    DELETE FROM pubsubnet_filter_ports WHERE pubsubnet_filterid = OLD.id;
END;

DROP TRIGGER after_update_pubsubnet_filter_tracks;
CREATE TRIGGER after_update_pubsubnet_filter_tracks AFTER UPDATE ON pubsubnet_filter_tracks FOR EACH ROW
BEGIN
    SELECT NOTIFY("UPDATE$DDMZ", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17;
END;

/*
* site2site
*/
DROP TRIGGER after_insert_pubsite_sites;
CREATE TRIGGER after_insert_pubsite_sites AFTER INSERT ON pubsite_sites FOR EACH ROW
BEGIN
    SELECT NOTIFY("UPDATE$SITE2SITE", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17;
END;

DROP TRIGGER after_update_pubsite_sites;
CREATE TRIGGER after_update_pubsite_sites AFTER UPDATE ON pubsite_sites FOR EACH ROW
BEGIN
    SELECT NOTIFY("UPDATE$SITE2SITE", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17;
END;

DROP TRIGGER after_delete_pubsite_sites;
CREATE TRIGGER after_delete_pubsite_sites AFTER DELETE ON pubsite_sites FOR EACH ROW
BEGIN
    DELETE FROM pubsite_tracks WHERE pubsite_siteid = OLD.id;
    DELETE FROM pubsite_connects WHERE pubsite_siteid = OLD.id OR pubsite_peerid = OLD.id;
    DELETE FROM pubsite_subnets WHERE pubsite_siteid = OLD.id;
    DELETE FROM pubsite_users WHERE pubsite_siteid = OLD.id;
    DELETE FROM pubsite_permissions WHERE pubsite_siteid = OLD.id;
    DELETE FROM pubsite_filters WHERE pubsite_siteid = OLD.id;
    -- send notification
    SELECT NOTIFY("UPDATE$SITE2SITE", "127.0.0.1", port) FROM static_service_sets WHERE service_typeid=17;
END;

/* ################################################
* check policy triggers
################################################ */
DROP TRIGGER after_delete_policy_users;
CREATE TRIGGER after_delete_policy_users AFTER DELETE ON policy_users FOR EACH ROW
BEGIN
    DELETE FROM policy_user_times WHERE policy_userid = OLD.id;
    DELETE FROM policy_user_groups WHERE policy_userid = OLD.id;
    DELETE FROM policy_cert_attributes WHERE policy_userid = OLD.id;
    DELETE FROM policy_local_attributes WHERE policy_userid = OLD.id;
    DELETE FROM policy_ldap_attributes WHERE policy_userid = OLD.id;
    DELETE FROM policy_radius_attributes WHERE policy_userid = OLD.id;
    DELETE FROM policy_others_attributes WHERE policy_userid = OLD.id;
    DELETE FROM v_policy_cert_attributes WHERE id = OLD.id;
    DELETE FROM v_policy_local_attributes WHERE id = OLD.id;
    DELETE FROM v_policy_ldap_attributes WHERE id = OLD.id;
END;


