Cari tablosunda silinen, değiştirilen veya eklenen kayıtların loglarını kayıt altına almak için aşağıdaki Trigger oluşturulmalı.

IF (SELECT count(*) FROM SYS.tables WHERE name = ‘CARI_LOG’)>0
BEGIN
DROP TABLE CARI_LOG

END

IF (SELECT count(*) FROM SYS.triggers WHERE name = ‘Tr_LogCARI’)>0
BEGIN
DROP TRIGGER Tr_LogCARI

END

GO
CREATE TABLE [dbo].[CARI_LOG](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TABLO] [nvarchar](50) NULL,
[ISLEM] [nvarchar](50) NULL,
[TARIH] [datetime] NULL,
[KODU] [NVARCHAR] (30) NULL,
[ACIKLAMA] [nvarchar](max) NULL,
[HOSTNAME] [nvarchar](150) NULL,
CONSTRAINT [PK_BA_LOG] 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
ALTER TABLE [dbo].[CARI_LOG] ADD CONSTRAINT [DF_CARI_LOG_TARIH] DEFAULT (getdate()) FOR [TARIH]
GO
ALTER TABLE [dbo].[CARI_LOG] ADD CONSTRAINT [DF_CARI_LOG_HOSTNAME] DEFAULT (host_name()) FOR [HOSTNAME]
GO
— Varsa kayıtları siler
DELETE FROM CARI_LOG
GO
–Trigger oluşturmak için

 

Create TRIGGER [dbo].[Tr_LogCARI]
ON [dbo].[CARI]
AFTER UPDATE, INSERT, DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Activity NVARCHAR (50)
DECLARE @COUNTER nvarchar(30)
DECLARE @PXml nvarchar(max)

IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
BEGIN
SET @Activity = ‘UPDATE’
SET @COUNTER=(SELECT KODU FROM deleted)
SET @PXml =(SELECT * FROM deleted FOR XML AUTO)
SET @PXml = @PXml +(SELECT * FROM inserted FOR XML AUTO)
END

IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted)
BEGIN
SET @Activity = ‘INSERT’
SET @COUNTER=(SELECT KODU FROM inserted)
SET @PXml =(SELECT * FROM inserted FOR XML AUTO)
END

IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS(SELECT * FROM inserted)
BEGIN
SET @Activity = ‘DELETE’
SET @COUNTER=(SELECT KODU FROM deleted)
SET @PXml =(SELECT * FROM deleted FOR XML AUTO)
END
INSERT INTO CARI_LOG (TABLO,ISLEM,KODU,ACIKLAMA)
VALUES(‘CARI’,@Activity,@COUNTER,@PXml)
SET NOCOUNT OFF;
END

 

 

 

Stok tablosunda silinen, değiştirilen veya eklenen kayıtların loglarını kayıt altına almak için aşağıdaki Trigger oluşturulmalı.

— Stok tablosu log kayıtları için oluşturuyoruz
DROP TABLE STOK_LOG
GO
CREATE TABLE [dbo].[STOK_LOG](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TABLO] [nvarchar](50) NULL,
[ISLEM] [nvarchar](50) NULL,
[TARIH] [datetime] NULL,
[KODU] [NVARCHAR] (30) NULL,
[ACIKLAMA] [nvarchar](max) NULL,
[HOSTNAME] [nvarchar](150) NULL,
CONSTRAINT [PK_BA_LOG] 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
ALTER TABLE [dbo].[STOK_LOG] ADD CONSTRAINT [DF_STOK_LOG_TARIH] DEFAULT (getdate()) FOR [TARIH]
GO
ALTER TABLE [dbo].[STOK_LOG] ADD CONSTRAINT [DF_STOK_LOG_HOSTNAME] DEFAULT (host_name()) FOR [HOSTNAME]
GO
— Varsa kayıtları siler
–DELETE FROM STOK_LOG
GO
— Trigger oluşturmak için
Create TRIGGER [dbo].[Tr_LogSTOK]
ON [dbo].[STOK]
AFTER UPDATE, INSERT, DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Activity NVARCHAR (50)
DECLARE @COUNTER nvarchar(30)
DECLARE @PXml nvarchar(max)

— update
IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
BEGIN
SET @Activity = ‘UPDATE’
SET @COUNTER=(SELECT KODU FROM deleted)
SET @PXml =(SELECT * FROM deleted FOR XML AUTO)
SET @PXml = @PXml +(SELECT * FROM inserted FOR XML AUTO)
END
— insert
IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted)
BEGIN
SET @Activity = ‘INSERT’
SET @COUNTER=(SELECT KODU FROM inserted)
SET @PXml =(SELECT * FROM inserted FOR XML AUTO)
END
— delete
IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS(SELECT * FROM inserted)
BEGIN
SET @Activity = ‘DELETE’
SET @COUNTER=(SELECT KODU FROM deleted)
SET @PXml =(SELECT * FROM deleted FOR XML AUTO)
END
INSERT INTO STOK_LOG (TABLO,ISLEM,KODU,ACIKLAMA)
VALUES(‘STOK’,@Activity,@COUNTER,@PXml)
SET NOCOUNT OFF;
END

 

 

Sipariş Paketleme tablosunda silinen, değiştirilen veya eklenen kayıtların log larını kayıt altına almak için aşağıdaki Trigger oluşturulmalı.
— Paket tablosu log kayıtları için
CREATE TABLE [dbo].[PAKET_LOG](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TABLO] [nvarchar](50) NULL,
[ISLEM] [nvarchar](50) NULL,
[TARIH] [datetime] NULL,
[COUNTER] [int] NULL,
[ACIKLAMA] [nvarchar](max) NULL,
[HOSTNAME] [nvarchar](150) NULL,
CONSTRAINT [PK_BA_LOG] 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
ALTER TABLE [dbo].[PAKET_LOG] ADD CONSTRAINT [DF_PAKET_LOG_TARIH] DEFAULT (getdate()) FOR [TARIH]
GO
ALTER TABLE [dbo].[PAKET_LOG] ADD CONSTRAINT [DF_PAKET_LOG_HOSTNAME] DEFAULT (host_name()) FOR [HOSTNAME]
GO
— Varsa kayıtları siler
–DELETE FROM PAKET_LOG
GO

— Trigger oluşturmak için
CREATE TRIGGER [dbo].[Tr_LogSIPARIS_PAKETLEME]
ON [dbo].[SIPARIS_PAKETLEME]
AFTER UPDATE, INSERT, DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Activity NVARCHAR (50)
DECLARE @COUNTER INTEGER
DECLARE @PXml nvarchar(max)
— update
IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
BEGIN
SET @Activity = ‘UPDATE’
SET @COUNTER=(SELECT COUNTER FROM deleted)
SET @PXml =(SELECT * FROM deleted FOR XML AUTO)
SET @PXml = @PXml +(SELECT * FROM inserted FOR XML AUTO)
END
— insert
IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted)
BEGIN
SET @Activity = ‘INSERT’
SET @COUNTER=(SELECT COUNTER FROM inserted)
SET @PXml =(SELECT * FROM inserted FOR XML AUTO)
END
— delete
IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS(SELECT * FROM inserted)
BEGIN
SET @Activity = ‘DELETE’
SET @COUNTER=(SELECT COUNTER FROM deleted)
SET @PXml =(SELECT * FROM deleted FOR XML AUTO)
END
INSERT INTO PAKET_LOG (TABLO,ISLEM,COUNTER,ACIKLAMA)
VALUES(‘SIPARIS_PAKETLEME’,@Activity,@COUNTER,@PXm l)
SET NOCOUNT OFF;
END