آیا ویرایش فیلد های جدول های پایگاه داده SQL Server می تواند خطرناک باشد؟

قبل از آن هم نیاز داریم که Log های تغییرات ویرایش ساختار این جدول را ردیابی کنیم. برای ردیابی از دستور DDL_DATABASE_LEVEL_EVENTS استفاده خواهیم کرد. برای استفاده از DDL_DATABASE_LEVEL_EVENTS با تریگر در SQL Server، ابتدا نیاز داریم یک تریگر DDL در سطح پایگاه داده ایجاد کنیم که به رخدادهای خاصی در سطح پایگاه داده (DDL) گوش دهد. این تریگر به شما این امکان را می‌دهد که به رویدادهایی مثل ایجاد، تغییر، و حذف اشیاء پایگاه داده واکنش نشان دهید. به عنوان مثال، فرض کنید می‌خواهیم از DDL_DATABASE_LEVEL_EVENTS استفاده کنیم تا زمانی که هر تغییری روی اشیاء پایگاه داده (مثل جداول، نماها، توابع و ...) ایجاد شد، آن را در یک جدول لاگ ذخیره کنیم.

به عنوان مثال، فرض کنید می‌خواهیم از DDL_DATABASE_LEVEL_EVENTS استفاده کنیم تا زمانی که هر تغییری روی اشیاء پایگاه داده (مثل جداول، نماها، توابع و ...) ایجاد شد، آن را در یک جدول لاگ ذخیره کنیم.

1. ایجاد جدول لاگ برای ذخیره رویدادها

ابتدا یک جدول لاگ برای ثبت جزئیات رویدادهای DDL ایجاد کنید:

کد sql
CREATE TABLE DDL_Log (
    EventID INT IDENTITY(1,1) PRIMARY KEY,
    EventType NVARCHAR(100),
    ObjectName NVARCHAR(255),
    ObjectType NVARCHAR(100),
    EventTime DATETIME DEFAULT GETDATE()
);
 
2. ایجاد تریگر DDL در سطح پایگاه داده
کد sql ایجاد تریگر دیتابیسی
CREATE TRIGGER DatabaseLevelDDLEvents
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
    DECLARE @EventData XML
    SET @EventData = EVENTDATA()

    INSERT INTO DDL_Log (EventType, ObjectName, ObjectType, EventTime)
    VALUES (
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)'),
        @EventData.value('(/EVENT_INSTANCE/ObjectType)[1]', 'NVARCHAR(100)'),
        GETDATE()
    );
END; 
 

در این مرحله، تریگر DDL را برای گوش دادن به تغییرات در سطح پایگاه داده ایجاد می‌کنیم. این تریگر از رویدادهای DDL_DATABASE_LEVEL_EVENTS استفاده می‌کند و اطلاعات رویداد را در جدول لاگ ذخیره می‌کند.

توضیح تریگر

• در این تریگر، از EVENTDATA() برای دریافت جزئیات رویداد استفاده می‌شود.
• این اطلاعات شامل نوع رویداد (EventType)، نام شیء (ObjectName) و نوع شیء (ObjectType) است.
• سپس اطلاعات رویداد در جدول DDL_Log ثبت می‌شود.

تست تریگر

برای تست، می‌توانید یک جدول جدید ایجاد کرده و مشاهده کنید که آیا اطلاعات مربوط به این تغییر در جدول DDL_Log ذخیره می‌شود یا خیر:

کد sql
CREATE TABLE TestTable (
    ID INT PRIMARY KEY,
    Name NVARCHAR(50)
);
 
 

سپس می‌توانید با اجرای دستور زیر، لاگ ثبت شده را بررسی کنید:

کد sql
 
SELECT * FROM DDL_Log; 

با این تنظیمات، هر تغییری که روی اشیاء پایگاه داده ایجاد شود، در جدول DDL_Log ثبت می‌شود و شما می‌توانید رویدادهای مختلف را نظارت کنید.

حالا میرویم سر اصل مطلب یعنی جدول Students و یکی از فیلد های آن را ویرایش می کنیم. ابتدا ساختار جدول به شرح ذیل است:

کد sql ایجاد جدول های دانش آموزان
 

-- ساخت جدول Students با ستون‌ها و تنظیمات اولیه
CREATE TABLE [dbo].[Students](
    -- تعریف ستون StudentID به عنوان کلید اصلی با افزایش خودکار (1,1) 
    [StudentID] [int] IDENTITY(1,1) NOT NULL,    
    -- تعریف ستون FirstName به عنوان نام دانش‌آموز از نوع nvarchar با طول 50
    ستون LastName به عنوان نام خانوادگی دانش‌آموز از نوع nvarchar با طول 50 (اجباری)
      NOT NULL,    
    -- ت دانش‌آموز از نوع int
    [Age] [int] NULL,    
    -- تعریف ستون Grade به عنوان نمره یا سطح دانش‌آموز از نوع nvarchar با طول 10
      NULL,    
    -- تعریف ستون FatherName به عنوا با طول 1550
      NULL,

    -- تعریف کلید اصلی (Primary Key) به نام PK__Students__32CStudentID با ترتیب صعودی (ASC)
    CONSTRAINT [PK__Students__32C52A79CBDD641B] PRIMARY KEY CLUSTERED 
    (
        [StudentID] 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
-- افزودن مقدار پیش‌فرض به ستون FatherName به‌طوری‌که اگر مقداری وارد نشود، مقدار 'F' به آن اختصاص داده شود
ALTER TABLE [dbo].[Students] 
ADD CONSTRAINT [DF_Students_FatherName] DEFAULT (N'F') FOR [FatherName]
GO

 

این کامنت‌ها توضیحات جامعی دربارهٔ هر قسمت از کد ایجاد جدول و مقادیر پیش‌فرض ارائه می‌دهند. بعد از ایجاد در ابتدا Select روی جدول Log مزنیم.

نمایش محتویات جدول DDL_LOG قبل از انجام عمل ویرایش روی جدول ها

عمل ویرایش را انجام می دهیم و یک فیلد جدید هم ایجاد می کنیم تا ببینیم چند عملیات در جدول log ذخیره خواهد شد:

سپس با دستور Select نتیجه ثبت Log پس از ویرایش را مشاهده می کنیم. 

همانگونه که مشاهده می کنید تعداد 9 رکورد یا عملیات در پس زمینه در هنگام ویرایش جدول ثبت شده است. طبق مستندات مایکروسافت با اینکه امروزه SQL Server بیشتر این موارد را به شدت مدیریت می کند ولی باز هم ممکن است در تعداد رکورد های بالا و یا در Server هایی که بار زیادی بر SQL Server وارد می شود در هنگام ایجاد و یا تغییر نام جدول های Temp موتور پایگاه داده به درستی عمل نکند و ارورهای ناخواسته موجب از بین رفتن بخش عظیمی از دیتا شود. پس قبل از هر چیز بک آپ فراموش نشود.