• Aranacak içerik:
31Ara
SQL Server Trigger Listesi

Aşağıdaki script Sql server attachlı olan tüm database lerdeki trigger ların listesini verir

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

SELECT @sql = @sql + '
USE ' + QUOTENAME(name) + ';

IF EXISTS (SELECT 1 FROM sys.triggers WHERE is_ms_shipped = 0)
BEGIN
    SELECT
        ''' + name + ''' AS DatabaseName,
        s.name AS SchemaName,
        o.name AS ObjectName,
        o.type_desc AS ObjectType,
        tr.name AS TriggerName,
        tr.is_instead_of_trigger,
        tr.is_disabled,
        tr.create_date,
        tr.modify_date
    FROM sys.triggers tr
    JOIN sys.objects o ON tr.parent_id = o.object_id
    JOIN sys.schemas s ON o.schema_id = s.schema_id
    WHERE tr.is_ms_shipped = 0
      AND (
            tr.is_instead_of_trigger = 1
            OR OBJECT_DEFINITION(tr.object_id) LIKE ''%INSERT%''
            OR OBJECT_DEFINITION(tr.object_id) LIKE ''%DELETE%''
          );
END
'
FROM sys.databases
WHERE state = 0
AND database_id > 4;

EXEC sp_executesql @sql;
16May
SQL Server Kontröllü Triger Oluşturma

Sql server da bir triger var mı diye kontrol edip var ise update yok sa create işlemini gerçekleştiren script

 IF NOT EXISTS(select * FROM sys.triggers where name = 'trg_HarSil')
BEGIN
    EXEC sp_executesql N' 
CREATE TRIGGER [dbo].[trg_HarSil] ON [dbo].[STOKHAREKET] FOR DELETE AS  
BEGIN SET NOCOUNT ON;
	DECLARE @TRGID INT
    SELECT @TRGID = deleted.ID FROM deleted
    delete from STOKHAREKET WHERE TRGID = @TRGID

END
'
END 
ELSE
BEGIN
      EXEC sp_executesql N'  
ALTER TRIGGER [dbo].[trg_HarSil] ON [dbo].[STOKHAREKET] FOR DELETE AS  
BEGIN SET NOCOUNT ON;
	DECLARE @TRGID INT
    SELECT @TRGID = deleted.ID FROM deleted
    delete from STOKHAREKET WHERE TRGID = @TRGID

END
'
END

yukarıdaki triger da STOKHAREKET tablosuna başka bir trigger ile kayıt edilen veriler bağlı hareket silindiğinde otomatik olarak trigger oluşmuş hareketleri temizler

Telif hakkı © 2026 Ahmet AYDINLIK | Yazılım Geliştirici