0-) 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]
GO
1-) MSSQL RMOS - ö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]
2-) otomatik log tablosu oluşturma procedürü
-- Kullanımı -> exec Log_Olustur 'Log','Telefonlar'
-- 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 Log_Olustur(@tableName_Model nvarchar (100),@tableName_Neyi 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 'tablo zaten var' 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
select 'oldu '+@kodlar
--Do some processing here
exec sp_executesql @kodlar,N'@rowcount int output', @rowcount output;
Delete @myTableVariable Where kod = @kodlar
End
End
End
3-) Trigger
Create TRIGGER Trigger_Telefonlar
ON Telefonlar FOR INSERT, UPDATE, DELETE NOT FOR REPLICATION
AS
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 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 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 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
END
4-) Başka veri tabanına Log tablolarını taşıma
declare @tasinacakDB nvarchar(50)='RM_Log'
declare @tasinacakSchema nvarchar(50)='dbo'
declare @tasinacakTablo nvarchar(50)='Log_Telefonlar'
declare @tasinacakTabloTamisim nvarchar(50)=@tasinacakDB+'.'+@tasinacakSchema+'.'+@tasinacakTablo
-- select @tasinacakTabloTamisim
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 = @tasinacakTablo
)
select @Log_PK
declare @rowcount nvarchar(MAX)
declare @createTablo nvarchar(200)='CREATE DATABASE '+@tasinacakDB
declare @createTablo1 nvarchar(200)='select * into '+@tasinacakTabloTamisim+' from '
+@tasinacakTablo+' where 1=0;ALTER TABLE '+@tasinacakTabloTamisim+' ADD PRIMARY KEY('+@Log_PK+'); drop table '+@tasinacakTablo+';'
--select @createTablo
exec sp_executesql @createTablo, N'@rowcount int output', @rowcount output;
exec sp_executesql @createTablo1, N'@rowcount int output', @rowcount output;
7-) Trigger orjinal
Create TRIGGER [dbo].[Audit_TestDB_Trigger]
ON [dbo].[Testtable] FOR INSERT, UPDATE, DELETE NOT FOR REPLICATION
AS
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
Insert into Testtable_Audit(Islem_Tarihi,Islem,id,ad,soyad,session_ID,IPAddress,MachineName,LoginName,ApplicationName)
SELECT GETDATE(),'Deleted',id,ad,soyad,@session_ID,@IPAddress,@MachineName,@LoginName,@ApplicationName 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 Testtable_Audit(Islem_Tarihi,Islem,id,ad,soyad,session_ID,IPAddress,MachineName,LoginName,ApplicationName)
SELECT GETDATE(),'inserted',id,ad,soyad,@session_ID,@IPAddress,@MachineName,@LoginName,@ApplicationName from inserted
END
-- Güncelleme işlemini yakala
ELSE
BEGIN
INSERT INTO Testtable_Audit(Islem_Tarihi,Islem,id,ad,soyad,session_ID,IPAddress,MachineName,LoginName,ApplicationName)
SELECT GETDATE(),'Updated',id,ad,soyad,@session_ID,@IPAddress,@MachineName,@LoginName,@ApplicationName from inserted
END
END
5-) SQL üretme dursun
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)
-- SQL üret
declare @table_name_orgi nvarchar(100)='Telefonlar'
declare @table_name nvarchar(100)='Log_Telefonlar'
declare @result varchar(max)
declare @result_orgi varchar(max)
select @result = COALESCE(@result + ', ', '') + COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table_name
select @result_orgi = COALESCE(@result_orgi + ', ', '') + COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table_name_orgi
--select @result
select @result_orgi
declare @virgul int=(SELECT CHARINDEX(',', @result))
declare @SQL_deleted nvarchar(500)=(select 'insert into '+@table_name+'('+SUBSTRING(@result, @virgul+2, LEN(@result))+')'+' select GETDATE() , ''Deleted'','''+@session_ID+''','''+@IPAddress+''','''+@MachineName+''','''+@LoginName+''','''+@ApplicationName+''','+@result_orgi+' from deleted');
select @SQL_deleted
-- SQL üret
9-) otomatik log tablosu oluşturma procedürü(yedek)
declare @tableName_Model nvarchar(50)='Log'
declare @tableName_Neyi nvarchar(50)='tablo'
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 'tablo zaten var' 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
select 'oldu '+@kodlar
--Do some processing here
exec sp_executesql @kodlar,N'@rowcount int output', @rowcount output;
Delete @myTableVariable Where kod = @kodlar
End
End
10-) sql stringi çalıştırma
declare @rowcount nvarchar(500)
exec sp_executesql N'insert into tblOrders values(''2018-01-01'',''asdas'')',
N'@rowcount int output', @rowcount output;
11-) MSSQL RMOS - orjinal hali
declare @tableName_Neyi nvarchar(50)
declare @tableName_Nereye nvarchar(50)
set @tableName_Neyi='tblOrders'
set @tableName_Nereye='yeni1'
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 ' 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 + ';
GO
'
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;
22-) EN SON KALDIĞIM YER OTOMATİK COLUMN'LARI ALIR DELETE KISMINDA BIR ORNEGI VAR AMA TERCİH EDİLMEYEN YÖNTEM ÇÜNKÜ HER DELETE DE GİDİP SÜTÜNLARI BULACAK YEDEK TABLO OLUSTURACAK SONRA ONU DROP EDECEK MALİYETLİ İŞ YANİ. YİNEDE YAPILABİLİR
alter TRIGGER Trigger_Telefonlar
ON Telefonlar FOR INSERT, UPDATE, DELETE NOT FOR REPLICATION
AS
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)
-- SQL üret
declare @table_name_orgi nvarchar(100)='Telefonlar'
declare @table_name nvarchar(100)='Log_Telefonlar'
declare @result varchar(max)
declare @result_orgi varchar(max)
select @result = COALESCE(@result + ', ', '') + COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table_name
select @result_orgi = COALESCE(@result_orgi + ', ', '') + COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table_name_orgi
--select @result
select @result_orgi
declare @virgul int=(SELECT CHARINDEX(',', @result))
declare @SQL_deleted nvarchar(500)=(select 'insert into '+@table_name+'('+SUBSTRING(@result, @virgul+2, LEN(@result))+')'+' select GETDATE() , ''Deleted'','''+@session_ID+''','''+@IPAddress+''','''+@MachineName+''','''+@LoginName+''','''+@ApplicationName+''','+@result_orgi+' from LOG_TEMP');
select @SQL_deleted
-- SQL üret
--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
SET @table_name = (SELECT 'tblSpendingRaw'
+ CAST(DATEPART(DAY, GETDATE()) AS VARCHAR(2))
+ LEFT(UPPER(DATENAME(MONTH, GETDATE())), 3)
+ CAST(DATEPART(HOUR, GETDATE()) AS VARCHAR(2))
+ CAST(DATEPART(MINUTE, GETDATE()) AS VARCHAR(2))
+ CAST(DATEPART(SECOND, GETDATE()) AS VARCHAR(2))
);
SELECT * INTO LOG_TEMP FROM Deleted;
exec sp_executesql @SQL_deleted
DROP TABLE LOG_TEMP
--insert into 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','54','<local machine>','RAMBO','sa','Microsoft SQL Server Management Studio - Query',telefon_id, telefon_Ad, telefon_SoyAd, telefon_No from deleted
-- AŞAĞISI MANUEL EKLEME
--Insert into 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 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 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
END