Delete triggers by query

Asked

Viewed 814 times

2

Consultation

To consult all the triggers of a database, use:

SELECT * FROM sys.triggers

Failed attempt

I tried to exclude all or some of these triggers contained in the result:

DELETE FROM sys.triggers or DELETE FROM sys.triggers WHERE nome LIKE 'doc%'

But accuses the error:

Ad hoc updates in system catalogues are not allowed.

Doubt

  • I’d like to know, how do I delete all or some triggers?
sql trigger

  • To remove a TRIGGER just do DROP TRIGGER [nome]. But what exactly do you want to do? You want to delete which triggers?

  • @Joãomartins wanted to delete all or some of them (filtering with WHERE) in the query result SELECT * FROM sys.triggers

2 answers

3

DELETE FROM sys.triggers this will try to erase the records from a system table, not the triggers.

To delete a Trigger the command is:

DROP TRIGGER nome
Documentation: DROP TRIGGER

Here a script that can delete several by mounting the command DROP and then using EXEC to exclude:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += 
    N'DROP TRIGGER ' + 
    QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + N'.' + 
    QUOTENAME(t.name) + N'; ' + NCHAR(13)
FROM sys.triggers AS t
WHERE t.is_ms_shipped = 0
  AND t.parent_class_desc = N'OBJECT_OR_COLUMN';

EXEC (@sql);

Copied from dba exchange

  • Ricardo, the problem is to delete "all" or a "selection", because I have N triggers.

  • @Rbz as would make this selection?

  • @Joãomartins In the same query: DELETE FROM sys.triggers WHERE nome LIKE 'doc%' ... was an assumption that can occur! rs

  • Is giving error while running @sql: O nome 'DROP TRIGGER [dbo].[trferiados_empresas_auto]; 
DROP TRIGGER [dbo].[trfiltro1_auto]; 
DROP TRIGGER [dbo].[trfiltro2_auto]; 
' não é um identificador válido. ... If I run it manually, it works... I tried to make some changes and nothing...

  • I found the reason, missing parentheses: EXEC (@sql);

  • good, I’ll edit the answer to be right ;)

Show 1 more comment

2


A more "simple" way of doing it will be like this:

DECLARE @SQL AS NVARCHAR(MAX) = ''

SELECT  @SQL = @SQL + 'DROP TRIGGER ' + [name]
FROM    sys.triggers
WHERE   [name] LIKE 'COP%'

EXEC(@SQL)

Browser other questions tagged sql trigger

You are not signed in. Login or sign up in order to post.