Как отключить триггер в sql

Как отключить триггер в sql

Для отключения триггера в SQL необходимо использовать оператор ALTER TRIGGER . Вот пример, как это сделать:

ALTER TRIGGER имя_триггера DISABLE; 

Здесь имя_триггера — это имя триггера, который вы хотите отключить. Команда DISABLE используется для отключения триггера.

Вы также можете использовать команду ENABLE , чтобы снова включить триггер:

ALTER TRIGGER имя_триггера ENABLE; 

Эта команда включает ранее отключенный триггер.

Обратите внимание, что для отключения или включения триггера вам может потребоваться соответствующее разрешение в базе данных.

Отключение/включение триггера в процедуре

Пишу участок кода, в котором для работы на время операции необходимо отключить триггер, а затем — включить. На самую первую строку «alter table. » выдаётся ошибка:

PLS-00103: Encountered the symbol «ALTER» when expecting one of the following:

Версия — 9i. Как включить/отключить триггер?

 if p_DicPurpose = 1 and p_RegisteredStandart = 1 then alter trigger TIB_j_standart_reg_name disable; -- отключаем триггер генерации наименования зарегистрированного эталона insert into j_standart_reg -- для предотвращения возникновения ошибки (equ_id) values (l_NewEquipmentId); alter trigger TIB_j_standart_reg_name enable; end if; 

Отслеживать
51.6k 201 201 золотой знак 65 65 серебряных знаков 247 247 бронзовых знаков
задан 19 сен 2017 в 7:59
393 4 4 золотых знака 8 8 серебряных знаков 20 20 бронзовых знаков

На вопрос уже ниже ответили. от себя замечу, что так делать вообще не стоит. DDL завершит текущую транзакцию и откат выполняемого в данном pl/sql блоке и до него будет невозможен. И что самое печальное — это почти равносильно тому, что вы просто удалите триггер навсегда. в момент между disable и enable другой процесс может сделать в таблице, что то на что нужна реакция, а триггер выполнен не будет. стоит рассмотреть другие пути обхода, например модифицировать некое поле и/или использовать специальное значение на которое среагирует триггер и не будет выполнять то, что не надо

19 сен 2017 в 8:08

@Mike отличный комментарий! Если добавить в него пример реализации без DDL и ссылки на документацию, то получится отличный ответ, который дополнит уже существующий.

19 сен 2017 в 8:40

2 ответа 2

Сортировка: Сброс на вариант по умолчанию

В PL\SQL блоках операторы DDL надо обернуть в execute immediate

execute immediate 'alter trigger TIB_j_standart_reg_name disable'; 

Отслеживать
ответ дан 19 сен 2017 в 8:02
7,175 9 9 золотых знаков 37 37 серебряных знаков 66 66 бронзовых знаков
Спасибо, то что нужно
19 сен 2017 в 8:04

Несколько подзабытое решение появившееся до execute immediate . Это пакетная процедура dbms_utility.exec_ddl_statement (ddl_statement) . Оба решения делают то же самое, в PL/SQL контексте выполняет DDL, который не поддерживается статическими инструкциями. Предпочтительней использовать execute immediate .

Но небольшое преимущество у dbms_utility.exec_ddl_statement всё же есть — её можно выполнить в дистрибутивной среде. Например отключить триггеры в удалённой базе до того, как произвести вставку или изменение в таблице в этой же удалённой базе.

create database link loopback using 'oracl'; Database link LOOPBACK created. begin dbms_utility.exec_ddl_statement@loopback('create table t1 (id number)'); dbms_utility.exec_ddl_statement@loopback('create trigger on_t1_new before insert on t1 for each row begin null; end;'); end; / PL/SQL procedure successfully completed. set serveroutput on size unlimited declare ret number; begin dbms_utility.exec_ddl_statement@loopback('alter trigger on_t1_new disable'); insert into t1@loopback values (99); select id into ret from t1@loopback; dbms_output.put_line ('ret@loopback='||ret); commit; dbms_utility.exec_ddl_statement@loopback('alter trigger on_t1_new enable'); end; / PL/SQL procedure successfully completed. ret@loopback=99 

Внимательно ознакомьтесь с комментарием от @Mike перед тем, как решите использовать предложенное решение.

Удаление или отключение триггеров DML

В этом разделе описывается удаление или отключение триггера DML в SQL Server с помощью SQL Server Management Studio или Transact-SQL.

В этом разделе

• Перед началом: РекомендацииБезопасность
• Для удаления или отключения триггера DML используется:Среда SQL Server Management StudioTransact-SQL

Перед началом

Рекомендации

• После удаления триггер удаляется из текущей базы данных. Это не повлияет на таблицу и данные, на которых он основан. При удалении таблицы автоматически удаляются все триггеры в ней.
• По умолчанию при создании триггер включается.
• Отключение триггера не сбрасывает его. Триггер все еще существует как объект в текущей базе данных. Впрочем, триггер в инструкциях INSERT, UPDATE или DELETE не сработает при выполнении любой из них. Отключенные триггеры можно повторно включать. При включении триггера он не создается повторно. Он срабатывает так же, как после создания.

Безопасность

Разрешения

Чтобы удалить триггер DML, необходимо разрешение ALTER для таблицы или представления, в которых определен данный триггер.

Для отключения или включения триггера DML пользователь должен обладать как минимум разрешением ALTER для таблицы или представления, где создан триггер.

Использование среды SQL Server Management Studio

Удаление триггера DML

1. В обозревателе объектов подключитесь к экземпляру ядра СУБД, а затем разверните этот экземпляр.
2. Разверните нужную базу данных, разверните узел Таблицы, а затем разверните таблицу, которая содержит удаляемый триггер.
3. Разверните узел Триггеры, щелкните правой кнопкой мыши нужный триггер и выберите команду Удалить.
4. В диалоговом окне Удаление объекта проверьте триггер и нажмите кнопку ОК.

Отключение и включение триггера DML

1. В обозревателе объектов подключитесь к экземпляру ядра СУБД, а затем разверните этот экземпляр.
2. Разверните нужную базу данных, разверните узел Таблицы, а затем разверните таблицу, которая содержит отключаемый триггер.
3. Разверните узел Триггеры, щелкните правой кнопкой мыши нужный триггер и выберите команду Отключить.
4. Чтобы включить триггер, выберите команду Включить.

Использование Transact-SQL

Удаление триггера DML

1. Соединитесь с ядром СУБД .
2. На панели «Стандартная» нажмите Создать запрос.
3. Скопируйте и вставьте следующие примеры в окно запроса. Выполните инструкцию CREATE TRIGGER , чтобы создать триггер Sales.bonus_reminder . Чтобы удалить триггер, выполните инструкцию DROP TRIGGER .

--Create the trigger. USE AdventureWorks2022; GO IF OBJECT_ID(N'Sales.bonus_reminder', N'TR') IS NOT NULL DROP TRIGGER Sales.bonus_reminder; GO CREATE TRIGGER Sales.bonus_reminder ON Sales.SalesPersonQuotaHistory WITH ENCRYPTION AFTER INSERT, UPDATE AS RAISERROR ('Notify Compensation', 16, 10); GO 

--Delete the trigger. USE AdventureWorks2022; GO IF OBJECT_ID ('Sales.bonus_reminder', 'TR') IS NOT NULL DROP TRIGGER Sales.bonus_reminder; GO 

Отключение и включение триггера DML

1. Соединитесь с ядром СУБД .
2. На панели «Стандартная» нажмите Создать запрос.
3. Скопируйте и вставьте следующие примеры в окно запроса. Выполните инструкцию CREATE TRIGGER , чтобы создать триггер Sales.bonus_reminder . Чтобы отключить или включить триггер, выполните соответственно инструкцию DISABLE TRIGGER или ENABLE TRIGGER .

--Create the trigger. USE AdventureWorks2022; GO IF OBJECT_ID(N'Sales.bonus_reminder', N'TR') IS NOT NULL DROP TRIGGER Sales.bonus_reminder; GO CREATE TRIGGER Sales.bonus_reminder ON Sales.SalesPersonQuotaHistory WITH ENCRYPTION AFTER INSERT, UPDATE AS RAISERROR ('Notify Compensation', 16, 10); GO 

--Disable the trigger. USE AdventureWorks2022; GO DISABLE TRIGGER Sales.bonus_reminder ON Sales.SalesPersonQuotaHistory; GO 

--Enable the trigger. USE AdventureWorks2022; GO ENABLE TRIGGER Sales.bonus_reminder ON Sales.SalesPersonQuotaHistory; GO 

Disable Enable Trigger SQL server for a table

I want to create one proc like below but it has error on syntax. Could anyone pointing out the problem?

Create PROCEDURE [dbo].[my_proc] AS BEGIN DISABLE TRIGGER dbo.tr_name ON dbo.table_name -- some update statement ENABLE TRIGGER dbo.tr_name ON dbo.table_name END ** Error Message : Incorrect syntax near 'ENABLE'. 

2,328 1 1 gold badge 21 21 silver badges 28 28 bronze badges
asked Sep 7, 2009 at 7:47
4,044 8 8 gold badges 37 37 silver badges 42 42 bronze badges

11 Answers 11

use the following commands instead:

ALTER TABLE table_name DISABLE TRIGGER tr_name ALTER TABLE table_name ENABLE TRIGGER tr_name 

answered Sep 7, 2009 at 7:54
Wael Dalloul Wael Dalloul
22.4k 11 11 gold badges 48 48 silver badges 57 57 bronze badges

what versions of SqlServer is this good for? not working for me, while DISABLE TRIGGER [dbo].[tr_name] on [schema].[table_name] worked

Jul 14, 2016 at 21:11

Your answer is correct. But actually @pang statements don’t need any fix instead of a simple ; ! I prefer to use ENABLE Trigger . It’s applicable on all SQL Servers starting with 2008.

Jan 13, 2018 at 8:47

If you need to do all the tables in a database use this: EXECUTE sp_msforeachtable «ALTER TABLE ? disable trigger ALL» go

Oct 29, 2018 at 20:15

The line before needs to end with a ; because in SQL DISABLE is not a keyword. For example:

BEGIN ; DISABLE TRIGGER . 

17k 20 20 gold badges 82 82 silver badges 120 120 bronze badges
answered Aug 30, 2010 at 21:28
811 6 6 silver badges 2 2 bronze badges

I much prefer this answer. It addresses the problem and gives the solution instead of a workaround. While workarounds have their place, it is important to understand why an error occurred instead of blindly following a workaround with no context.

Oct 16, 2015 at 15:46

As Mark mentioned, the previous statement should be ended in semi-colon. So you can use:

; DISABLE TRIGGER dbo.tr_name ON dbo.table_name 

17.5k 18 18 gold badges 90 90 silver badges 117 117 bronze badges
answered Oct 2, 2010 at 0:47
3,100 5 5 gold badges 35 35 silver badges 47 47 bronze badges

After the ENABLE TRIGGER OR DISABLE TRIGGER in a new line write GO, Example:

DISABLE TRIGGER dbo.tr_name ON dbo.table_name GO -- some update statement ENABLE TRIGGER dbo.tr_name ON dbo.table_name GO 

1,687 6 6 gold badges 19 19 silver badges 20 20 bronze badges
answered Nov 9, 2017 at 17:36
41 1 1 bronze badge

Below is the Dynamic Script to enable or disable the Triggers.

select 'alter table '+ (select Schema_name(schema_id) from sys.objects o where o.object_id = parent_id) + '.'+object_name(parent_id) + ' ENABLE TRIGGER '+ Name as EnableScript,* from sys.triggers t where is_disabled = 1 

2,883 4 4 gold badges 29 29 silver badges 42 42 bronze badges
answered Oct 22, 2018 at 11:52
Shahab Naseer Shahab Naseer
11 1 1 bronze badge

I wanted to share something that helped me out. Idea credit goes to @Siavash and @Shahab Naseer.

I needed something where I could script disable and re enable of triggers for a particular table. I normally try and stay away from tiggers, but sometimes they could be good to use.

I took the script above and added a join to the sysobjects so I could filter by table name. This script will disable a trigger or triggers for a table.

select 'alter table '+ (select Schema_name(schema_id) from sys.objects o where o.object_id = parent_id) + '.'+object_name(parent_id) + ' ENABLE TRIGGER '+ t.Name as EnableScript,* from sys.triggers t INNER JOIN dbo.sysobjects DS ON DS.id = t.parent_id where is_disabled = 0 AND DS.name = 'tblSubContact' 

answered Feb 16, 2021 at 14:51
Jay Walker Jay Walker
11 5 5 bronze badges

USE [DatabaseName] GO -- HABILITAR TRIGGERS SELECT 'ALTER TABLE ['+ ( SELECT SCHEMA_NAME(SCHEMA_ID) FROM [sys].[objects] AS O WHERE O.[object_id] = T.[parent_id]) + '].[' + OBJECT_NAME(T.[parent_id]) + '] ENABLE TRIGGER '+ T.[name] + ';' AS [EnableScript], * FROM [sys].[triggers] AS T INNER JOIN [sys].[sysobjects] DS ON DS.[id] = T.[parent_id] WHERE T.[is_disabled] = 0 --AND DS.[name] = 'TableName' -- DESHABILITAR TRIGGERS SELECT 'ALTER TABLE ['+ ( SELECT SCHEMA_NAME(SCHEMA_ID) FROM [sys].[objects] AS O WHERE O.[object_id] = T.[parent_id]) + '].[' + OBJECT_NAME(T.[parent_id]) + '] DISABLE TRIGGER '+ T.[name] + ';' AS [EnableScript], * FROM [sys].[triggers] AS T INNER JOIN [sys].[sysobjects] DS ON DS.[id] = T.[parent_id] WHERE T.[is_disabled] = 0 --AND DS.[name] = 'TableName' 

answered May 24, 2021 at 22:07
Francisco J. Naranjo Francisco J. Naranjo

— HABILITAR TRIGGERS SELECT ‘ALTER TABLE [‘+ ( SELECT SCHEMA_NAME(SCHEMA_ID) FROM [sys].[objects] AS O WHERE O.[object_id] = T.[parent_id]) + ‘].[‘ + OBJECT_NAME(T.[parent_id]) + ‘] ENABLE TRIGGER ‘+ T.[name] + ‘;’ AS [EnableScript], * FROM [sys].[triggers] AS T INNER JOIN [sys].[sysobjects] DS ON DS.[id] = T.[parent_id] WHERE T.[is_disabled] = 1 — Must be 1, not 0 — —AND DS.[name] = ‘TableName’

answered Oct 5, 2022 at 20:20

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Oct 9, 2022 at 21:08

Pinned: Currently used. Clean: Meaning the buffer is now unpinned and is a candidate for immediate aging out if the current (data blocks) are not referenced again. The contents are either in synch with disk or the buffer contains a CR snapshot of a block. Free/Unused: Meaning the buffer is empty because the instance just started. This state is very similar to Clean, except that the buffer has not been used. Dirty: Buffer is no longer pinned but the contents (data block) have changed and must be flushed to disk by DBWn before it can be aged out. Three Buffer Pool:

Keep Recycle Default Database Buffer Cache Parameter:

DB_CACHE_SIZE (BLOCK SIZE will be the size declare in DB_BLOCK_SIZE) DB_KEEP_CACHE_SIZE DB_2K_CACHE_SIZE DB_4K_CACHE_SIZE DB_8K_CACHE_SIZE DB_16K_CACHE_SIZE DB_32K_CACHE_SIZE DB_RACYCLE_CACHE_SIZE DB_BLOCK_SIZE(Default Block size which can not be changed later anyway)

SHOW PARAMETER DB_BLOCK_SIZE;

show parameter db_cache_size;