🚀 MSSQL RMOS - otomatik log tablosu oluşturma procedürü BAŞKA VERİ TABANINA!

Ramazan HABER / MSSQL RMOS / otomatik log tablosu oluşturma procedürü BAŞKA VERİ TABANINA!

1-) MSSQL RMOS - otomatik log tablosu oluşturma procedürü BAŞKA VERİ TABANINA!

2-) Model (Bu kısım her veritabanında olacak. Log için sabit alanlar)

CREATE TABLE [dbo].[Log](

[Log_id] [int] IDENTITY(1,1) NOT NULL,

[Log_Islem_Tarihi] [datetime] NULL,

[Log_Islem] [char](10) NULL,

[Log_session_ID] [nvarchar](50) NULL,

[Log_IPAddress] [nvarchar](50) NULL,

[Log_MachineName] [nvarchar](50) NULL,

[Log_LoginName] [nvarchar](50) NULL,

[Log_ApplicationName] [nvarchar](250) NULL,

CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED

(

[Log_id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

3-) örnek bir tablo

CREATE TABLE [dbo].[Telefonlar](

[telefon_id] [int] IDENTITY(1,1) NOT NULL,

[telefon_Ad] [nvarchar](30) NULL,

[telefon_SoyAd] [nvarchar](30) NULL,

[telefon_No] [nvarchar](30) NULL,

CONSTRAINT [PK_Telefonlar] PRIMARY KEY CLUSTERED

(

[telefon_id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

4-) otomatik log tablosu oluşturma procedürü

-- Kullanımı -> exec Log_Olustur 'Log','Telefonlar','RM_Log' -> Log ve Telefonlar tablosunu orjinal DB de olacak. RM_Log veri tabanı ise Log için Açılmış olan veri tabanıdır

-- Log'da belirli bir değerin son 3 olayının değerini görmek için yazılan sorgu aşağıdadır. not : En son yapılan işlem en üsttedir

-- select top 3 * from Log_Telefonlar where telefon_id=5 order by Log_id desc

Create proc [dbo].[Log_Olustur](@tableName_Model nvarchar (100),@tableName_Neyi nvarchar (100),@tasinacakDB nvarchar(100))

as begin

declare @Log_PK nvarchar(100)=(SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1

AND TABLE_NAME = @tableName_Model)

declare @tableName_Nereye nvarchar(50)=@tableName_Model+'_'+@tableName_Neyi

declare @rowcount nvarchar(MAX)

if (object_id(@tableName_Nereye, 'U') is not null)

begin select 'çalıştırmış olduğun DB de tablo zaten var! Lütfen silip tekrar deneyin. -> ' + @tableName_Nereye as UYARI end

else begin

declare @createTablo nvarchar(200)='select * into '+@tableName_Nereye+' from '+@tableName_Model+' where 1=0;ALTER TABLE '+@tableName_Nereye+' ADD PRIMARY KEY('+@Log_PK+');'

exec sp_executesql @createTablo, N'@rowcount int output', @rowcount output;

DECLARE @myTableVariable TABLE (kod varchar(2000))

INSERT INTO @myTableVariable

SELECT 'ALTER TABLE dbo.'+@tableName_Nereye+' ADD ' + c.Name + ' ' +

CASE WHEN c.is_computed = 1 THEN 'AS ' + cc.definition

ELSE t.Name +

CASE WHEN c.is_identity = 1

THEN '' -- yorum satırı yaptım -> ' IDENTITY(' + CONVERT(VARCHAR(10), ic.seed_value) + ',' + CONVERT(VARCHAR(10), ic.increment_value) + ')'

WHEN t.name IN ('CHAR', 'NCHAR', 'VARCHAR', 'NVARCHAR', 'DATETIME2')

THEN '(' + CONVERT(VARCHAR(10), c.max_length) + ')'

WHEN t.name IN ('NUMERIC', 'DECIMAL')

THEN '(' + CONVERT(VARCHAR(10), c.precision) + ', ' + CONVERT(VARCHAR(10), c.scale) + ')'

ELSE ''

END

END + ';

' as kod

FROM sys.columns AS c

INNER JOIN sys.types AS t

ON t.system_type_id = c.system_type_id

AND t.user_type_id = c.user_type_id

LEFT JOIN sys.computed_columns AS cc

ON cc.object_id = c.object_id

AND cc.column_id = c.column_id

LEFT JOIN sys.identity_columns AS ic

ON ic.object_id = c.object_id

AND ic.column_id = c.column_id

WHERE c.object_id = OBJECT_ID(N'dbo.'+@tableName_Neyi+'', 'U')

AND NOT EXISTS

( SELECT 1

FROM sys.columns AS c2

WHERE c2.object_id = OBJECT_ID(N'dbo.foo_mod')

AND c2.name = c.name

)

ORDER BY c.column_id;

select * from @myTableVariable

Declare @kodlar nvarchar(200)

While (Select Count(*) From @myTableVariable) > 0

Begin

Select Top 1 @kodlar = kod From @myTableVariable

declare @gecici nvarchar(MAX)=REPLACE(@kodlar, '-1', 'MAX')

set @gecici =REPLACE(@gecici, 'decimal', 'decimal(18,5)')

select 'oldu '+@gecici

--Do some processing here

exec sp_executesql @gecici,N'@rowcount int output', @rowcount output;

Delete @myTableVariable Where kod = @kodlar

End

-- diğer veri tabanını oluşturma ve tabloları ona aktarma

declare @tasinacakTabloTamisim nvarchar(50)=@tasinacakDB+'.dbo.'+@tableName_Nereye

-- select @tasinacakTabloTamisim

set @Log_PK =(SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1

AND TABLE_NAME = @tableName_Nereye

)

--select @Log_PK

set @createTablo='CREATE DATABASE '+@tasinacakDB

declare @createTablo1 nvarchar(200)='select * into '+@tasinacakTabloTamisim+' from '

+@tableName_Nereye+' where 1=0;ALTER TABLE '+@tasinacakTabloTamisim+' ADD PRIMARY KEY('+@Log_PK+'); drop table '+@tableName_Nereye+';'

--select @createTablo

If(db_id(@tasinacakDB) IS NULL) begin exec sp_executesql @createTablo, N'@rowcount int output', @rowcount output; end

if (object_id(@tasinacakTabloTamisim, 'U') is not null)

begin select @tasinacakTabloTamisim+'-> tablo zaten var! Lütfen silip tekrar deneyin.' as UYARI end

else begin

exec sp_executesql @createTablo1, N'@rowcount int output', @rowcount output;

end

End

/*--AKTARMA KONTROL

use deneme1

declare @TABLE_CATALOG nvarchar(50)='deneme1' --Nereden DB

declare @TABLE_SCHEMA nvarchar(50)='dbo' --Nereden tablo Schema

declare @TABLE_NAME nvarchar(50)='Telefonlar' --Nereden tablo

declare @TABLE_CATALOG1 nvarchar(50)='RM_Log' --Nereye DB

declare @TABLE_SCHEMA1 nvarchar(50)='dbo' --Nereye tablo Schema

declare @TABLE_NAME1 nvarchar(50)='Log_Telefonlar' --Nereye tablo

DECLARE @DEGER1 NVARCHAR(20)=(SELECT COUNT(COLUMN_NAME)+8 as 'COLUMN SAYISI'

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_CATALOG = @TABLE_CATALOG AND TABLE_SCHEMA = @TABLE_SCHEMA

AND TABLE_NAME = @TABLE_NAME)

use RM_Log

DECLARE @DEGER2 NVARCHAR(20)=(SELECT COUNT(COLUMN_NAME) as 'COLUMN SAYISI'

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_CATALOG = @TABLE_CATALOG1 AND TABLE_SCHEMA = @TABLE_SCHEMA1

AND TABLE_NAME = @TABLE_NAME1)

if @DEGER1=@DEGER2 begin select 'Column Sayıları Eşittir' as 'DURUM' end else begin select 'Column Sayıları Eşit Değildir!' as 'DURUM' end

4-) Son olarak Triggerimizi oluşturalım

Create TRIGGER Trigger_Telefonlar

ON Telefonlar FOR INSERT, UPDATE, DELETE NOT FOR REPLICATION

SET NOCOUNT ON;

--INSERT,UPDATE yada DELETE işlemi olduğunu belirleme

--Ramazan HABER

declare @session_ID nvarchar(50)

declare @IPAddress nvarchar(50)

declare @MachineName nvarchar(50)

declare @LoginName nvarchar(50)

declare @ApplicationName nvarchar(250)

set @session_ID=(SELECT conn.session_ID FROM sys.dm_exec_connections conn inner join sys.dm_exec_sessions sess on conn.session_ID=sess.session_ID where conn.session_ID=@@SPID)

set @IPAddress=(SELECT conn.client_net_address FROM sys.dm_exec_connections conn inner join sys.dm_exec_sessions sess on conn.session_ID=sess.session_ID where conn.session_ID=@@SPID)

set @MachineName=(SELECT sess.host_name FROM sys.dm_exec_connections conn inner join sys.dm_exec_sessions sess on conn.session_ID=sess.session_ID where conn.session_ID=@@SPID)

set @LoginName=(SELECT login_name FROM sys.dm_exec_connections conn inner join sys.dm_exec_sessions sess on conn.session_ID=sess.session_ID where conn.session_ID=@@SPID)

set @ApplicationName=(SELECT sess.program_name FROM sys.dm_exec_connections conn inner join sys.dm_exec_sessions sess on conn.session_ID=sess.session_ID where conn.session_ID=@@SPID)

--Ramazan HABER

DECLARE @operation as Varchar(10)

DECLARE @Count as int

SET @operation = 'Inserted' -- Setting operation to 'Inserted'

SELECT @Count = COUNT(*) FROM DELETED

if @Count > 0

BEGIN

SET @operation = 'Deleted' -- Set Operation to 'Deleted'

SELECT @Count = COUNT(*) FROM INSERTED

IF @Count > 0

SET @operation = 'Updated' -- Set Operation to 'Updated'

END

--Silme işlemini yakala

if @operation = 'Deleted'

BEGIN -- BAŞKA VERİ TABANINA AKTARMAK İÇİN *RM_Log.dbo.Log_Telefonlar* ŞEKLİNDE YAPILABİLİR

Insert into RM_Log.dbo.Log_Telefonlar(Log_Islem_Tarihi,Log_Islem ,Log_session_ID ,Log_IPAddress ,Log_MachineName ,Log_LoginName ,Log_ApplicationName ,telefon_id,telefon_Ad,telefon_SoyAd,telefon_No)

SELECT GETDATE() ,'Deleted' ,@session_ID ,@IPAddress ,@MachineName ,@LoginName ,@ApplicationName ,telefon_id,telefon_Ad,telefon_SoyAd,telefon_No from deleted

END

ELSE

BEGIN

--trigger, insert ve update için aynı davranır.Burada belirtebiliriz.

--SELECT GETDATE(),'inserted',id,ad,soyad from inserted

--Yeni kayıt işlemini yakala

if @operation = 'Inserted'

BEGIN

Insert into RM_Log.dbo.Log_Telefonlar(Log_Islem_Tarihi,Log_Islem ,Log_session_ID ,Log_IPAddress ,Log_MachineName ,Log_LoginName ,Log_ApplicationName ,telefon_id,telefon_Ad,telefon_SoyAd,telefon_No)

SELECT GETDATE() ,'inserted' ,@session_ID ,@IPAddress ,@MachineName ,@LoginName ,@ApplicationName ,telefon_id,telefon_Ad,telefon_SoyAd,telefon_No from inserted

END

-- Güncelleme işlemini yakala

ELSE

BEGIN

INSERT INTO RM_Log.dbo.Log_Telefonlar(Log_Islem_Tarihi,Log_Islem ,Log_session_ID ,Log_IPAddress ,Log_MachineName ,Log_LoginName ,Log_ApplicationName ,telefon_id,telefon_Ad,telefon_SoyAd,telefon_No)

SELECT GETDATE() ,'Updated' ,@session_ID ,@IPAddress ,@MachineName ,@LoginName ,@ApplicationName ,telefon_id,telefon_Ad,telefon_SoyAd,telefon_No from inserted

END

5-) COLUMN'LARI DOĞRU AKTARMIŞ MI DİYE COLUMN SAYILARININ DOĞRULUĞUNU KONTROL EDER

--AKTARMA KONTROL

use deneme1

declare @TABLE_CATALOG nvarchar(50)='deneme1' --Nereden DB

declare @TABLE_SCHEMA nvarchar(50)='dbo' --Nereden tablo Schema

declare @TABLE_NAME nvarchar(50)='Telefonlar' --Nereden tablo

declare @TABLE_CATALOG1 nvarchar(50)='RM_Log' --Nereye DB

declare @TABLE_SCHEMA1 nvarchar(50)='dbo' --Nereye tablo Schema

declare @TABLE_NAME1 nvarchar(50)='Log_Telefonlar' --Nereye tablo

DECLARE @DEGER1 NVARCHAR(20)=(SELECT COUNT(COLUMN_NAME)+8 as 'COLUMN SAYISI'

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_CATALOG = @TABLE_CATALOG AND TABLE_SCHEMA = @TABLE_SCHEMA

AND TABLE_NAME = @TABLE_NAME)

use RM_Log

DECLARE @DEGER2 NVARCHAR(20)=(SELECT COUNT(COLUMN_NAME) as 'COLUMN SAYISI'

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_CATALOG = @TABLE_CATALOG1 AND TABLE_SCHEMA = @TABLE_SCHEMA1

AND TABLE_NAME = @TABLE_NAME1)

if @DEGER1=@DEGER2 begin select 'Column Sayıları Eşittir' as 'DURUM' end else begin select 'Column Sayıları Eşit Değildir!' as 'DURUM' end

2021 Ocak 18 Pazartesi
330