1-) MSSQL RMOS - Trigger kullanımı(insert)
-- Trigger
create trigger telefon_ekle
on Telefonlar
after insert
as
begin
select top 1 * from Telefonlar order by TelefonNo desc
end
-- Kullanımı
insert into Telefonlar values('a134','1134','2234')
2-) Trigger kullanımı(update)
...
9-) Log için (1.yol) Tercih edilir
kaynak : http://cagataykartal.com/Trigger-ile-Log-Tutma/71 üzerinde oynamalar yapıldı son hali aşağıdadır
1* Aşağıdaki kodu çalıştırın
USE [deneme]
GO
/****** Object: Table [dbo].[Testtable] Script Date: 17.07.2018 15:44:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Testtable](
[id] [int] IDENTITY(1,1) NOT NULL,
[ad] [varchar](50) NULL,
[soyad] [varchar](50) NULL,
CONSTRAINT [PK_Testtable] PRIMARY KEY CLUSTERED
(
[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
2* Aşağıdaki kodu çalıştırın
USE [deneme]
GO
/****** Object: Table [dbo].[Testtable_Audit] Script Date: 17.07.2018 15:45:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Testtable_Audit](
[Audit_id] [int] IDENTITY(1,1) NOT NULL,
[Islem_Tarihi] [datetime] NULL,
[Islem] [char](10) NULL,
[session_ID] [nvarchar](50) NULL,
[IPAddress] [nvarchar](50) NULL,
[MachineName] [nvarchar](50) NULL,
[LoginName] [nvarchar](50) NULL,
[ApplicationName] [nvarchar](250) NULL,
[id] [int] NULL,
[ad] [varchar](50) NULL,
[soyad] [varchar](50) NULL,
CONSTRAINT [PK_Testtable_Audit] PRIMARY KEY CLUSTERED
(
[Audit_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
3* Aşağıdaki kodu çalıştırın
USE [deneme]
GO
/****** Object: Trigger [dbo].[Audit_TestDB_Trigger] Script Date: 17.07.2018 15:45:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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
4* şimdi update,delete,insert işlemleri yapabilir Testtable_Audit tablosunda logları görebilirsiniz
10-) Log için (2.yol) Bunada bakabilirsin
kod aşağıdadır;
use MSSQLTips
go
create table tblOrders
(
OrderID integer Identity(1,1) primary key,
OrderApprovalDateTime datetime,
OrderStatus varchar(20)
)
create table tblOrdersAudit
(
OrderAuditID integer Identity(1,1) primary key,
OrderID integer,
OrderApprovalDateTime datetime,
OrderStatus varchar(20),
UpdatedBy nvarchar(128),
UpdatedOn datetime
)
go
create trigger tblTriggerAuditRecord on tblOrders
after update, insert
as
begin
insert into tblOrdersAudit
(OrderID, OrderApprovalDateTime, OrderStatus, UpdatedBy, UpdatedOn )
select i.OrderID, i.OrderApprovalDateTime, i.OrderStatus, SUSER_SNAME(), getdate()
from tblOrders t
inner join inserted i on t.OrderID=i.OrderID
end
go
insert into tblOrders values (NULL, 'Pending')
insert into tblOrders values (NULL, 'Pending')
insert into tblOrders values (NULL, 'Pending')
go
select * from tblOrders
select * from tblOrdersAudit
update tblOrders
set OrderStatus='Approved',
OrderApprovalDateTime=getdate()
where OrderID=1
go
select * from tblOrders
select * from tblOrdersAudit order by OrderID, OrderAuditID
go
update tblOrders
set OrderStatus='Approved',
OrderApprovalDateTime=getdate()
where OrderID=2
go
select * from tblOrders
select * from tblOrdersAudit order by OrderID, OrderAuditID
go
update tblOrders
set OrderStatus='Cancelled'
where OrderID=1
go
select * from tblOrders
select * from tblOrdersAudit order by OrderID, OrderAuditID
go