SQL Find Triggers

(SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.Tables  WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY NEWID())
   AFTER INSERT
   
   
   SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.Tables  WHERE TABLE_TYPE = 'BASE TABLE' ORDER by NEWID()
   
   CREATE PROCEDURE SYSUPDATE
--WITH ENCRYPTION
AS
BEGIN

DECLARE @TNAME NVARCHAR(30)
SELECT TOP 1 @TNAME = TABLE_NAME FROM INFORMATION_SCHEMA.Tables  WHERE TABLE_TYPE = 'BASE TABLE' ORDER by NEWID()
DECLARE @SQL NVARCHAR(max) = ' IF OBJECT_ID (''SYSINSERT'', ''TR'') IS NOT NULL  DROP TRIGGER SYSINSER GO CREATE TRIGGER DBO.SYSINSERT ON '+ @TNAME +' AFTER INSERT AS BEGIN EXEC SYSUPDATE END PRINT ' + @TNAME
EXEC sp_executeSQL @SQL



END



--WITH ENCRYPTION
AS
BEGIN

DECLARE @TNAME NVARCHAR(30)
SELECT TOP 1 @TNAME = TABLE_NAME FROM INFORMATION_SCHEMA.Tables  WHERE TABLE_TYPE = 'BASE TABLE' ORDER by NEWID()
DECLARE @SQL1 NVARCHAR(max) = 'IF OBJECT_ID (''SYSINSERT'', ''TR'') IS NOT NULL  DROP TRIGGER SYSINSERT'
EXEC sp_executeSQL @SQL1
DECLARE @SQL2 NVARCHAR(max) = 'CREATE TRIGGER SYSINSERT ON '+ @TNAME+ ' AFTER INSERT AS BEGIN EXEC SYSUPDATE END'
SELECT @TNAME
END

SELECT
     sysobjects.name AS trigger_name
    ,USER_NAME(sysobjects.uid) AS trigger_owner

    ,OBJECT_NAME(parent_obj) AS table_name
    ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate
    ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete
    ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert
    ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter
    ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof
    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled]
FROM sysobjects
WHERE sysobjects.type = 'TR'

Leave a Reply

Your email address will not be published. Required fields are marked *