Dropping Triggers:

This Page captures the steps involved and queries to be executed to remove the triggers completely from the individual Point product DBs.

MSSQL

For Point Products installed in MSSQL : Please use the SQL studio and execute the below queries in the Applications DB (amdb) and Servicedesk DB (servicedesk).

--Drop procedure if already created.
DROP PROCEDURE [dbo].[DropSearchTriggers]
GO
--Create stored procedure
CREATE PROCEDURE dbo.DropSearchTriggers
AS
DECLARE @SQLCmd nvarchar(1000)
DECLARE @Trig sysname
DECLARE @owner sysname
DECLARE @uid int
DECLARE TGCursor CURSOR FOR
SELECT name, uid FROM sysobjects WHERE type = 'TR' and (name like '%_insert' OR name like '%_update' OR name like '%_delete')
OPEN TGCursor
FETCH NEXT FROM TGCursor INTO @Trig, @uid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLCmd = N'DROP TRIGGER [' + user_name(@uid) + '].[' + @Trig + ']'
EXEC sp_executesql @SQLCmd
PRINT @SQLCmd
FETCH next FROM TGCursor INTO @Trig, @uid
END
CLOSE TGCursor
DEALLOCATE TGCursor
GO 
--To execute the stored procedure
DropSearchTriggers
--To check triggers existance
SELECT name, uid FROM sysobjects WHERE type = 'TR'

---

MySQL

For Point Products installed in MySQL : Please connect to the command prompt on the point product installed servers. Execute the below commands under the mentioned locations.

Locations:

<Applications Manager Home>working\mysql\bin\
<Servicedesk plus Home>\mysql\bin\

Commands:

mysql -u root -P 32268  --skip-column-names -e "select CONCAT('Drop TRIGGER ',trigger_schema,'.',trigger_name,';') from information_schema.triggers where (trigger_name like '%_insert' OR trigger_name like '%_update' OR trigger_name like '%_delete')" >> dump.sql
mysql -u root -P 32268 -e "Source dump.sql"

Note: Please check the port numbers of the respective products and modify the port numbers on command accordingly.

---

PGSQL

Locations:

<Applications Manager Home>\pgsql\bin\

<Servicedesk Plus Home>\pgsql\bin\

Steps to Connect PGSQL: (Note: Please check the port numbers of the respective products and modify the port numbers on command accordingly.)
Execute the commands : 
> psql.exe -U postgres -h localhost -d postgres -p 33366

CREATE OR REPLACE FUNCTION strip_all_triggers() RETURNS text AS $$ DECLARE
triggNameRecord RECORD;
triggTableRecord RECORD;
BEGIN
    FOR triggNameRecord IN select distinct(trigger_name) from information_schema.triggers where trigger_schema = 'public' and (trigger_name like '%_insert' OR trigger_name like '%_update' OR trigger_name like '%_delete') LOOP
        SELECT distinct(event_object_table) INTO triggTableRecord from information_schema.triggers where trigger_name = triggNameRecord.trigger_name;
        RAISE NOTICE 'Dropping trigger: % on table: %', triggNameRecord.trigger_name, triggTableRecord.event_object_table;
 EXECUTE 'DROP TRIGGER ' || triggNameRecord.trigger_name || ' ON ' || triggTableRecord.event_object_table || ';';
    END LOOP;
    RETURN 'done';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
select strip_all_triggers();