﻿EXEC dev.sp_Global_GenerateAsyncAuditTriggerAll
GO

EXEC dev.sp_Global_GenerateAsyncAuditProcedureAll
GO

EXEC dev.sp_Global_GenerateAsyncAuditAuditPocessing
GO

ALTER QUEUE auditLog.[AuditQueue]
WITH ACTIVATION
( 
    STATUS = ON,
    PROCEDURE_NAME = auditLog.usp_AuditProcessing,
    MAX_QUEUE_READERS = 10,
    EXECUTE AS 'Kreta_tech_user'
);
GO


DECLARE trCursor CURSOR FOR 
  SELECT OBJECT_NAME(parent_id) AS tableName, name as triggerName 
  FROM sys.triggers
  WHERE name like 'tr%Log'

DECLARE 
  @tableName nvarchar(100),
  @triggerName nvarchar(100),
  @sql nvarchar(max)

OPEN trCursor
FETCH NEXT FROM trCursor INTO @tableName, @triggerName

WHILE @@FETCH_STATUS = 0 BEGIN
  SET @sql = 'DROP TRIGGER ' + @triggerName + ';'
  EXEC sys.sp_executesql @sql
FETCH NEXT FROM trCursor INTO @tableName, @triggerName
END

CLOSE trCursor
DEALLOCATE trCursor
GO
