خطر ویرایش فیلد جدول پایگاه داده SQL Server

چرا ویرایش فیلد در جدول SQL Server خطرناک است

توسط admin | گروه SQL Server | 1403/08/10

نظرات 1

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

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

خطر ویرایش فیلد ها در جدول های پایگاه داده عملیاتی در اس کیو ال سرور SQL Server Alter Table Fields.

این تریگر به شما این امکان را می‌دهد که به رویدادهایی مثل ایجاد، تغییر، و حذف اشیاء پایگاه داده واکنش نشان دهید. به عنوان مثال، فرض کنید می‌خواهیم از 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 مزنیم.

ایجاد جدول log در sql

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

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

ویرایش فیلد در پایگاه داده

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

تعداد رخداد ثبت شده برای ویرایش جدول

EventType

ObjectName

ObjectType

EventTime

ALTER_TABLE

Students

TABLE

2024-10-31 11:01:34.177

ALTER_TABLE

Students

TABLE

2024-10-31 11:01:34.177

ALTER_TABLE

Students

TABLE

2024-10-31 11:01:57.260

CREATE_TABLE

Tmp_Students

TABLE

2024-10-31 11:01:57.260

ALTER_TABLE

Tmp_Students

TABLE

2024-10-31 11:01:57.263

ALTER_TABLE

Tmp_Students

TABLE

2024-10-31 11:01:57.267

DROP_TABLE

Students

TABLE

2024-10-31 11:01:57.283

RENAME

Tmp_Students

TABLE

2024-10-31 11:01:57.470

ALTER_TABLE

Students

TABLE

2024-10-31 11:01:57.480

 

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

 

 

1 نظر

ارسال شده توسط حاتم جوادی
1403/08/15

موضوع: قدردانی از هشدار شما در مورد ویرایش فیلدهای جدول پایگاه داده با سلام و احترام، با سپاس فراوان از توجه و دقت شما در یادآوری خطرات احتمالی ویرایش فیلدهای جداول پایگاه داده SQL Server. تغییرات در ساختار پایگاه داده‌ها می‌تواند تاثیرات جدی و پیش‌بینی نشده‌ای بر عملکرد سیستم و داده‌های موجود بگذارد. هشدار و توجه دقیق شما به این موضوع، به‌ویژه در حفظ یکپارچگی و سلامت اطلاعات، قابل تقدیر است. اطمینان داشته باشید که ما با آگاهی بیشتری به این مسائل خواهیم پرداخت. با تشکر.

نظر محترم شما در مورد مقاله های وب سایت برنامه نویسی و پایگاه داده

نظرات محترم شما در خدمات رسانی بهتر ما را یاری می نمایند. لطفا اگر مایل بودید یک نظر ما را مهمان فرمائید. آدرس ایمیل و وب سایت شما نمایش داده نخواهد شد.

حرف 500 حداکثر